A Perfect Guide for All You Need to Know About Data Formatting in Excel

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.

Post Graduate Program In Business Analytics

In partnership with Carlson School of ManagementVIEW COURSE
Post Graduate Program In Business Analytics

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.

Data_Formatting_In_Excel_1

  • In the Number Group, select the drop-down to access the Number formatting as shown below.

Data_Formatting_In_Excel_2

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

Data_Formatting_In_Excel_3

Select the "Format cells" option.

FREE Business Analytics With Excel Course

Start your Business Analytics Learning for FREEStart Learning
FREE Business Analytics With Excel Course

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.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's 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.

Data_Formatting_In_Excel_4

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.

Data_Formatting_In_Excel_5.

Cell Color Formatting in Excel

The Cell background color option will help users modify the cell background color in the spreadsheet, as shown below.

Data_Formatting_In_Excel_6.

Cell Borders Formatting in Excel

The spreadsheet borders option will allow users to add cell borders as per the requirement of the users.

Data_Formatting_In_Excel_7

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. 

Data_Formatting_In_Excel_8

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.

Data_Formatting_In_Excel_9.

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.

Data_Formatting_In_Excel_10.

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP in Business Analysis

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.

Data_Formatting_In_Excel_11

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.

Data_Formatting_In_Excel_12

With that discussed, you can wrap up this ‘Formatting in the Excel’ tutorial.

Get certification from IBM and Carlson School of Management with our Post Graduate Program in Business Analytics. Enroll TODAY!

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. 

About the Author

Ravikiran A SRavikiran A S

Ravikiran A S works with Simplilearn as a Research Analyst. He an enthusiastic geek always in the hunt to learn the latest technologies. He is proficient with Java Programming Language, Big Data, and powerful Big Data Frameworks like Apache Hadoop and Apache Spark.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors