Tip 303 – Use a name instead of an address

DOWNLOAD PDF Cells already have addresses, such as A1, but you can assign names to single cells or multiple cells that you can then use in formulas instead of their address. Select a cell, click in the Name Box (which is just above cell A1) and type a name for this cell – for example, […]

Tip 296 – Easily analyse data by month

DOWNLOAD PDF If you need to summarize a set of data by month, insert a new column next to the date column and enter this formula: =month(A1) to extract the month as a number from the date in cell A1, or =text(A1, “mmmm”) to extract the month in words. Copy this formula down the rest […]

Tip 294 – Quickly convert a range to a table

DOWNLOAD PDF Converting a range to a table gives you instant filters and totals for each column. To do this quickly, select the cells you want to convert to a table and press the CTRL and T keys together. Once inserted, click the Table Design tab to add totals and control the formatting of the […]

Tip 292 – View two sheets from the same workbook, side by side

DOWNLOAD PDF If you need to see two sheets from the same workbook side by side, open one of the sheets and click on the View tab. Click the “New Window” button and a duplicate view of your sheet will open. Both views will have a :1 and :2 after the name in the Window […]

Tip 285 – Recover an unsaved Workbook

DOWNLOAD PDF If you’re quick, you may not need to redo an entire spreadsheet you forgot to save. Open Excel, go to the File tab and select the Open menu. Scroll to the bottom of the Recent Workbooks list and click the “Recover Unsaved Workbooks” button. This will display a folder with any unsaved workbooks […]

Tip 276 – Quickly fill a column

DOWNLOAD PDF In Excel, rather than clicking and dragging to copy a cell down the rest of a column, simply double click the fill handle – the little square in the bottom right-hand corner of the selected cell  

Tip 275 – Quickly copy a cell to adjacent cells on the same row

DOWNLOAD PDF If you need to copy a cell to adjacent cells to the right of it, select the cell plus the cells you want to copy it into (they must all be adjacent) and then press the CTRL and R keys together. Selecting a single cell and pressing the CTRL and R keys will […]

Tip 269 – Join text using the ampersand

DOWNLOAD PDF If you have a column containing first names and another column containing surnames and you could like to join the two together so the full name is in the same column or cell, in a third empty column, on the first row, enter this formula: =A2&” “&B2, where A2 is the cell with […]

Tip 267 – Quickly multiply a range of numbers by a given number

DOWNLOAD PDF If you have a range of numbers, pricing for example, and you want to multiply the entire range by the same number, say by 0.95 to apply a 5% discount, or 1.05 to apply a 5% increase, type the coefficient you want to use in an empty cell. Copy that cell and select […]