Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
Category Archives: Tips & Tricks
Free Excel Webinar Recording – Copy and Paste Tips and Tricks
Feedback score 93%
My free Excel webinar for June 2018 covered Copy and Paste Tips and Tricks. Download the materials using the button below and watch the video.
The session focuses on the Paste Special dialog plus a little known pasting feature that is great for dashboards.
It covers the hows and whys of
- Paste Values, Paste Formulas, Paste Formats
- Converting negative to positives
- Fixing Text numbers in-situ
- Applying a Factor to a range
- Paste Link – how and why to use it
- Transpose (switching rows to columns and visa-versa)
- Paste Picture Link (great for dashboards)
The session includes lots of keyboard shortcuts. As always, I shared a few other tips during the session.
Stopping the Clipboard from being cleared in Excel VBA
How to check if the clipboard is empty
It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
Inserting a Blank Row Between Entries
Manual technique
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy.
Show all comments
Cell comments are useful for instructions and documentation.
If you want to make all the comments on a sheet visible, use Alt v c pressed in sequence, not held down.
Once visible this shortcut also hides all the comments in one go.
This is an old Excel 2003 shortcut that still works.
Identifying Locked Cells in Excel
How do you know if a cell is locked in Excel? There is an easy was to find out. And it also makes it easier to lock or unlock the cell.
Name Box and Macros
I have found out yet another feature of the Name Box.
The Name Box is on the left of the Formula Bar and above the column letters – see image below.
During a recent macro webinar I tried to create a new range name called Test using the Name Box.
But I also has already created a macro called Test as part of the training.
As soon as I pressed Enter after typing Test into the Name Box to create the Test range name, I was magically transported to the VBA window to the Test macro – Wow!
This means you can’t create a range name in the Name Box that is the same as a macro name.
You have to use the Define name icon on the Formulas tab to do that.
Easy Financial Year Formula
To get the Australian financial year from a date you usually use an IF function based on the month number.
I recently learned a new hack from Matt Allington of Exceleratorbi.
You can add 184 to the date and then use the YEAR function. See table and formulas below.
The formula in cell B2 is
=YEAR(A2)
The formula in cell C2 is
=YEAR(A2+184)
Both formulas have been copied down.
A simple solution to a frustrating issue. Thanks Matt.
PivotTable Combinations Trick
How to get a list of combinations
I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.
PivotTable Grouping Doesn’t Work
Rogue data the reason
Grouping is a powerful feature in PivotTable reports but sometimes Excel won’t let you apply grouping. There are a few reasons for this.
Excel Test For One of Two Conditions
A shorter alternative
In Excel it is quite common to test a cell for either a zero or a blank. If either of these two entries are found then you do a particular calculation. There is an easy way to handle this.
Excel Formula to Extract the Domain
Using the SUBSTITUTE function
I recently read a blog post about using Excel for SEO (Search Engine Optimisation). It mentioned a function to extract a domain from a URL. The function was from Google docs, not Excel. So I wrote an Excel formula to extract the domain from their list of URLs.
Selecting a column
To quickly select a column of data in a formatted table you have a couple of options.
Keyboard
Select a cell in the column and press Ctrl + Space Bar.
This will select the column of data. If you want the heading too, press it again.
You can also select multiple columns before using the shortcut.
Mouse
This technique can take practice if your headings are in row 1.
If the heading starts in row 2 or below it is easier. See image below.
If you point to just above the heading row you will see a downward facing, black arrow. Click this once to select just the data. Click it again to include the heading.
When the heading row is in row 1 you need to do the same but make sure the column letter doesn’t highlight.
The image below is the correct arrow – this will select the column in the table only.
In the image below the arrow shown (because the column letter is highlighted) will select the whole column, not just the data in the table.
Naming Charts in Excel
Two techniques
You may have noticed that Excel gives every chart a unique number when it creates the chart. It is displayed in the Name Box in the left corner above the grid. You have the ability to change that name and make it more descriptive.
Excel Protection Tips
Letting users make input entries
When you are setting up a protected sheet that allows users to make inputs, a few option changes can make the user’s experience a lot easier.
Stop Charts Resizing
Works with other graphic objects like text boxes
We’ve all been there, our charts are looking just right and then some one inserts a column or changes the column width and throws out all our perfectly proportioned charts.
VBA If statement tip
When creating long VBA code it is common that the start of an If statement and the matching End If statement may not be visible on the same screen.
When scrolling around trying to understand your code it can be useful to include the If statement itself as a comment following on the same line as the End If command – see examples below.
If x=0 Then 'lots of code If y =1 Then 'lots of code End If 'If y =1 then End If 'If x=0 Then |
The apostrophe is used to specify the start of a comment – you can have a comment following a line of code.
This structure can assist when trying to identify which End If statement relates to which If statement.
Entering Date and Time in Excel
There are shortcuts to enter
- the current Date Ctr + ;
- the current Time Ctrl + Shift + :
There isn’t one to enter both.
You can use them in sequence to achieve a date and a time entry.
In sequence press
Ctrl + ;
Space bar
Ctrl + Shift + :
The space separates the date and time and Excel will recognise the entry as a date and time.
Good Excel Ettiquette
Common sense
If you change a formula in someone else’s file you should tell them. In fact there are a few things you should do to make it easier. This can apply to changes to your own files as well.
Another date solution
Formula to the rescue
Getting dates into order is usually a job for Power Query, but not everyone has it or uses it so I still get requests for formulas to fix text dates.