Tag Archives: cell styles

Reasons to Format Excel Spreadsheets with Cell Styles

Excel, Office, , , , , , ,

Use Cell Styles to Format in Excel to save time and frustration and look smart doing it!

Here’s why you want to use Cell Styles to format Excel data:

  • Apply professional formatting to a worksheet in a flash.
  • Consistency across worksheets.
  • Built-in styles are labelled according to purpose for easy use.
  • Styles are customizable so you can edit existing styles or create your own to match your needs.
  • One click formatting.

Have you ever wondered how to format a spreadsheet in a hurry? Here’s a simple example of a worksheet with boring, unformatted data:

Name Region Q1 Q2 Q3 Q4 Total
Smith South 21223 17855 24855 21377 85310
Jones Southwest 23456 29550 24294 25335 102635
Wing North 19954 22600 19448 26450 88452
Baker South 17564 25439 32944 24000 99947
Folsom North 28543 28540 24400 28550 110033
Range Northwest 19534 30558 21844 19605 91541
McKnight West 20585 29667 27595 13605 91452
Crump South 24957 31322 15330 27550 99159
Rogers North 30332 27407 26440 24007 108186
Morris East 25395 27700 16500 27500 97095
Rollins South 35822 31854 24384 31005 123065
Jefferson Southwest 20949 23100 21774 27550 93373

But now you get a call to send it to a co-worker or the boss or it has to be ready for a presentation. Using individual formatting tools, it can take longer to format than it took to create it!

Format the Column headers and Data Content

Want to look smart really fast? Here’s the fix…First, I want to format the header row:

Select the header row in the worksheet

On the Home tab, in the Styles group, click Cell Styles icon:

If you have a larger screen and/or higher resolution, you may see several cell styles already displayed. See all styles by clicking the More arrow button at the bottom of the scroll bar for the group.

This box below will display with lots of options that are labelled for specific uses but you can use them for any purpose you choose. Roll your mouse over the sections and because they are live preview, you can see the results before you actually choose the option. I want Heading 1 for my header row, so I point and click to apply it.

I’ve selected Heading 1 under the Titles and Headings section:

Next select the body of the spreadsheet:

Click Cell Styles again, and choose whatever style you think would look best for your data. (If you are using Input Style, don’t include the Total column as you’ll probably want to use the Calculate Style for that to indicate formulas).

Total Row Style

If you have a total row, quickly make your totals stand out with the Total Style:

Data and Model Section

A way to alert users about which data they can edit is to use the Input and Calculation styles under the Data and Model section. It can’t stop them from overwriting formulas, etc., but it does signify the ranges that they should not edit. If you don’t like the default colors, you can change them. (See below)

Remove Styles

So, you’ve been playing and got carried away and now your data looks like a kindergarten project! Don’t worry if you goof and want to remove a style. Just select that range of cells; go back to the Cell Styles icon to the Style group and click on Normal to reset the cells to your default font style and size. (Located in the upper left of the first section, Good, Bad
and
Neutral).

Customize/Create Styles for Text

You can modify any style or leave the original styles and create a duplicate for your new style. I would recommend the latter. Here’s how to make changes in font, color, etc. for any style:

Example here is for Heading 1:

  • Click the Cell Styles icon (Home tab, Styles group).
  • Right click over the style you want to change.
  • Choose Duplicate (or Modify if you want to edit the original style) to display the Style box:

  • Change the name.
  • Click the Format… button and make desired changes. (Ensure Font tab is selected).
  • Click OK, OK.

Changes to Numerical Data Styles

If you want to change the Input or Calculation or any other numerical styles to add number formatting as well, the process is the same except you:

  • Right click over the style name.
  • Choose Duplicate.
  • Type in a new style name.
  • Click on the Number tab in the Format Cells dialog box.
  • Click on Currency in the Category pane.
  • Choose if you want the style to always display 2 decimal places or change to 0 if you want that for the style.
  • Make any other desired changes to Font, Alignment, etc., using the tabs at top.
  • Click OK, OK.

Apply Customized Styles

This can be a little confusing after modifying a style as it looks like nothing happened. The new style is not applied to your selected data. because you have created the style but not yet applied it.

When you customize or create new styles, Excel adds a Custom section to the top of the Cell Styles list. Just select desired data, click Cell Styles and click on your customized style. I added Currency formatting to the Input Style but left the background cell color; applied Currency formatting for the Calculate Style but changed to red color so it would be obvious not to enter data in those cells:

