Consolidating Formatting and Formulas in Excel

Consolidating formulas and formatting in Excel is an excellent way to streamline workflow and reduce time spent writing formulas and formatting across spreadsheets.  This technique works for workbooks that have cell references in the same location from sheet to sheet (i.e., the title is in Cell A1 on all sheets, the data represented is in the same location, respectively, from sheet to sheet).

For example, you have a workbook that has 12 sheets for each month of the year (January to December) and then a Summary sheet at the end of the workbook.  To streamline workflow, you can link the sheets together and any formatting changes you make to one will be done on all the others as well.  To link the sheets together:

  • Click the first sheet tab (January)
  • Hold down the shift key and keep it held down
  • Click the last sheet tab (December)
  • Let go of the shift key.

This will link (or group) the sheets together.  At this point you may begin making formatting changes to modify the appearance of the sheets.  When you have finished your formatting changes, double click any one of the sheet tabs to ungroup the sheets or right click a sheet tab and select ungroup.  When you take a look at the sheets, the formatting changes made will be the same from the January sheet to the December sheet.  You have just saved yourself the time and energy of formatting 12 sheets individually.

In the same manner, you may also streamline writing formulas on the Summary sheet by linking the January to December sheets together.  As an example, if on the Summary sheet you want to write a formula that calculates sales totals for all months, you may do so by linking the sheets and then simply writing one formula.  To write a formula across spreadsheets:

  • Place your cursor on the summary sheet where you wish to write the formula.
  • Start the formula.  In this case it will be =sum(
  • (Leave the bracket open until you include all the criteria)
  • Click the January sheet tab
  • Hold down the shift key and keep it held down
  • Click the December sheet tab
  • Let go of the shift key
  • Click the cell reference that you want to include in the formula
  • As an example, if the sales totals for all months are in Cell B30 of each sheet, click cell B30
  • Close the bracket on the formula
  • Press the enter key

You should have written a formula that looks like this:

=SUM(‘January:December’!B30)

You can pick up additional streamlining power by auto filling this formula across by column or down by row on the Summary sheet if need be.  Excel will change the cell reference for you, relative to it’s new position on the sheet (hence the term “relative reference”).

Next time you need to consolidate formulas and formatting across sheets, consider this time saving technique to increase the speed and efficiency with which you work.

This entry was posted in: MS Excel Tips.
"Hawthorn Training delivers expert and up to date software training solutions for your business."