Excel Hints, Tips & Hacks - Part 1
Updated: Feb 20
In the first post of the series, we'll be posting hints & tips that will help you on your way to becoming an Excel expert.
Produce an Instant Chart to Visually Inspect It
When working with a data set it can be useful to see a quick visual of what that data looks like, to examine a trend or spot any errors. You can do this by highlighting the data and pressing ALT + F1
Have Descriptive Headers Such as 'Y1' but Still Use Them in Calculations
If you have a data set such as below, users will often add a number above their descriptive header to use in calculations. In the below example Excel wouldn't recognise Y1, Y2 (short for Year 1, Year 2) as a number, so the users add the number above it to use in calculations:
However, there is another way that this can all be done directly in the header row itself that would allow Excel to use it as a calculation, but still display as a mixture of text/numbers. This is done via custom formatting.
If you were to use the above example then instead of typing in Y1, Y2, Y3 etc. we'd just have the numbers 1,2,3 etc.
Then you would highlight those numbers, go to custom number formatting and input the formatting like this:
The "Y" in quotes will enter the letter Y before any number (indicated by the 0). So by selected the formatting as such the numbers 1,2,3,4,5 now turn into Y1, Y2, Y3, Y4, Y5.
You can expand on this, for example, you could use "Year "0 and it would work exactly the same, or any other text that you wanted to incorporate.
The beauty of this is that in can all be done directly within the same cell and also still allows the underlying numbers to be used in calculations, but the user sees the formatted example on their screen.
Hidden Excel Mouse Menu
Excel has a menu that a lot of users don't know exists! You can find this by right clicking and dragging on the border of a range and the release the mouse button to display the hidden menu!
This gives you the ability to:
Copy Here as Values Only
Copy Here as Formats Only
Create Hyperlink Here
Shift Down and Copy
Shift Right and Copy
Shift Down and Move
Shift Right and Move
Left Click & Drag to Move Cells
On a similar note to the above, you can also move a cell, or range of cells, by selecting them and then left click and hold on the border of the selected range whilst moving it to the desired location. When releasing the mouse button the cells will then move to the chosen destination
Keep The Top Row Visible At All Times
If you have a data set that you need to keep the top row visible so that you can see the headers when scrolling down through the data then you can use the Excel option of 'Freeze Top Row' which can be found under view --> Freeze Panes --> Freeze Top Row
Other Freeze Options Apart From Top Row
Using the above method you can also select 'Freeze First Column' to keep the first column in place when scrolling to the right, or alternatively you can use 'Freeze Panes' which will freeze the rows above the selected cell and columns to the left the selected cell. This can be useful to freeze headers and data labels when they are not in the first row/column.
Quickly View/Navigate a List of Sheets Within an Excel Workbook
Sometimes if you have lots of worksheets in a workbook it can become painful to have to scroll through them all to find the one you need. However, you can obtain a list of all of these sheets by right clicking on the sheet scroll arrows at the bottom left of the Excel screen.
This allows you to choose and sheet and then click OK to be taken to it.
Quickly Select Every Cell In A Worksheet
To do this you can click on the small icon above and to the left of column A and row 1
Quickly Go To a Cell Using the 'Go To' Shortcut
If you need to quickly get to another cell, for instance when tracing through a formula, then you can use the shortcut of Ctrl + G which opens the 'Go To' Excel menu
In the above example, you would type A5 into the 'Reference' box and then when you click OK this would navigate you straight to the chosen cell
Automatically Go To A Cell Referenced In a Formula
This shortcut can be very handy when navigating between formulas and worksheets. If a cell had a formula in it that said = B25 then by pressing "CTRL + [ " then this would automatically take you to that cell.
This also works across sheets, so if the formula referred to a cell on 'Sheet 5' such as ='Sheet 5'!B25 then this hack would jump to that sheet and cell.
Automatically Return To The Above Cell
Likewise with the above, you can also use "CTRL + ]" to return you back to the cell that the above shortcut of "CTRL + [" took you to.
Again this is extremely useful when flicking between spreadsheets and cell references by just using those shortcuts.