DOC Office Consulting

A well designed spreadsheet is like modern day magic. Linked cells, conditional formatting and clever formula writing can all be combined to analyse data and present results in less time than it takes to pull a rabbit from a hat.

Incorporating new efficiencies into your spreadsheets may seem daunting, but there’s lots of solutions out there if you can spare the time to learn. I promise you that the time you’ll invest in learning will be a drop in the ocean compared to the time you’ll save.

To get you started, here are five of my favourite super simple, but extremely underutilised Excel automation functions:

The Format Painter is one of the most underutilised functions in Microsoft Office. It copies all of the formatting from a selection (including spacing, font style, size, colour, borders, shading, number formatting, etc., etc., etc.) and applies it to another selection, with just 3 mouse clicks.

To use the format painter:

• Select the cells that are already perfectly formatted
• Click the Format Painter icon from the Clipboard section of the Home tab on the ribbon
• Select the cells to apply the formatting to.

Voila! Perfect formatting easily duplicated across multiple cells 

Conditional formatting is a super simple feature that provides a visual indicator of how well each entry in a range of data compares against the other entries. It can be used to highlight the best or worst performers, display indicators of performance against expectations or (as in the case of data bars), show a simple bar graph in each cell to represent the cell value compared against the other cells selected.

To apply data bars:

• Select the range of cells with comparable data
• Click Conditional Formatting from the Styles group on the home tab of the ribbon
• Click Data Bars and choose the preferred style and colour of the data bar to apply

Linking cells from one worksheet to another in Excel is easier than you might think and is achieved with only two keystrokes and two mouse clicks.
• In the cell that should display the linked cell value (the destination cell), type = (the equals sign)
• Use the mouse to select the tab of the worksheet of the linked cell (the linked worksheet)
• Use the mouse to click the cell that shows the value to be linked (the linked cell)
  – Notice that the formula bar now displays the Sheet Name and cell address of the linked cell

• Press the ENTER key – this step is important and must be done to complete the formula before clicking back to the destination worksheet

Excel’s AutoSum function can be used to simply calculate a common equation on a range of cells, such as discovering the sum, the average, the highest value (maximum), the lowest value (minimum), the count of cells, etc.

To enter a calculation with the AutoSum:

• Click the cell that should display the calculation
  – this is usually directly adjacent to the range of cells being calculated

• Click the AutoSum button from the Editing group on the Home tab of the ribbon
• Check that the range is correct & press Enter



Flash Fill

Flash Fill is a feature new to Excel 2013 that anticipates data requirements by detecting patterns in cells when compared to adjacent cells and auto completes text entry based on these patterns. It can be used to extract just a small part of a cell, combine parts of multiple cells or add text to calculated values – all without the need for formula writing… Wow.

To use flash fill:

• Select a cell in the same row as the text to be extracted, combined or summarised
• Type the first entry
• Select the remainder of the cells in this column to be completed
• From the Home tab of the ribbon, click the Fill icon
• From the drop down list, choose Flash Fill

Abracadabra! All entries in the list have been completed without any extra data entry.

If you’d like to take your Excel efficiency to the next level, either through group training sessions or one-on-one for more specialised attention, give us a call to chat about what you’re looking for and what we can offer.