Formatting Spreadsheets in Excel
This article will cover the basics involved in formatting a Microsoft Excel worksheet including formatting of cells, use of styles, merging and alignment, fonts and conditional formatting. Once completed you should be able to professionally format a worksheet so that it can be used in presentations.
Good formatting will help enhance your data in several ways:
- Formatting cells so they present data correctly (example: formatting as dates or currency) will give more value to your data.
- Using styles (such as table styles) can make your data stand out and helps the reader to focus on important parts of the worksheet.
- Merging and aligning your data is an important aspect of making your data more readable.
- Formatting your text by increasing the size, adding italics, bolding or changing the fonts will enhance the overall look of your worksheet.
- Conditional formatting is a great tool that allows you to graphically or visibly highlight important areas of your worksheet – as these tools are dynamic, as your data changes so does the highlighted area.
How to Format Cells in Microsoft Excel
The diagrams below show the various tabs in the Format Cells Dialogue Box:
There are many different ways to format cells within Microsoft Excel:
- Right clicking on a cell will show a menu with the most common formatting tools, plus a more detailed menu – on this detailed menu is an option to ‘Format Cells’ – clicking on this will open up the main Format Cells Dialog Box
- On the Home Tab of the ‘Ribbon’ is a Cells section. Clicking the small arrow beneath Format shows a small menu of formatting options – clicking the ‘Format Cells’ option will open up the main Format Cells Dialog Box.
- On the Home Tab there are various sections such as Font, Alignment, Number etc – these provide the most common elements from the main Format Cells Dialog Box – clicking the small arrow at the bottom right of each of these sections (where appropriate) opens the full dialog box.
The Format Cells Dialog Box contains the following tabs:
Number – How to change the Number Format in Microsoft Excel
One of the most important factors when formatting a worksheet is the format of a number. Without formatting, a number is simply a measure – when proper number formatting is applied it becomes information. When formatting is applied the reader will instantly know what the number signifies.
The Number section within the Format Cells dialog box contains the following categories:
(For examples of each of the formatting styles below see Diagram 1 and Diagram 2)
- General – this formats the cell with no specific number format.
- Number – this formats the cell as a number and allows customization of number of decimal places, whether to use a 1000 separator and how to display negative values.
- Currency – this formats the cell as a specific currency and allows customization of number of decimal places, the currency and how to show negative values.
- Accounting – this formats the cell as specific currency and also aligns the currency symbols and decimal points in a column. It allows for customization of the decimal places or the currency.
- Date – the formats the cell as a date. It allows varying styles of dates and the ability to select a locale.
- Time – the formats the cell as a time. It allows varying styles of time and the ability to select a locale.
- Percentage – this multiplies the cell value by 100 and displays the result with a percent symbol.
- Fraction – displays decimals as fractions. It allows for customization of the type of fraction – one digit, two digits, three digits, halves, quarters etc.
- Scientific – displays the cell in standard scientific format. It allows for customization of the decimal places.
- Text – treats all entries as text and will display cells as entered.
- Special – formats the cell using special formats such as Zip Code, Phone Number or SSN.
- Custom – allows for customization of the standard format codes.
Alignment - How to Merge Cells and Align Text in Microsoft Excel
While it isn’t obvious, alignment is a powerful formatting tool that will help to make your data more readable. With the ability to spread headings over columns, wrap text, change the orientation of the text and align the text within cells you are able to provide as much information as possible by using simple text.
(Diagram 3 shows a simple worksheet prior to alignment. Diagram 4 shows the same worksheet after alignment has been applied to it.)
The methods used were:
- Wrapping text within a single cell: Right Click on Cell D2 -> Select Format Cells -> Click on the Alignment Tab -> Click the check box next to Wrap Text -> Click OK
- Aligning and centering text over several columns: Select range D5 to G5 -> Right Click the selected range -> Select Format Cells -> Click on the Alignment Tab -> Click the check box next to Merge Cells -> Change the Horizontal alignment to Center -> Click OK
- Changing orientation of a range of cells: Select range C6 to C17 -> Right Click the selected range -> Select Format Cells -> Click on the Alignment Tab -> Click the check box next to Merge Cells -> Change the Horizontal and Vertical alignments to Center -> Change the Orientation to 90 Degrees ->Click OK
- Centering text in a range: Select range C6 to C17 -> Right Click the selected range ->Select Format Cells -> Click on the Alignment Tab -> Change the Horizontal alignment to Center -> Click OK
Border – How to Change the Border of Cells in Microsoft Excel
Adding borders to a worksheet are a great way to help readers categorize data – use of borders allows you to help focus the headings for rows and columns and define what are categories and what is data.
(Diagram 6 updates the Daiagram 5 and adds borders.)
The methods used were:
- Add a border around all data: Select range C5 to G17 -> Right Click the selected range -> Select Format Cells -> Click on the Border Tab -> Click on the single thick line on the right of the Style box ->Click on the Outline in the Presets box -> Click OK (Do the same for ranges C5 to C17, D6 to D17 and D5 to G6)
- Add a colored border: Select range C5 to G5 -> Right Click the selected range -> Select Format Cells -> Click on the Border Tab -> Click on the single thick line on the right of the Style box -> Change the color to Red, Accent 2 (hover over colors to see definition) -> In the Border section click the bottom area of the box (a red line should appear) -> Click OK
- Add dotted lines to all other internal cells: Select range CE6 to G17 -> Right Click the selected range -> Select Format Cells -> Click on the Border Tab -> Click on the first dotted line on the left of the Style box -> In the Border section click the center area of the box between the words text(do this horizontally and vertically – a dotted line should appear separating the words) -> Click OK (Do the same for range D7 to D17)
- If necessary, re-apply the thick border to range D5 to G6.
Fill – How to Change the Fill Settings of a cell in Microsoft Excel
The fill formatting options on Microsoft Excel add some depth to the data making the presentation easier to read and understand. With advanced features such as using fill effects or shading styles it is very easy to produce effects that can highlight your data.
(Diagram 7 updates the Diagram 6 and adds fill formats.)
The methods used were:
- Fill main title heading: Select range C5 to G5 -> Right Click the selected range -> Select Format Cells -> Click on the Fill Tab -> Click on Fill Effects-> Change Color 2 to Green-> Click OK
- Fill Name fields focusing on the center of the cell: Select range D7 to D17 -> Right Click the selected range -> Select Format Cells -> Click on the Fill Tab -> Click on Fill Effects-> Change Color 1 to Olive Green, Accent 3 and Color 2 to Olive Green, Accent 3 Lighter 80%-> change Shading Style to From Center ->Click OK
- Use shading styles: Select range G7 to G17 -> Right Click the selected range -> Select Format Cells -> Click on the Fill Tab -> Change Pattern Color to Red -> change Pattern Style to 6.25% Gray->Click OK
Font - How to Format Text in Microsoft Excel
The size, formatting and style of the font can make a huge difference to the look and feel of your Microsoft Excel spreadsheet. With thousands of fonts available you will be able to enhance your spreadsheet in many ways.
(Diagram 8 updates the Diagram 7 and adds font formats.)
The methods used were:
- Change the font of the main headings: Select range D6 to G6 -> Right Click the selected range -> Select Format Cells -> Click on the Font Tab -> Under Font, scroll down and click on Lucida Console-> Click on Bold under Font Style-> Click OK
- Changing a character in a heading to superscript: Click on cell D6 -> In the edit box highlight ‘1’ -> Right Click the highlighted ‘1’ -> Select Format Cells -> Click on the Font Tab -> Click the checkbox next to Superscripts in the Effects box -> Click OK
- Change the color of a font: Click on cell D8 -> Right click the cell -> select Format Cells -> Click on the Font Tab -> Using the drop down arrow, change the color to Red – Click OK
Advanced Formatting Options
The article above has described the basics of formatting a worksheet in Microsoft Excel - for the final spreadsheet I have used some advanced formatting options.Microsoft Excel has many advanced options that allow you to:
- Add and format pictures
- Add Smart Diagrams
- Add Shapes
- Add Word Art
- Add complex conditional formatting
- Add Graphs
It is beyond the scope of this article to cover the advanced techinques, but many of these techniques are used in the articles below:
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
Glenn Stok from Long Island, NY on July 27, 2014:
I use Excel extensively as I find it useful to keep track of many things that I do in my business and even in my personal life. But I have to admit that I never used most of these formatting features that it provides. I found your hub very informative and useful. And I look forward to applying some of the things I learned from you on my spreadsheets.
Sondra Rochelle from USA on January 16, 2013:
Very thorough and well done article. I have never used excel but know it is THE spreadsheet program most people use. I love spreadsheets and use them for everything that helps me keep my life organized. Voted up.
1st Class Travelr from Jackson, MS on December 15, 2012:
This is a very useful article...Thanks!