I selected the quarterly data to apply my customized Input Style, and then selected the Total column to apply my new Calculation Style. If there is also a Totals row, I would select them both first, and then go to Cell Styles and click my customized style:

Create your own styles and save a bundle of time while looking darn professional as well!

Let me know how it works for you…

The Magic of Excel Tables

Excel, Office, , , , , ,

 

Table Benefits

This terrific Excel feature does many things automatically including formatting, formulas and much more. It is very easy to convert worksheet data to a table and convert back to a worksheet range with one mouse click, if needed. (Note, prior to the 2007 version, Tables were called Lists). 

Here’s some treasures you get by Inserting a table:

  • Creates a sophisticated worksheet in a flash. Inserting a table auto formats your data with the built-in style and applies filtering to each column for easy data analysis.
  • Auto selects all the data for the table range. (Make sure you have column headings and don’t have blank rows or columns in your data. Insert a blank row if you don’t want all data in the table).
  • Instant access to a Total Row function where columns can be summed, averaged, counted and more.
  • Insert Table Rows and not affect data cell referencing in columns outside of the table.
  • Easy to select entire data or column or row no matter how large the table.
  • You can name a table and use it in a formula.
  • Change the table style with one mouse click. Live Preview displays how your table would look as you point at each style.

Create a Table in a Flash

You have checked the worksheet and removed any unwanted blank columns and rows:

  • Click anywhere in your data
  • Click the Insert tab on the Ribbon
  • Click the Table icon in the Tables group (Excel will display the Create Table dialog box and auto insert the cell range but be sure there is a checkmark in My Table has headers)

Presto! Excel has applied its default Table format and inserted filters for each column.

When you really want to impress or in a hurry, just click anywhere in your data and press CTRL T to auto insert your table!

Select the Table

Tables make it a snap to select all the data with or without the column headers:

  • Point your mouse to the very upper left of the very first table cell (column header).
  • When mouse pointer changes to a small, black down-angled arrow, single click for data only, or double-click for entire table. (If you double-clicked too slowly and only highlighted data, just single click and now column headers are included).

Customize the Table

Add a column to the table or click below the last row and enter data, and the table is automatically expanded along with the formatting to include the new cells.

Add the Total Row feature. Click in any cell in the table and then, on the Ribbon, under the Table Tools / Design tab in the Table Style Options group, click in the Total Row box to automatically add a Total Row and sum to bottom of the table. Click in any cell in that new row to display a drop down arrow and choose a function, i.e., average, count, etc.

Table Restrictions

A couple of things to be aware of in the table:

  • You cannot create Groups and Outlining within a table. The SUBTOTAL function that is used at the bottom of each column allows selection of function formulas such as SUM, AVERAGE, COUNT, etc., that apply to the entire column.
  • You cannot share a spreadsheet that contains a table.

Convert a Table Back to a Range

In a hurry to share or need to group your data? The good news is you can use the one click method to convert to a data range:

  • The Design contextual tab for the Table is displayed on the Ribbon.
  • Click Convert to Range in the Tools group. (Excel asks if you really mean it).
  • Click Yes, and you are back to the data range but the table formatting style and the column filtering is still applied. This can be handy because it still has those table benefits but you can now group and outline and/or share the worksheet.

Remove Table Formatting

If you want to be back to Square 1 so the table formatting and filters are completely removed, you have  some choices depending on whether you apply before or after Convert to Range, and if you want to retain your font and numerical formatting.

After converting table back to a range: The table style and filters are still applied.

  • Ensure all relevant data is selected.
  • Click the Home tab, and then click the Cell Styles arrow in the Styles group, and choose None.
  • Another option is to click the Clear arrow in the Editing group and choose Clear Formats.

Be aware that both these methods remove the table formatting style but you may have to reformat font and numerical formatting.

Want to have all the formatting back to its state before you inserted the table? Apply these steps to remove all formatting before you Convert to Range.

Before converting table back to range:

  • In the Design tab on Table Tools, click the More drop down arrow button in Table Styles group.
  • Choose the first icon under Light which is None.
  • Click in the Filter button icon in the Table Styles Options group to remove the filtering icons from the columns.
  • Click Convert to Range in the Tools group and confirm.

You now have your spreadsheet back just as you left it. Experiment with tables. I think they will save you time and frustration, especially in those humongous worksheets!