Press Enter and Stay in Current Cell
A trick to stay in the cell you are editing is to hold the Ctrl key down when you press Enter.
Monthly Archives: July 2017
You Can Undo After You Save
I am amazed how few people know this.
Way back in Office 2007 Microsoft changed the Undo List so that it is NOT cleared whenever you save a file.
You can use Ctrl + z or the Undo icon to undo things you did before you saved the file.
If you close the file that obviously clears the Undo List.
Please let people know this as I find so many people in my training sessions do not know things have changed since Office 2003.
This applies to all MS Office apps.
Clear Borders In Excel
If you need to clear all the borders from a selected range use
Ctrl + Shift + _ (underline)
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.
Make Excel VBA Pause
Sometimes when running a macro you need to make sure Excel has had time to do something before progressing.
This is typically in large models were it can take time (a few seconds) to do a specific task eg removing a filter or updating an external data source.
You can pause a macro to allow Excel to do something by using the Wait command.
Application.Wait (Now + TimeValue("0:00:02"))
The above code will pause the macro for 2 seconds.
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.
Adam Saxton - Microsoft
2017-06-12
2017-06-12
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.
Ctrl + Alt + F5 Not Working
The keyboard shortcut to Refresh All in Excel is
This refreshes all the data connections in the file in one step.
The problem is that on some systems (like mine) this conflicts with an Intel Graphics hot key.
To turn off the graphics hotkeys right click the Desktop and choose Graphics Options, then Hot Keys then Disable. See below.
Big thanks to StackOverflow for covering this issue – link below.
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.
All the Power BI whitepapers in one place.