What is Formatting in Excel?

Formatting in Excel means a trick that we can use to modify the data’s appearance in a worksheet. We can format the data in various ways, like we can format the font of the cells or the table with the help of the styles and format tab present in the Home tab.

It’s easier than ever to format worksheet (or sheet) data in Excel. There are various quick and easy ways to generate professional-looking worksheets that efficiently present our data. For example, we can utilize document themes to give our Excel spreadsheets a consistent design, style to apply predetermined formats, and other manual formatting capabilities to highlight essential data.

Microsoft Excel has several features that permit users to customize the way their data is displayed. And there is a solid reason for it: formatting cells can help bring attention to essential data or show the content more properly (such as adding $ to cells which comprise price values or configure cells that represent dates to a standard display of xx/xx/xxxx).

Excel formatting is an optional step following data preparation, or all of the data cleansing, structuring, enriching, and standardizing necessary to prepare the data for analysis.

New data rarely comes without its own unique set of issues; it is up to the analysts to analyze their data and guarantee that it is ready to meet the exact requirements of their analytical project. Splitting columns, eliminating rows with incomplete data, and standardizing against a certain name.

Once completed, Excel formatting adds the finishing touches, ensuring that the data is properly prepared and presented.

Good formatting will improve our data in various ways:

• With the help of the formatting, we can present our data correctly; for example, formatting as dates or currency will provide more value to our data.
• Merging and aligning our data is a vital aspect of making our data more readable.
• Formatting our text by increasing the Size, bolding, adding italics, or changing the fonts will improve the overall appearance of our worksheet.
• Using styles (like table styles) can make our data stand out and helps the reader to focus on crucial portions of the worksheet.
• Conditional formatting is a useful tool for highlighting crucial portions of our worksheet graphically or visibly. These are dynamic tools. The Highlighted region changes as our data changes.

Benefits of Data Formatting in Excel

The following are some of the benefits of Excel data formatting:

• The data appears to be more presentable.
• Data formatting saves a lot of time and effort.
• With the help of the chart, we can analyze the data.
• With the help of the formatting, we can highlight specific data such as profit or loss in business.

How to Format Data in Excel?

Let’s have a look at some easy instances of data formatting in Excel. Suppose we have a simple sales report for a company that looks like this:

We need to format the data in this report because it is not attractive to viewers.

Now, in order to format data in Excel, we’ll do the following things:

• The Font size is larger.
• We make the text of the column head bold
• Center aligning the data
• We will apply the outline border with the help of the shortcut that is (Alt+H+B+T),
• After selecting the full table (using Ctrl+A), use the shortcut key (Alt+H+O+I) to adjust the column width.
• To alter the background, use the ‘Fill Color’ command in the ‘Font’ group on ‘Home’.

We will use a similar format for the ‘Total’ row, which is the last row of the table, with the help of the ‘Format Painter’ command, present in the ‘Clipboard’ group on the ‘Home’ tab.

Because the amount gathered is in a currency so, we have to format the same as currency, using the command available in the ‘Number’ group which is placed on the ‘Home’ tab.

After selecting the cells, that need to be formatted as currency, click the arrow above to open the ‘Format cells’ dialogue box.

Select the ‘Currency’ option and click on ‘OK.’

We can also apply the outline border to the table.

Now our next task is to generate a label for the report. With the help of the Shapes, we will make the report’s label. If we want to create the shape above from the table, we’ll need to add two new rows. To do so, we have to select the row by ‘Shift+Spacebar’ and then press ‘Ctrl+’+” twice to insert two rows.

Now we can insert the shape, to insert shape, go to the ‘Shapes’ command in the ‘Illustration’ group of the ‘Insert’ tab and select an appropriate shape.

Create the shape that meets the requirement and add text to it by right-clicking on the shape and selecting ‘Edit Text.’

We may also utilize the ‘Format’ contextual tab to format the shape with commands like ‘Text Outline’, ‘Text Fill’, ‘Shape Fill’, Shape Outline.’ etc. We can also use the excel formatting on text using the commands available in the ‘Front’ group, which is placed on the ‘Home’ tab.

Conditional Formatting can also be used to draw the visitor’s attention to the ‘Top 3’ and ‘Bottom 3’ salespeople. To perform this, we have to select the option Top/Bottom rules in the conditional formatting and then we have to select the option named Top 10 Items.

After that, the dialog box will open, and in this dialog box, we have to fill the number for top ranks and the color we want.

Similarly, we can do this for Bottom 3.

We can also use other options of conditional formatting, such as Data Bars.

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

Shortcut Keys to Format Data in Excel

There are various shortcut keys to format data in Excel:

