Excel is still easily the most widely used and the most popular spreadsheet software. Everyone who needs a good dose of tips & tricks to improve their Excel skills should be sure to read the five tips below.
1. Removing blank cells
In most Excel sheets, empty cells are totally unwanted and annoying. Fortunately, there is an easy way of removing all empty rows from a data range.
How?
– Select your range (or use Ctrl + A if you want to remove the empty cells from the entire Excel sheet).
– Press F5.
– In the pop-up Go To box, click on ‘Special’.
– In the menu, select ‘Blanks’ and click on ‘OK’.
– The blank cells in your range are selected (grey).
– Right-click in one of the selected cells.
– In the menu, select ‘Delete’.
– A new pop up opens, in which you have four choices:
o Shift cells left
o Shift cells up
o Entire row
o Entire column
– In our example, you select ‘Shift cells up’ or ‘Entire row’ if you have entirely blank rows.
2. Adding today’s date
This is an underrated by very handy shortcut in Excel. Suppose you want to add today’s date in a report. You have two options:
– Ctrl + In this case, the date is added as a fixed value (in other words, it will not change).
– =Today() The date is added and is dynamic (in other words, it will adapt the next time you open the report).
3. Making a forecast
To find a trend and forecast the future values, you can use the FORECAST function. Example: suppose you want to forecast the turnover/costs on the basis of the turnover/costs recorded in the previous period. Note that the figures do not depend on a certain time or season of the year (e.g.: Christmas, summertime, etc.). To do this, you can use the FORECAST.LINEAR. function in Excel.
How?
– Suppose that in the example below, you want to know what the anticipated turnover, costs and margin will be in period 13 (column N).
– Go to cell N2.
– At the top, press fx to open the function wizard.
– In the search window, type ‘forecast’.
– In the drop-down list, select ‘FORECAST.LINEAR’ and press OK.
– A window opens in which you can enter the criteria:
o X = the period for which you wish to make a forecast (in our example this 13, so we can refer to cell N1)
o Known_ys = the known figures or data range from the previous period (in our example this is the turnover from period 1 to 12 inclusive or range B2:M2)
o Known_xs = the range of the known or previous period (in our example this is the period 1 to 12 inclusive or range B1:M1).
N.B.: if you want to copy the formula in other cells, it is best to use the $ sign to freeze columns or rows.
– Press OK.
– N2 will now show the result and if necessary you can carry this over to other cells (in our example, to costs N3 and margin N4).
4. Sparklines
A sparkline is also known as a mini chart as it does not take up much room. These are one-cell images that you can use to visual a specific trend.
How?
– Taking the example from the previous tip, we want to view our data trend over the past 12 months in cell N2.
– Go to the ‘Insert’ tab and select ‘Sparklines’.
– Depending on the type of data that you want to view, select Line, Column or Win/Loss. In our example, it is best to select ‘Column’ type.
– A pop up opens in which you have to enter the criteria:
o Data Range = the data range that we want to view. In our example this is B2:M2
o Location Range = the cell in which the sparkline should be placed.
– Click on OK.
– The sparkline is added. An extra tab labelled ‘Design’ also appears at the top.
Here you can process the (layout of the ) sparkline (colour, datapoints, etc.).
5. Advance select and adapt
Suppose you want to find and adapt several cells with the same values. There is a handy way of adapting them all at the same time. Rather than using the filter.
How?
– In the Home tab, go to ‘Find & Select’ at the end and click on Find.
– Enter the value you want to find. In the above example, we want to find and adapt the value ‘item 1’.
– Then click on Find All. A list appears with all the cells containing ‘item 1’. Then click on Ctrl + A to select all the cells.
– Click on Close.
– You can now change the layout of all cells containing ‘item 1’ (e.g.: in bold, yellow background, etc.).
