Search
  • Chris Rowley

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


Excel Expert Hints & Tips

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:



Excel Specialist Hints & Tips

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:



Excel Hints & Tips - Insightful Dynamics

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!



Excel Expert Hacks


This gives you the ability to:


  • Move Here

  • Copy Here

  • Copy Here as Values Only

  • Copy Here as Formats Only

  • Link Here

  • 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



Excel Expert Hints & Tips

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.



Insightful Dynamics - Excel Specialist - Hints & Tips

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



Excel Developer - Hints & Tips

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.



Ready for more? Read part 2 of the Excel hints & tips series


If you found this article useful then you can subscribe for further content, you can also visit Insightful Dynamics at www.insightfuldynamics.co.uk or contact us at contact@insightfuldynamics.co.uk.

0800 193 4700

  • LinkedIn
  • Facebook
  • Twitter

©2020 by Insightful Dynamics.

T&C's & Privacy Policy