• Ctrl+2 or Ctrl+B, we can use this shortcut to make the text bold.
• Ctrl+3 or Ctrl+l if we want to make the text italic, then we can use this shortcut.
• Ctrl+4 or Ctrl+U if we want to underline the text, then this shortcut is helpful.
• Alt+H, FA shortcut is used to open the ‘Alignment’ Dialog box.
• Alt+H, FN shortcut is used to open the ‘Front’ Dialog box.
• Alt+H, FK is used to make the font size of the text smaller.
• Alt+H, FG is used to make the font size of the text larger.
• Alt+H, B is used to add border.
• Alt+H, A then C is used to center align cell content.
• Ctrl+1 is used to open the ‘Format Cell’ Dialog box.
• Ctrl+Shift+Ampersand (&) is used to apply an outline border to the selected cells.
• Ctrl+Shift+Percent (%) is used to apply the percentage format with no decimal places.
• Shift+F8 is used to add a non-adjacent cell or range to a selection of cells.
• Ctrl+5 is used to apply or remove strikethrough Data Formatting Excel.

Note: While data formatting in Excel makes the title stand out, bold, and appealing, it also guarantees that it clearly relates to the content we’re presenting. Then slightly increase the column and row headings and color them in a different hue. Readers will rapidly scan the column and row headings to understand how the worksheet’s content is organized. This will aid them in determining the most crucial information on the and page and where they have to start.

What is Format Cells in Excel?

In Excel, the format cells is used to modify the formatting of cell numbers without modifying the actual number. With the help of the format cells, we can change the number, alignment, font style, Border style, Fill options, and Protection.

We can access this option with right-click of the mouse. After right-clicking, a pop-up will display, and then we have to click on the Format Cells, or we can also use the shortcut key Ctrl+1 on our keyboard.

Format cells has five tabs for formatting the cells. Using this, we can modify the data style, Alignments, time style, insert the border with a different style, protect the cells, etc.

Number Tab

Excel Number Tab is used to change the decimal formatting of number cells, offering the appropriate format in terms of number, date, percentage, fractions, and so on.

With the help of the Number Tab, we can format the numbers as per our requirements. We can select from various choices such as currency, dates, times, percentage, etc.

Alignment Tab

The alignment tab can be used to align the cell’s text and merge the text of two cells together. If the text is hidden, we can use the wrap text to display it appropriately, and align the text in the desired direction.

Within the Formal cells dialogue box, the Alignment tab allows us to specify how our values are aligned, both horizontally and vertically.

Font Tab

With the help of the Font Tab, we may alter the font size, font style, font color, etc. we can change the font effects, we can underline the text, and preview how it will appear.

Quick Font changes can be made from the home tab; however, the Formal Cells dialog box is more efficient for mass changes. From there, we can easily modify the typeface, font size, italicize, point size, bold underlining, italicize, and, color across the whole selection of cells.

Border Tab

By using Border Tab, we can create colorful border line for various styles; if we don’t want to provide the border outline, we can leave it blank.

We can create borders around a single cell or a group of cells in Excel. We can choose the thickness, color, and line style of the lines and where they will be created (for example, only on the cell’s top or on all horizontal sides).

Fill Tab

We may use the Fill Tab to fill a cell or a range with colors in various ways, combine two colors, and even put a picture into a cell using the Fill option.

Protection Tab

The Protection tab can be used to protect cell, range, cells, sheets, a formula containing, etc.

The Protection tab doesn’t apply unless we’ve already protected our worksheet. To do this, click on Protection in the Tools menu, select Protect Sheet, and then select the Contents check box to determine how the worksheet will be protected. When the Locked option is selected, we are forbidden to do the following:

• Modifying the data or formulas of a cell.
• Typing data in an empty cell.
• Moving the cell.
• Resizing the cell.
• Deleting the cell or its contents.

When we choose the Hidden option, all the formulas used to calculate values will no longer be viewed in the formula bar (although we can still see the end result of that formula).

Formatting in Excel Example 1:

We have the below-mentioned disorganized data, which appears to be quite straightforward. Now we’ll use Excel to format the data and show it in a readable fashion.

The following are the steps that must be followed when formatting data:

• First, we have to select the header field and make it bold.
  
• Then, we will select the entire data and select the option named “All Borders” under the border.
  
• As a result, the data will appear as follows:
  
• Now, we will choose the header field and use the “Thick box borders” option under the border to create a thick border.
  
• After that, the data will appear as follows:
  
• Next, we will make the header Field in the center.
  
• Also, select a background color other than white. We’ll use a light green color here.
  
• Now the data appears more presentable.
  

Formatting in Excel-Example 2

Let us understand more about formatting with the help of the following example.

In this example, we have a day-wise weather production of different cities.

Now we have to highlight the Friday data.

• First, we have to select the entire record of that data means the data of column “F”, and then will do the right-click. After that, the drop-down box will appear. We will select the option named “Format Cells”.
  
• The Format cells dialogue box will appear. As we can see, there are many possibilities for changing the font style in various ways.
  
• With the help of the Number button, we can modify the data type such as percentage, decimal, date, time etc.
  
• With the help of the Alignment button, we can align the font in a different style.
  
• Click on the Font button. There are numerous font styles options available for this button. We can alter the font type, size, style, and color, etc.
  
• In this example, we’ll use a bold font size of 11 and the color green. By clicking on the Border button, we can also make the data’s border more evident.
  
• Now our data looks like the screenshot below:
  

Formatting in Excel-Example 3

