Advanced Formatting in Excel 2010

Comments
Comments can be added to an Excel document for many purposes such as:

  • Reviewing a document.
  • Giving instructions to people completing a form.
  • Explaining how a calculation works.

Create a Comment

To create a comment:

  • Highlight the cell the comment is to be added to.
  • On the Review tab, in the Comments group, click the New Commentbutton.
  • Enter your comment in the yellow box that appears. Leave your name in the box as it shows who made the comment.

Hide and Show Comments

Comments hide by default leaving only the little red triangular marker in the top right hand corner of the cell visible. Hover the mouse over a cell with the comment marker to temporarily display the comment. To display all comments on the sheet:

  • On the Review tab, in the Comments group, click on thebutton. This button is a toggle so when comments. are displayed they will become hidden and when hidden they will become continuously shown.
  • Individual comments can be continuously shown or hidden using the button.

Edit a Comment

To edit an existing comment:

  • Click into the cell containing the comment.
  • On the Review tab, in the Comments group, click the Edit Comment button.
  • The comment will be displayed and be ready for editing.

Delete a Comment

To delete an existing comment:

  • Click into the cell containing the comment.
  • On the Review tab, in the Comments group, click the Delete button.
  • The comment will be deleted.

Working with Cell Styles

Cell styles are pre-set formatting that can be applied to a cell. They use the colours and fonts of the document theme in conjunction with additional cell formatting such as borders and backgrounds. Despite the names they have no other function, they simply format the cell.

To apply cell formatting:

  • Select the cell or cells to be formatted.
  • On the Home tab, in the Styles group, click the button.
    NOTE: Depending on your screen size you may see the gallery immediately on the ribbon, rather than the Cell Styles button.
  • The gallery of styles above will appear.
  • Hover over the desired style to preview what it will look like.
  • Click the chosen style to apply to the cell(s).

Using Format Painter

Format painter allows you to copy formatting from one place to another. This may be on the same sheet, or across sheets or even files. This is useful to ensure consistency but also if formatting has gone wrong to correct these errors.

To use the format painter to copy formatting to a single cell or group of adjacent cells:

  • Select the cell(s) containing the formatting to be copied.
  • On the Home tab, in the Clipboard group, click the button.
  • Move the mouse to the start of the cell(s) you wish to apply the formatting to.
  • Note that the mouse pointer now has a matching paintbrush icon next to it.
  • Click into the cell you wish to apply the formatting to. Or if applying the formatting to a group of adjacent cells click and drag from the first to the last cell in the group.
  • The formatting will be applied.

To use format painter to copy formatting to a group of non-adjacent cells:

  • Select the cell(s) containing the formatting to be copied.
  • On the Home tab, in the Clipboard group, double click the button.
  • Move the mouse to the first of the cells you wish to apply the formatting to.
  • Note that the mouse pointer now has a matching paintbrush icon next to it.
  • Click into the cell.
  • Move to the next cell you wish to apply the formatting to (Note that the mouse pointer still has the paintbrush icon next to it).
  • Continue to the do this until all the cells have been formatted.
  • Click the brush to switch it off.

Using Table Formatting

Tables in Excel can also have built in formatting applied. This does much more than just formatting the table it also facilitates the inclusion of automatic table totals as well as integrating sorting and filtering amongst others.

To apply table formatting:

  • You need to create a minimum of column headers (in what will be row 1 of your table) and the first set of data (in what will be row 2 of your table).
  • Include any formulas in row 2 as the table will then automatically copy these down to all table rows.
  • Format the cells in row 2 so that they are using the correct number format. You do not need to format for cell colour or borders but column width and alignment/text wrapping can be set now or after applying the table formatting.
  • Click into any cell of the table
  • On the Home tab, in the Styles group, click the button.
  • The gallery of table styles appears.
  • Choose a table style by clicking on it.

    Table styles do not give you a preview

    at this stage.

  • You must now confirm that Excel has found the correct cells for your table and if you have a header row. Assuming you have set your table up as described above the default will always be correct.
  • Your spreadsheet will now include the formatted table
  • And the Table Tools ribbon tab will be displayed which allows you to customise the table
  • You could insert a Total Row by ticking the Total Row checkbox in the Table Style Options

    The totals can be customised in each column by clicking the drop down arrow which appears in the cell when you click into it. Choose the function you want to use in the total row e.g. Sum to total up the values in that column.

  • The table can be extended at any time by dragging the icon in the bottom right of the table. Additional rows and columns are then included in the table. Rows added this way will automatically be populated with calculations. If you have the total row switched on this will always be the last row.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s