Latest news

Excel’s best feature hidden in plain sight – Using Styles

No comments

Styles are a reasonably new feature, being significantly upgraded in Excel 2007 for Windows (earlier verisons had much reduced functionality), but not making it to the Mac version until Excel 2011, inexplicably being left out of Excel 2008 for Mac, along with all macro/VBA functionality. I love styles so much that 10 years ago wrote a .xla file that I used to keep formatting consistent throughout a model (although far inferior to what MS ultimately incorporated, as it was very slow, having to look at each cell individually). And when I found out that they’d been included in Excel 2011, I went and upgraded the whole Office suite on the spot (which if the company you work for is a participant in Microsoft’s Home Use Program, is well worth it).

What are Styles

Styles are a set of preset, editable formats that you can apply throughout your workbook. Excel contains a good number of built-in styles straight out of the box, such as Normal, Output, Heading1, Total, Input etc. These are all capable of being customised to your liking, or you can create new ones.

Why they’re good

For two key reasons – consistency and time.

Consistency

You’ve put a lot of work into producing a spreadsheet for your company’s management, that uses multiple worksheets. Before the file is circulated, someone else reviews your work and decides that they don’t like the font you’ve used on the page they’re looking at, and also doesn’t like your heading format. Before Styles, they would highlight the cells they wanted to change, change the format to their liking, and then send the file out to a broader audience. But chances are, they didn’t go through every sheet (or even every part of a sheet), so the different sheets all look different. But as the creator of that work, the audience will think that it’s you who has created a messy piece of work. With Styles, the reviewer can just change the formats for the appropriate style types (in roughly the same time as it takes to change the format of one cell) and the changes will flow through the full workbook.

Time

I don’t think I’ve ever produced a spreadsheet that someone didn’t want some formatting changes made to (there’s no accounting for some people’s taste!). The usual way of making this sort of change would be to change the format in one cell, and then copy and paste formats across all the applicable cells. With multiple format changes over multiple ranges for each format type, this was a time consuming process. With Styles, the change can be made once, with no copying and pasting necessary.

How to use them

Styles appear in the Format block of the Home tab. Depending on how wide your window is, you’ll either see a Styles button which has a dropdown box when you click on it screen shot - styles dropdown

or a block of installed styles. screen shot - styles boxTo apply a style to a cell (or range of cells) simply highlight the cell and click the style you wish to apply. To change a style, right-click the style name in the Home tab and select Modify. By default, all cells are set to the Normal style.
For more detailed explanations of how to use Styles, the Microsoft Office website has a pretty reasonable walkthrough.

What next?

Just start playing around with using Styles in your work, and you should find you save a lot of time. And when you’re comfortable with Styles, look at creating Themes as well, to keep formatting consistent across different workbooks, rather than just within the one workbook. And don’t forget that Styles and Themes can also be used in Word and Powerpoint, which benefit even more from consistent formatting.

email
Brendan WalpoleExcel’s best feature hidden in plain sight – Using Styles

Leave a Reply

Your email address will not be published. Required fields are marked *