In this example. We’re using the same data as before. In this, we will try to present the data in a pictorial format.

• Select the entire data set and go to the insert menu. Select Stacked Column from the 2-D column chart by clicking on Column Chart.
  
• Now our data looks like the below screenshot.
  

Formatting Tricks for Excel Users

The majority of Excel users are familiar with basic numeric and text formatting. Users who go beyond the basics, on the other hand, will produce more readable and effective sheets. Furthermore, knowing how to quickly and simply apply the appropriate formatting to specific cells helps users work more productively. These formatting tips will help users in getting the most out of Excel’s formatting features.

1. Copy Styles Between Workbooks

If we use similar cell styles in multiple workbooks, don’t waste time re-creating custom cell styles. Rather, copy the style from one file to another in the following manner:

• First, we have to open the source workbook and a destination workbook.
• Then from the destination workbook, we have to click on the Cell Styles in the Styles group on the Home tab. In Excel 2003, select Styles from the Format menu.
• Next, we have to choose Merge Styles at the bottom of the gallery.
• In the resulting dialog, select the open worksheet, which comprises the styles we need to copy.
• Click on OK twice.

Open Excel’s default workbook, book.xltx. as the destination, if we want all new workbooks to have the same custom design. (In Excel 2003, open book.xlt). After we have added the style, save and close the template file. The unified styles will be included in all new workbooks based on the book.xltx.

2. Add a Background Image

It is so easy to add a background image to the sheets that we might be tempted to brighten up all our sheets. (Of course, we are not going to do that). To add an image to the background of the sheet, perform the following:

• First, we have to click on the Page Layout tab.
• Then, we have to click on the Background option in the Page Setup group.
• Lastly, browse to the file and double-click it.

3. Quickly Apply Table Formatting

If we select a range and choose a built-in format from the Format As Table drop-down, Excel (2007 and later) converts the range to a Table object. If the format works for us, but we do not need the table object, we can keep the format and dump the table.

It takes a few clicks to do, but probably less than formatting it manually. To quickly format a data range using the built-in table format, do the following:

• First, we have to click anywhere inside the data range.
• Then on the Home tab, we have to click on the Format As Table drop-down and select a format from the gallery.
• Select properly when Excel asks if the range has headers and click OK.
• Click anywhere within the table.
• With the contextual Design tab current, select Convert To Range in the Tools group.
• Then we have to click Yes to confirm the action.

4. Create a Cell Style That Indicates Purpose

The usage of a Cell Style to determine the purpose helps users in acclimating faster. It’s also a simple technique for a company to maintain uniformity. For example, the color could be used to separate input and label cells. The use of a Cell Style is a quick and easy technique to implement the rule. Make a Cell Style for input cells to demonstrate this concept:

• Click on the Home tab and then click Cell Styles group. In Excel 2003, select from the Format menu and skip to the 3rd step.
• Then we have to click on the New Cell Style at the bottom of the list.
• Enter a name for the style in the resulting dialog box, like Input cell.
• Then click on the Format. In Excel 2003, we have to click on Modify.
• Click on the Border tab and select the Outline option in the Presets section.
• Select light blue from the Fill tab.
• Click OK to view the selected formats as shown below:
• Click OK again.

5. Change Colors in a Snap

Suppose we have made a spreadsheet model or dashboard. We also want to modify the colors to something more vibrant. Simply select a color scheme from the Colors box on the top left of the Page Layout ribbon. Microsoft has come up with some fantastic color palettes. These are nicely contrasted and look fantastic on our computer screen. We can create our own color schemes as well (to match corporate style). Furthermore, we can set font schemes or combine the two to create a new theme.

6. Use Format Painter

Format painter is a lovely tool that comes with all office programs. This is useful for copy formatting from one place to another. See the below screenshot in order to understand how it works. Format painter is located on the top left corner of the Home ribbon.

After applying this formula, the output is:

7. Formatting Keyboard Shortcuts

There are a number of shortcuts in Excel which we can use to format our data. Formatting is an everyday activity. We do it while composing an email, preparing a report, creating a workbook, making a presentation or drawing something. Knowing a few formatting shortcuts will help us improve productivity. The following are some formatting keyboard shortcuts:

• Ctrl+1: – Using this shortcut, we can open the format dialog for whatever we’ve chosen (charts, drawing shapes, charts, etc.).
• Ctrl + B: – This shortcut is used to bold the text.
• Ctrl + U: – This shortcut is used to underline the text.
• Ctrl + I: – This shortcut is used to Italicize the text.
• ALT + EST: – We used this shortcut to paste formats. It works like the format painter.
• Ctrl + T: – We used this shortcut to apply formatting to the current region of the cells.
• ALT + Enter: – While editing a cell, we can use this shortcut to add a new line.

8. Use Templates

It may be enjoyable and challenging to develop our own spreadsheets, but there is no reason to do so with so many pre-made templates available. For example, we can get a C# spreadsheet library that allows us to read, modify, and create spreadsheets without having to open Excel!

There are various templates for calendars, invoices, budgets, etc.

---