Welcome to your weekly 3 tip Friday update, it’s just a short dose of Excel help before you start the weekend. Each week we’ll cover a tip, a shortcut and a function - making it super easy for you to get better at Excel.
A quick win you can learn today -
Excel’s charting functionality is well known, and you can spend hours learning how to use all the different types. One downside of charts is the amount of space they take up on the page. Did you know that you can add mini ‘in cell’ charts called sparklines? These can be simple bar or line charts. They work especially well in reports organised horizontally across the sheet - for example one column per month.
To add one, select the data, go to the insert menu, and select a sparkline. Excel will prompt you to choose the range of data, and location of the sparkline. Once built, you can add more lines by dragging down the sheet, the same way you would with a formula.
A keyboard shortcut I'm adding to my list -
Do you often work with spreadsheets containing a large number of formulas? Sometimes it can be hard to deduce what other cells in the work book are being referred to, without going into the function itself and looking at each component of the function. There’s 2 really useful shortcuts that will highlight either the cells that a function is referring to, or alternatively to select any cells that refer to the cell you have selected. The two shortcuts are:
Ctrl + [ Selects all the cells that are directly referred to by the formula in the active cell (precedents)
Ctrl + ] Selects all the cells that directly refer to the active cell (dependents)
A function I've found super useful this week -
One of the first mathematical functions that Excel user’s tend to learn is AVERAGE, along with SUM and COUNT. The AVERAGE function in Excel calculates the mean of a data set, that is to say the sum of all the items divided by the number of records. But there are 2 other commonly used types of average used which you may wish to perform in Excel.
=MEDIAN will calculate the median of a data set - that is the middle value of a data set organised from highest to lowest. This can be useful if you have a few outlying values which skew the mean.
=MODE will calculate the mode of a data set - that is the most commonly occurring value. This is useful if you’re trying to work out the most popular number in a range.
Hope these were useful, until next time all the best.