Keep Your Spreadsheets in Style!

Overview

Do you format your Excel files one format and one cell at a time?  Or do you use cell styles to speed it up?  I admit, I didn’t use cell styles for the longest time.  Old habits die hard, right?  But after learning about how easily cell styles can be quickly tweaked and updated all across a workbook, I’m now a fan.  In this article, we’ll discuss what cell styles are, why you should consider using them, some tips and gotchas, and a few macros that make them even quicker and easier to use.

What are cell styles?

• They are a way to apply several cell formats in one step – font type/style/size/color, fill colors, number formats, borders, alignment, and even cell protection 🖼️.
• They are accessible from the Home tab 🖼️ and they display a palette of the built-in cell styles and your custom cell styles 🖼️.
• They are tied to individual workbooks, not the entire Excel application.  The cell styles from separate workbooks can be merged.
• There are 49 built-in cell styles at the time of this writing, and most of these are based on your selected Office theme (which can be changed on the Page Layout tab).  These can be changed, and all but the Normal cell style can be deleted.
• You can can add custom cell styles either by adding them or duplicating an existing style 🖼️.  While you can theoretically add thousands of cell styles, they can quickly become unmanageable.
• By default, all cells have the Normal cell style applied.

Why should you use cell styles?

There are a few solid reasons to use cell styles:

• They allow you to set several formats in one step, saving you lots of clicks and time.
• They allow you to make formatting changes in one place that cascade across your entire workbook, saving a huge amount of time.  Often people don’t want to make changes mass changes like that because it takes so much time, but cell styles make it quick and easy!
• They help ensure your styles are applied consistently.  This gives your files a professional look, and can add control as well if colors or other formats are used heavily in communicating status or other important information.

Tips & tricks

Here are several tips to help you use cell styles more quickly and easily:

• If you use cell styles a lot, consider adding the cell style drop-down palette to your Quick Access Toolbar (QAT).
• When adding custom styles, consider naming them briefly and based on the intended usage rather than the formatting (which could change).
• Use and modify the built-in cell styles as much as possible to keep them manageable and help ensure consistency.
• Use the duplicate option (right-click a cell style from the drop-down palette) to use an existing cell style as a template without changing the existing style.
• Use themes to change all the theme-based styles in just one step.  The themes are helpful because the different cell styles flow together well.

Gotchas

Be aware of several gotchas related to cell styles:

• Unlike other built-in cell styles, the Normal cell style cannot be deleted, but it can be modified.  Just note that modifying the Normal cell style will affect all cells without a cell style already applied!
• When copying cells to another workbook, generally the cell style name is copied along with the cells.  If the cell style name has different formats applied in the new workbook, the cells may look different after they are pasted.
• Styles can be changed even if worksheet and cell protection is turned on.
• Borders can be tricky to apply with cell styles because of the nature of inside/outside borders.  When only one cell is selected, no inside borders are changed.
• Like fixed formats, cell styles are overwritten with conditional formatting.  If you are trying to apply cell styles and you’re not seeing the results you expect, check the cell range for any conditional formatting rules.

Macros That Help

The XLEV8 Excel Add-in contains several macros that help you work more efficiently with cell styles:

• Toggle Cell Style – this lets you toggle through your five favorite cell styles (and Normal) with one shortcut.
• Cell Style Picker – this lets you apply (and define) your five favorite cell styles (and other options) with one shortcut.
• Bulk Edit Cell Styles – this lets you add, modify, delete, and export cell styles in one bulk step.

Summary

If you want quickly make your Excel files polished and super easy to update when you want to change up some of the formats, then cell styles are an awesome option.  Use the built-in styles, or use them as templates to build your own.  Using themes is a great way to change a lot of styles at once and make sure they flow well together.  Make sure to take advantage of the tips, tricks, gotchas, and macros above to apply cell styles quickly and easily!

Have you used cell styles extensively in Excel?  Any additional tips or tricks to share?  Let us know in the comments below!