Disable Automatic Updates for Power Query for Excel

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel]
“DisableUpdateNotification”=dword:00000001

or

New-ItemProperty -Path “HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft Power Query for Excel” -Name “DisableUpdateNotification” -PropertyType DWORD -Value 1

Some Icon Set Images in Excel Won’t Print

The original file that prompted this post had many more elements in it than the example I will use. I’ve cut out a lot of the hours (and I mean hours) of random troubleshooting tangents I went on and tried to simplify it to get to the core of the issue. This one is going to be a screenshot happy post.

A user reported an issue she was having with an Excel file where some images were not printing. The spreadsheet has grouped rows with summary rows for each. There is a Conditional Formatting rule that displays the green, yellow, and red icon sets based on percentages. The print area has been set to include only the cells with data.

 

Groups collapsed

Groups expanded

 

Conditional formatting rule

 

When printing some of the icons in the print preview (and the print job) were missing.

If I remove the print area and expand the groups they all show up in the pint preview.

So what’s causing this??? Merged cells and page breaks. More specifically, when merged cells are outside of the print area but are adjacent to cells that contain an icon from a conditional formatting rule, the icon will not print. In the screenshot below I have filled in the merged cells in blue.

In the current scenario B3, B8, and B13 would be an issue but they are not currently on a page break. B18 and F7 on the other hand are a problem because they are outside the print area but right next to a cell that contains an icon. When a group is collapsed it is technically no longer considered to be in the print area.

The fix is simple, unmerge the cells. Now the print preview shows the icons!

 

This is definitely a bug.

Here’s a link to my sample file if you are interested. Sample.xlsx

Compare Two Columns in Excel

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.

=IF(ISERROR(VLOOKUP(A1,$B$1:$B$1000,1,FALSE)),A1,””)

UPDATE: I kind of like this a little better.

=IF(ISERROR(VLOOKUP(A1,B$1:B$1000,1,FALSE)),A1,””)

Error opening an Excel file

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.

Ref – http://www-01.ibm.com/support/docview.wss?uid=swg21350064, https://www-304.ibm.com/support/docview.wss?uid=swg21355007

Excel Title Bar Doesn’t say “Microsoft Excel – Book 1”

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.

My Excel Phone Number Trick

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:

=”(“&LEFT(A1,3)&”) “&MID(A1,4,3)&”-“&RIGHT(A1,4)

Right click B1 & select copy. Right click A1 & select Paste Special. Select Values & click OK. Now delete cell B1.