Data Cleaning and Data Formatting in Excel has always been the toughest task for a data analyst. Most business decisions are based on reports the analysis team generates and shares. One mistake around the data format is a perfect recipe for disaster.
This tutorial on Formatting in Excel by Simplilearn is your one-stop solution to learn the fundamentals of data formatting in excel. By the end of this tutorial, you will be able to recognize and rectify all the common mistakes you make while preparing your reports.
What Is Formatting in Excel?
Formatting in Excel is a simple process of modifying or manipulating data in spreadsheets from one form to another. Formatting can be done based on appearance and data types.
Now that you know the fundamentals of Data Formatting in Excel, you will learn how to implement formatting in real-time.
How to Format Data in Excel?
As discussed before, formatting in excel can be done based on various requirements, including appearance, datatype, and data organization. You can do Data Formatting in Excel by navigating the home tab.
Moving ahead, you will now learn how to implement formatting in Excel based on the number or datatype formatting in the next section.
Number Formatting in Excel
Number Formats might sound simple, but improper number formats may lead to improper reports in the end. A simple scenario can explain the situation in a better way.
Now, imagine you work with an automotive store, and your store manager requests you to find the customer that made the highest purchase in the month to award the customer a discount on his next purchase.
Let us assume all transactions are saved in dollar format except the one. In a hurry, you missed adding dollar format to it, which is the highest transaction made in the month. This simple scenario explains how easily things could go sideways.
In this section, you will learn what is number Formatting in Excel and how to add number formatting in Excel?
Number Formats can be done in a few simple steps as follows:
- Select a cell or a cell range or the complete column where you wish to add Number Formats
- Navigate to the Home button.
- In the Number Group, select the drop-down to access the Number formatting as shown below.
There is another more straightforward way.
- Select a cell or a range of cells or an entire column
- Right-click on any selected cell
- A drop-down will appear as shown below
Select the "Format cells" option.
Now that you have understood how to navigate the number formats option, it’s time to dig a little deep and understand the variety of formats available in Excel.
The following are the type of number formats available in Excel.
General
General is a number format selected as the default by excel for any number you type into the spreadsheet. The number appears the same way you type it without additional decimals or modifications.
Number
Number Format is exclusively used when you are working with numbers. The Number Format will add decimal points to your data to keep it more accurate, and you can also customize the number of decimal points you wish to have.
Currency
Currency Number Format is used when you want to represent numbers in the form of currency. For example, annual investment data. You might use dollars to represent such kinds of data.
Accounting
Accounting is completely similar to currency. You can use the accounting Number Format to add decimal places to your currency to make it more accurate.
Date
The Date Format is employed in spreadsheets to consider your input as a calendar date instead of a regular number.
Time
The Time Format converts the general default number format to the time format.
Percentage
The Percentage Data Format represents the values in terms of percentages. For example, the Excel spreadsheet calculates the percentage values and multiples by 100 and denotes the decimal values in a percentage format.
Fraction
The Fraction Format displays the general number in the form of a fraction according to the type of fraction you chose.
Scientific
The Scientific Number Format differs slightly from currency or a general or decimal number. The Scientific Number Format has an exponential number that indicates the power of a number. Apart from exponents, the scientific number format also includes trigonometry, calculus, roots of a number, etc.
Text
The text includes the textual type of data. Here, Excel treats any number of data you type as text format and keeps it the way it is without adding any decimals or mathematical symbols.
Special
You also have symbols besides numbers, text, and scientific notations. These symbols are entitled as special symbols. While using these symbols, you need to select the Format of the special symbol.
Custom
Apart from all the available options, Excel also provides its users with a customizable option that will help them use existing formats and customize them to create a completely new one.
So far, you have learned about number Formatting in Excel. Apart from number formatting, you can also carry out formatting at the cell level. You will now learn Cell Formatting in Excel in the next segment of this tutorial.
Start your Dream Career with the Best Resources!
Caltech Post Graduate Program in Data ScienceExplore Program
Cell Formatting in Excel
Cell Formatting in Excel is a little different from number formatting in Excel. Cell formatting is related to text alignment, color, font, cell background color, images, etc.
First, let us check the font options in Cell Formatting in Excel.
- Navigate to the Home tab and search for the Font group.
- Click on the drop-down icon, and Excel will provide you with a wide variety of font options, as shown below.
After the Font option, you can go through the font color, bold, italic, and underline options along with the text alignment in the same Home tab. A little towards the right side in the alignment group, you have text alignment, text wrapping, and text indentation options, as shown below.
Text Color Formatting in Excel
The Text color option will help users customize the text color in Excel with various color options, as shown below.
Cell Color Formatting in Excel
The Cell background color option will help users modify the cell background color in the spreadsheet, as shown below.
Cell Borders Formatting in Excel
The spreadsheet borders option will allow users to add cell borders as per the requirement of the users.
Clipboard and Text Paste in Excel
You have the clipboard group on the left side of the font tab. The clipboard group will help you with a variety of paste options. The copied text on the clipboard can be pasted, or you can also use a custom paste option.
Conditional Formatting in Excel
On the right side of the Number Format Group, you have Conditional Formatting, Cell Formatting, and Table Formatting options, as shown below.
The Conditional formatting option will allow the users to highlight or mark cells on excel spreadsheets based on logical functions and formulae, as shown below.
Table Formatting in Excel
The Table Formatting in excel will allow you to modify the tabular data in Excel with various options, as shown in the image below.
Cell Formatting in Excel
The Cell Formatting option in Excel will allow users to modify individual cell background colors per the user requirements, as shown below.
With that discussed, you can wrap up this ‘Formatting in the Excel’ tutorial.
Next Steps
‘How to Calculate in Excel’ can be your next stop for your data analytics with the Excel learning journey. Excel data sometimes includes dates and you might have to calculate the age and time difference of a business-oriented question. At times Age in excel or Datedif function in Excel will come in handy.
Are you interested to get certified in Business Analytics? Then do visit our Post-Graduate Program in Business Analysis from Simplilearn. It is on top among the five popular and top-ranking business analytics training and certifications in the industry. This Simplilearn program is a result-oriented training and certification program that helps you master statistics and data analytics concepts.
Should you have questions about this "A Perfect Guide for All You Need to Know About Data Formatting in Excel" tutorial, please feel free to write to us in the comments below. Our expert team will resolve them and will be happy to answer them earliest.
