Formatting in Excel

Formatting in Excel (2016, 2013 & 2010 and Others)

Formatting in excel is a neat trick in excel which is used to change the appearance of the data represented in the worksheet, formatting can be done in multiple ways such as we can format the font of the cells or we can format the table by using the styles and format tab available in the home tab.

How to Format Data in Excel? (Step by Step)

Let us understand the working on Data formatting in excel by simple examples. Now let us suppose we have a simple report of sales for an organization as below:

You can download this Formatting Excel Template here – Formatting Excel Template

Data Formatting example 1

This report is not attractive to viewers; we need to format the data.

Now to format data in excel, we will make

  • The text of the column head bold,
  • Font size larger,
  • Adjust the column width by using the shortcut key (Alt+H+O+I) after selecting the entire table (using Ctrl+A),
  • Center aligning the data,
  • Apply the outline border by using (Alt+H+B+T),
  • Apply background color by using the ‘Fill Color’ command available in the ‘Font’ group on ‘Home.’

We will be applying the same format for the last ‘Total’ row of the table by using the ‘Format Painter’ command available in the ‘Clipboard’ group on the ‘Home’ tab.

Data Formatting (Format painter)

As the amount collected is a currency, we should format the same as currency using the command available in the ‘Number’ group placed on the ‘Home’ tab.

Data Formatting (currency)

After selecting the cells, which we need to format as currency, we need to open the ‘Format Cells’ dialog box by clicking the arrow marked above.

Choose ‘Currency’ and click on ‘OK.’

Data Formatting (currency english india)

We can also apply the outline border to the table.

Data Formatting (outside border)

Now we will be creating the label for the report by using ‘Shapes.’ To create the shape above the table, we need to add two new rows, for that we will be selecting the row by ‘Shift+Spacebar’ and then insert two rows by pressing ‘Ctrl+’+” twice.

To insert the shape, we will be choosing an appropriate shape from the ‘Shapes’ command available in the ‘Illustration’ group in the ‘Insert’ tab.

Data Formatting (insert shapes)

Create the shape according to the requirement and with the same color as the heads of the column and add the text on shape by right-clicking on the shapes and choosing ‘Edit Text.’

Data Formatting (Edit)

We can also use the ‘Format’ contextual tab for formatting the shape using various commands as ‘Shape Outline,’ ‘Shape Fill,’ ‘Text Fill,’ ‘Text Outline,’ etc. We can also apply the excel formatting on textExcel Formatting On TextText formatting in Excel include changing the colour, font name, font size, alignment, font appearance in bold, underlining, italic, background colour of the font cell, and so more using the commands available in the ‘Font’ group placed on the ‘Home’ tab.

Data Formatting (format)

Data Formatting (sales Report)

We can also use ‘Conditional Formatting’ to taking the attention of the viewers for ‘Top 3’ salesperson and ‘Bottom 3’ salesperson.

Data Formatting (conditional formatting - Top bottom Rules)

To format the cells that rank in the top 3 with Green Fill with Dark Green Text.

Data Formatting (Format Cells)

Also, format the cells that rank in the Bottom 3 with Light Red with Dark Red Text.

Data Formatting (Format Cells - bottom rule)

We can apply other conditional formatting option also, which is ‘Data Bars.’

Data Formatting (conditional formatting - data bars)

We can also create the chart to display the data, which is also the part of ‘Data Formatting Excel.’

Data Formatting final sales report

Shortcut Keys to Format Data in Excel

  • To make the text bold: Ctrl+B or Ctrl+2.
  • To make the text italic: Ctrl+I or Ctrl+3.
  • To make the text underline: Ctrl+U or Ctrl+4.
  • To make the font size of the text larger: Alt+H, FG.
  • To make the font size of the text smaller: Alt+H, FK.
  • To open ‘Font’ Dialog box: Alt+H,FN
  • To open ‘Alignment’ Dialog box: Alt+H, FA.
  • To center align cell contents: Alt+H, A then C
  • To add borders: Alt+H, B
  • To open the ‘Format Cell’ Dialog box: Ctrl+1
  • To apply or remove strikethrough Data formatting Excel: Ctrl+5
  • To apply an outline border to the selected cells: Ctrl+Shift+Ampersand(&)
  • To apply the Percentage format with no decimal places: Ctrl+Shift+Percent (%)
  • To add a nonadjacent cell or range to a selection of cells by using the arrow keys: Shift+F8

Things to Remember

  • While data formatting in excel makes the title stand out, good and bold, and make sure it says something clearly about the content we are showing. Next, enlarge the column and row heads just a bit and put them in a second color. Readers will quickly scan the column and row headings to get a sense of how the information on the worksheet is organized. This will help them see what is most important on the page and where they should begin.

Recommended Articles

This has been a guide to Formatting in Excel. Here we discuss how to Format Data in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion