Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
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.
Formatting Data in Excel (Table of Contents)
Formatting in Excel (2016, 2013 & 2010 and Others)
The ultimate goal of any worksheet is to present information in a way so that others are
- Able to understand the data quickly: For those of us who are not numbers people to start with, pouring over a black-and-white grid stuffed with an ocean of numerals is torture. We can immediately tell other persons what we are looking at. We can tell them from where they need to start on the page, as being a general rule, the largest items on the page will get the first look. Then, the eye will look for the next largest and the next largest after that. The actual content, the data in the cells, is likely to be what readers see last.
- Attractive for viewers: They do not get bored if they get something attractive.
After making the report, we need to make the sheet attractive by following guidelines:
- A header is a phrase which describes the entire content shortly.
- The header should be bold. (Shortcut key is Ctrl+B)
- The font size of the header should be larger than the content.
- The background color of the header cell should be other than white color. An energizing color like green, orange, purple is attractive for column heads.
- Outline border should be applied for the header.
- Alignment for the header should be center.
- Shapes can also be used for the headings of the table. (Insert tab)
- Width for all columns should be enough to show the data written in any of the cells in the column. (Shortcut keys in excel is ALT+H+O+I)
- There are different formats available for all type of data be it number, currency, percentage, date, fraction etc. The appropriate format for the cell should be chosen.
- Alignment for alphanumeric values should be left and for the number, that should be right.
- Normal borders should be applied for the data in the table. There are predefined table formats also available in Home tab.
- Where column width is not sufficient to place the text in a single line and display the entire data then we could use the ‘Wrap Text‘ command placed on the ‘Home’ tab and ‘Alignment’
- If we have extra information, then to explain the note, we should use the second color with the outline border.
- We can also use ‘Conditional Formatting‘ where we want a different type of data formatting in excel based on conditions.
- Various themes are available in ‘Themes’ group placed on ‘Page Layout’ We can choose one of the themes for presentation.
How to Format Data in Excel?
Let 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:
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 column head bold,
- Font size larger,
- Adjust the column width by using shortcut key (Alt+H+O+I) after selecting the entire table (using Ctrl+A),
- Center aligning the data,
- Apply outline border by using (Alt+H+B+T),
- Apply background color by using ‘Fill Color’ command available in ‘Font’ group on ‘Home’
We will be applying the same format for the last ‘Total’ row of the table by using ‘Format Painter’ command available in ‘Clipboard’ group on ‘Home’ tab.
As amount collected is a currency, we should format the same as currency using the command available in ‘Number’ group placed on ‘Home’ tab.
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’.
We can also apply the outline border for the table.
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 ‘Illustration’ group in ‘Insert’ tab.
Create the shape according to the requirement and with the same color as heads of the column and add the text on shape by right-clicking on the shapes and choosing ‘Edit Text’
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 formatting on text using the commands available in ‘Font’ group placed on ‘Home’ tab.
We can also use ‘Conditional Formatting’ for taking the attention of the viewers for ‘Top 3’ salesperson and ‘Bottom 3’ salesperson.
So format the cells that rank in the top 3 with Green Fill with Dark Green Text
Also, format the cells that rank in the Bottom 3 with Light Red with Dark Red Text
We can apply other conditional formatting option also, which is ‘Data Bars’.
We can also create the chart to display the data, which is also the part of ‘Data Formatting Excel’.
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 Formatting in Excel
- While data formatting in excel make 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.
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 –