How to Format Spreadsheets in Microsoft Excel

Updated on March 24, 2017
This worksheet was created and formatted in Microsoft Excel 2010 - the process is exactly the same for Microsoft Excel 2007.
This worksheet was created and formatted in Microsoft Excel 2010 - the process is exactly the same for Microsoft Excel 2007. | Source

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:

Click thumbnail to view full-size

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

Click thumbnail to view full-size
Diagram 1: A simple worksheet with no number formatting.Diagram 2: The same worksheet with number formatting applied.
Diagram 1: A simple worksheet with no number formatting.
Diagram 1: A simple worksheet with no number formatting. | Source
Diagram 2: The same worksheet with number formatting applied.
Diagram 2: The same worksheet with number formatting applied. | Source

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

Click thumbnail to view full-size
Diagram 3: A simple worksheet without alignment,Diagram 4: The same worksheet with alignment applied.
Diagram 3: A simple worksheet without alignment,
Diagram 3: A simple worksheet without alignment, | Source
Diagram 4: The same worksheet with alignment applied.
Diagram 4: The same worksheet with alignment applied. | Source

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

Diagram 6: Borders have been added to the worksheet shown in Diagram 5.
Diagram 6: Borders have been added to the worksheet shown in Diagram 5. | Source

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

Diagram 7: Fill formats are applied to the worksheet in Diagram 6.
Diagram 7: Fill formats are applied to the worksheet in Diagram 6. | Source

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

Diagram 8: Font formats are applied to Diagram 7.
Diagram 8: Font formats are applied to Diagram 7. | Source

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:

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • Glenn Stok profile image

        Glenn Stok 

        3 years ago from Long Island, NY

        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.

      • TIMETRAVELER2 profile image

        Sondra Rochelle 

        5 years ago from USA

        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 profile image

        1st Class Travelr 

        5 years ago from Jackson, MS

        This is a very useful article...Thanks!

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)