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.
Category Archives: Tips & Tricks
Simple Excel Drop Down List
Quick Solution
A drop down list in Excel can help speed up data input and ensure the user has entered a valid entry. If you have only a few choices, creating the drop down can be a quick process.
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.
Reduce Excel File Size
Ctrl + End is your friend
I recently helped a client reduce the size of an Excel file. The file took a while to save which was frustrating and time consuming. I thought I would share this reasonably easy solution.
Filtering Tip
Make your headings bold.
This tip applies to tables and to the structures you use for charts.
Excel looks for the bold format when it reviews tables and layouts to figure out if your table has a headings row.
You can use Ctrl + Shift + L to add or remove the filter icons to a data table. There is also an icon on Data ribbon tab.
This will work more reliably if the headings are bold.
I use the following keyboard combination on the top left corner of the table.
Ctrl + Shift + right arrow (this selects all the headings)
Ctrl + b (this applies bold to the headings)
Ctrl + Shift + L (to turn on filters)
This combination can be done very quickly.
You can just use Ctrl + Shift + L within the table, but sometimes this applies the filter to the wrong row.
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.
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.
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)
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.
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.
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.
VBA to Clear a Filter
Using Excel’s built-in filtering can speed up your VBA code.
It is important if you are applying filters that you clear any existing filters before you apply a new filter. Otherwise the existing filters will usually affect a new filter you apply.
The line of code below will remove filters on Sheet1 (Sheet1 is the sheet code name that you see on the left side of the VBA screen – it may not be the sheet tab name).
If Sheet1.FilterMode Then Sheet1.ShowAllData
The .FilterMode property is True if a filter is in place on the sheet and False if not.
The .ShowAllData method will return an error if no filter is in place – hence the use of the If statement.
Instant Format in Excel
You may know the two keyboard shortcuts below for currency and percentage.
But what you may NOT know is a technique that has been around since the early versions of Excel.
The technique allows you to automatically apply these two formats after you type an entry.
It you type $1000 into a cell and press Enter. Excel will automatically apply the $ format to the cell. The $ sign will not display in the Formula Bar – see below.
If you type 2.5% into a cell. Excel will automatically apply the standard % format to the cell. The % sign will display in the Formula Bar – see below.
As I mentioned these are really old skills that have been lost over the years since we no longer have Excel manuals – shows my age.
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.