[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel]
New-ItemProperty -Path “HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel” -Name “DisableUpdateNotification” -PropertyType DWORD -Value 1
To compare two columns (A & B) in Excel use the following formula in C. If more than 1000 rows are used update the formula as needed. Autofill the C column as needed.
UPDATE: I kind of like this a little better.
When opening an Excel file that was exported from IBM Cognos Impromptu you receive an error that says, “The file you are trying to open, “Export.xls”, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?”
If you click Yes the file still opens however the error is annoying. If you open the Excel file in notepad you will also notice it says, “This document is formatted for viewing in Microsoft Excel 2002 and subsequent versions. You are using a previous version of Excel.”
To fix the issue do the following. Back in Cognos click Report>Excel. In the Version drop-down change it from Excel 2002/2003 to Excel 2007 and higher then click OK. Now save the report again as an Excel file. Notice this time the file extension will be XLSX.
I had a user report that every time she opened any spreadsheet in Excel 2000 when logged in to the Terminal Server it said “Simulation Template – filename.xls” instead of “Microsoft Excel – filename.xls“. Even if she just opened the Excel program first it still said “Simulation Template – Book 1”. It didn’t seem to affect anything negatively but it was still strange that it was showing up like this.
My first thought was she had saved a file in her XLStart folder (C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART) but there was nothing in there. Maybe somehow someone saved a file to the system XLStart folder (C:\Program Files\Microsoft Office\Office\XLStart). Nope, nothing there either.
OK, maybe there’s something in her user profile that’s messed up. I had her log off the Terminal Server. I verified that her profile had been deleted from the server & then rename her roaming profile & redirected folders so that when she logged in it created everything from scratch. When she logged back in everything appeared as expected as if she was logging in for the first time. When she launched Excel it still said, “Simulation Template – Book 1”.
Well maybe it’s more server related than user related. So I logged into the server, launched Excel & wouldn’t you know it, it said “Simulation Template – Book 1”. At this point I’m a little stumped. Like any stumped administrator…when in doubt, run Process Monitor.
I started ProcMon, launched Excel then stopped the capture. I filtered to only show excel.exe. Then I did a search for the word simulation. I got several hits but as I was going through them one jumped out at me. It was querying a value called LoadBehavior in HKLM\SOFTWARE\Microsoft\Office\Excel\Addins\SimulationClientAddIN.Connect. I right clicked the entry to jump to that spot in the registry. LoadBehavior was set to 3.
Not knowing what a valid value is for this I did a little searching & came across this site. Apparently 3 mean the add-in’s status is “Loaded” & its load behavior is “Load at startup”. Looking through the list 2, 8, or 9 also looked like viable options as well. So I changed the value to 2. I opened up Excel & sure enough the title bar now says, “Microsoft Excel – Book 1”!!!
According to the chart on that website leaving it on 2 leave opens the possibility that LoadBehavior could change back to 3 if the application successfully loads the add-in. But for now I’m going to leave it at 2. If the issue arrises again I may just change it to 9.
This is my little way of changing the way a phone number is displayed in a cell not using number formating (i.e. General).
Cell A1 contain 5555555555. I want it to display as (555) 555-5555. So in B1 I enter the following formula:
Right click B1 & select copy. Right click A1 & select Paste Special. Select Values & click OK. Now delete cell B1.