Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
Category Archives: Excel Blog
Old Article
Some articles make a greater impression than others. I was recently asked about a magazine article from August 2011.
Someone had changed jobs and needed to be reminded of the rolling 13 month technique.
The congratulations relate to 15 years of Excel Yourself!
How to Handle a Formatted Table Limitation
Choose from simple or advanced
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation.
Declaring Variables in Excel VBA
Using the Dim statement
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
Converting Text Numbers and Reversing Signs in Excel
Paste Special to the rescue
Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special.
Hyperlink to a Range in Excel
Select a range with a click
Hyperlinks are a great way to navigate around complex spreadsheets. Most times when you create a hyperlink you link to a single cell within the sheet. In some cases there is a good reason to link to a range.
Inserting a Blank Row Between Entries in Excel
A sorting solution
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently there is an import routine that requires it. My normal solution is a macro because it automates the whole process but there is a manual technique that is quick and easy.
Avoid Overwriting Files in Excel
A reason to open as read-only
Why would you purposely open a file as read-only? If you regularly open files from last month and then save them as this months’ version then read-only is your friend – see how.
Excel Filtering for Blanks or Zeroes
Making the filter work across multiple columns
If you want to filter by blanks across multiple columns the standard Filter feature can’t help you. You can use the Advanced Filter but that takes time to set up and most users don’t know how to use Advanced Filter.
Filter by Cell Value Keyboard Shortcut
Its quicker to use the keyboard
I have found a keyboard shortcut combination to one on my favourite right click options and its quicker to use.
Replace Merged Cells with Center Across Selection
Let the macro do the work
Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
Understanding Filter Context in DAX
An Excel-based explanation
Are you trying to get your head around Filter context in DAX? I watched a video from the sqlbi.com guys and it explained it well. I thought I could add an Excel flavour to it.
Creating a Unique Dynamic List in Excel
A Power Query technique
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list.
Easier Structures in Excel
Some are easier to use than others
What is the best layout when working with months/quarters/half years and full years? There are a few common structures. I prefer the one that lets you create single formulas that can be quickly copied across and down with as few copies as possible.
Are There Hidden Rows or Columns?
A macro answers the question
I was working on a project for a client and receiving multiple files. Some of the sheets had hidden rows or columns. I realised there is no easy way to find out if a sheet has hidden rows or columns, so I wrote a macro.
Reduce Excel Formula Length
Remove redundant references
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
Automatically Populating a Column with Text in a Formatted Table
A formula trick
Here’s the problem, we have four separate tables with the same layout. They hold four different metrics: Actuals, Budget, Forecast and Last Year. A column called Type is used to hold the metric name. We need to populate the Type column.
Deleting Duplicated Names in Excel
Names scoped at Worksheet level are often duplicates
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
Some Observations on Structure in Excel
Tables rule
My consulting work recently highlighted a stark contrast in different Excel models and the effort it takes to create or change them. I make some recommendations to make things easier for yourself at the end of this post.
Excel Subtotal Sort Magic
Sorting by subtotals is possible
Sometimes Excel surprises me. In this case it sorts in a way I didn’t expect, but in a good way. Thanks to Mr Excel for the tip.