Open In App

Custom Excel Number Format

Last Updated: 04 Dec, 2022

S

sajid
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

MS Excel is a very important tool for businesses as well as for individuals with very useful features and tools. It is a market leader in spreadsheet editing and managing software. MS Excel contains many inbuilt number formats that can be used as per the needs of the user, however, sometimes users may need specific custom number formats that are not available in Excel by default.

Custom Excel Number Format

For example, if we want to round a decimal number to one digit after the decimal point, if we consider the number 44.64  we can use the custom number format ##.# to convert it to 44.6. If you want to represent number 347523450 using the Indian number system, of crores, lakhs, and thousands you can use the custom number format ##\,##\,##\,### to convert it to 34,75,23,450. First, let’s consider the list of the following numbers given below :

Dataset

 

To Convert these numbers into our required format we have to execute the following steps:

Step 1: Drag and select the numbers whose format has to be changed using the cursor.

Dataset

 

Step 2: a) Go to home and click on format.

Clicking-format-from-home

 

b) Then click on Format Cells.

Clicking-format-cells

 

Or

a) Right-Click and choose Format Cells.

Clicking-format-cells

 

Format Cells Dialog Box appears.

Editing-format-cells

 

Step 3: Choose a custom option from the given Dialog Box.

Choosing-custom-option

 

Step 4: Double-Click on the Type box and erase the given format and type your required format as shown and press ok to convert the given number into your required format.

Converting-number-into-format

 

Step 5: The numbers change to the required format.

Numbers-changed-into-format

 

This is the way in which numbers can be changed to any custom format. However, if you would want to remove the custom formatting from your numbers you can follow the given steps:

Step 1: Follow Step 1, Step 2, and Step 3 mentioned above in the article to go to Format Cells, Custom Format Tab as shown below:

Clicking-on-custom-format

 

Step 2: Erase the format present in the custom dialog box and keep it blank and click on ok.

Erasing-format

 

Step 3: The number format changes to the original.

Numbers-changed-to-original

 



Article Tags :

S

sajid
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

S

Saurabh Gupta

Custom Format to Show Units Without Changing Number to Text in Excel

article_img
Microsoft Excel is a great software that is used by individuals to store data in the form of spreadsheets in a formatted manner. Microsoft Excel allows us to store a variety of data such as characters, text, numbers, decimals, date, time, etc. Since Excel is a smart tool, it automatically identifies the data type of the records and places them accordingly inside the cell based on its default convention. But, there can be scenarios in which we want to store data which is actually of kind number but along with some unit which is generally text. Let's say we want to store unit values like 10 Litres (10 L), 20 Litres (20 L) or we want to store distance traveled like 2 km, 5 km. In such cases, when we input let's say 2 km or 20 L inside a cell then it will be treated as a text by Excel automatically. But, what if we want these records to be actually stored as numbers inside the cell, so that we can perform some functional operations on these numbers such as calculating the total amount of currency or calculating the total distance traveled. In such cases, mathematical formulas cannot be performed on text values. Hence, we are required to change those texts back to numbers. In this artic
Read More

A

Ankit kumar

How to Create a Custom Date Format in Excel?

article_img
Date formatting is very important when you prepare any set of data, then there are different types of formatting used for different categories, which are different from our normal formatting which is dd/mm/yyyy. When you enter any date in MS-Excel then excel considers it as the default format of date as your computer has, but you can change their format in numerous ways, and also you can make your own custom format of a date. Such as 14-Dec, December 14, 2023, 14/12/2023, and may more depending on individual choices, you can change it. Here we are going to show the different ways to format a Date. Let's understand it in a better way by taking an example, Step 1: Open MS Excel and save with any name. Here we are going to save it by "Date Format" name. Step 2: Take any date, like here we are taking 15/04/2001, which is in the format of mm-dd-yyyy and also it is the normal format of a date. Step 3: You can change the format by selecting that particular date cell, and press ctrl+1, then a new "format cells" window will open. From this format window, you can choose date formatting according to your needs. If you want that date to be on 15 April 2001 format, then choose dd mont
Read More

A

Amisha Kirti

How To Convert Date To Number String Or Text Format In Excel?

article_img
Excel's built-in date system by default supports dates between January 1, 1900, and December 31, 9999. In this range, the numbers start at 1 and increase by 1 each time. In addition to text and numeric data types, Excel also allows dates that are internally stored as serial numbers. In Excel, the date format is typically recognized by default. However, Excel also allows you to input date format by choosing the cell and pressing (Ctrl+;). Methods to Convert Date to Number StringDATEVALUE Function Syntax: =DATEVALUE (date_text) Where date_text is the only argument DATEVALUE FunctionCell FormattingStep 1: Once the cell with the value has been chosen, click the home tab and choose the NUMBER or TEXT option. Step 2: The cell format then changes into the original serial number internally stored by Excel. Note: The first serial number stored by excel is of date 01/01/1900. Methods to Convert Date to TextTEXT Function It is used to convert a date into a specified text format. Syntax: =TEXT(value, format_text) For today’s date: =TEXT(TODAY(), format_text) Where value: The value that needs to be converted. This could be a numeric cell reference or value, or a numeric value returned by
Read More

A

Asha Senthilkumar

Use Custom Data Validation Function To Limit Number of Digits In Excel

article_img
There might arise a situation while working on Excel where you want the users to type in 10-digits(say) numbers only in a cell. In these types of scenarios, we can make use of the Custom Data validation Function to limit the number of digits in Excel. In this article, we will discuss the same. Sample Data: Customer Sales Table Sales Table Approach: First, we check the given cell value is number and then the cell value is only 10 digit. Allow only both conditions are true Excel Formula: ISNUMBER(): Return Boolean value True if the cell value is number.LEN(): Return the number of characters in a cell.AND(): Logical and function.Implementation of Custom Data Validation Function: Follow the below steps to implement the custom data validation function: Step 1: Type Header (“Mobile Number” as an example) in Cell C1. Step 2: Select cells “C2” to “C21” Step 3: Click Data >> Data Validation >> Data Validation… to open the Data Validation dialog box as shown below. Here we will be implementing the function as : AND(LEN(C2)=10,ISNUMBER(C2))Error Alert: Data validation popup an Error Alert, when the condition is not met.
Read More

S

Saurabh Gupta

How to Format Numbers in Thousands and Millions in Excel?

article_img
Microsoft Excel allows its users to store raw data in a well-structured format in a spreadsheet. The spreadsheets consist of numerous cells in the form of rows and columns in which an individual can store data. An Excel spreadsheet can store different types of data such as numbers, characters, strings, dates, date-time, etc. A lot of the time when we store a large amount of data inside an Excel spreadsheet and when we want to look out for a particular entity within that spreadsheet it becomes quite cumbersome to get information about that in a single glance. In order to manage such situations and avoid such scenarios, a user must always think about keeping the records formatted in such a way that it looks easier to the human eyes and conveys much more information in a glance. Suppose, we have an Excel spreadsheet containing data about the net worth of different persons in a city or sales of a particular company as shown in the below image. Now, when we give these extremely large raw numbers to any individual, it would be really hard for that person to digest these numbers in one glance. To make it more convenient, we can write these numbers in a particular way like writing the num
Read More

S

Shivani Jain

How to Change Date Format in Excel

article_img
You know that Excel is one of the best spreadsheets you can work on. Excel is the first preference of many users to create an impressive data set. Have you tried filling in the Date on the sheet? Date with the data is a common practice done by users in Excel to get information timeline. To help organize data in an Excel worksheet, we will discuss four easy yet effective ways to change the date format in Excel. How to Change Date Format in Excel? What is the Date Format in Excel?In Excel, a date is displayed depending on the user's chosen format. You may choose from the different formats available in your Excel worksheet, or you can even create a customized date format per your requirements. The default date format is usually shown in your system's 'Control Panel.' However, you can even change these default settings if necessary. Change Date Format in ExcelWhen you input a date in Microsoft Excel, the program will format it according to the default date settings. For example, if you want to enter the date April 14, 2024, the date could appear in either long date format, or short date format, as 14-Apr, April 14, 2024, 14 April, or 04/14/2024, all depending on your settings. You may
Read More

R

Rishabh Chakraborty

How to Format Chart Axis to Percentage in Excel?

article_img
By default, in Excel, the data points in the axis are in the form of integers or numeric representations. Sometimes we need to express these numeric data points in terms of percentages. So, Excel provides us an option to format from number representation to percentage. In this article, we are going to discuss how to format a chart axis to percentage in Excel using an example. Example: Consider the dataset shown below : Plotting a chart The steps are : 1. Insert the dataset in the worksheet. 2. Select the entire dataset and then click on the Insert menu from the top of the Excel window. 3. Click on Insert Line Chart set and select the 2-D line chart. You can also use other charts accordingly. 4. The Line chart will now be displayed. We can observe that the values in the Y-axis are in numeric labels and our goal is to get them in percentage labels. In order to format the axis points from numeric data to percentage data the steps are : 1. Select the axis by left-clicking on it. 2. Right-click on the axis. 3. Select the Format Axis option. 4. The Format Axis dialog box appears. In this go to the Number tab and expand it. Change the Category to Percentage and on doing so the axis data p
Read More

A

Asha Senthilkumar

How to Convert Data from Wide to Long Format in Excel

article_img
Do you find your Excel data hard to analyze? Sometimes, changing the way your data is arranged can make it much easier to understand. Converting data from wide format to long format can help you organize your spreadsheet better, making it simpler to spot trends and patterns. This method is useful for business reports, school projects, and more. In this guide, we’ll show you, step-by-step, how to change your data from wide to long format in Excel. Let’s make your data work for you! Convert Data from Wide to Long Format in MS ExcelReshape data is an important step in Data analysis. Most of the time the raw data will be unstacked. The below example of the data presents each quarter in a column. Before doing further analysis, the user may need to know stack/pivot the data. In this article, we explain how to convert data from wide to long format in Excel. So we have a table that contains some data as shown in the image below: Sample DataNow we convert the data from wide to long format as shown in the below image:  So to this, we have to follow the following steps: Step 1: In Excel, press Alt+D+P to popup PivotTable and PivotChart WizardStep 2: Select “Multiple Consolidation ranges“ then
Read More

A

Asha Senthilkumar

Excel Format Painter

article_img
Imagine you spend a lot of time making a cool-looking chart in Excel, with just the right colors and fonts. But then you need to copy that look to other charts. No need to do it manually! Excel's Format Painter is like a copy-and-paste button for formatting. You just pick the pretty chart, click the painter, and then brush it onto the other charts. Table of Content What is Format Painter in Excel How to Use Format Painter in Excel How to Use Format Painter in Excel for Multiple RowsHow to Use Format Painter in Excel for Entire Column How to Use Format Painter for Multiple CellsShortcut for Format Painter in Excel How to Use Format Painter for Multiple SheetsHow to Use Format Painter in Excel on Mac FAQs on Excel VBAWhat is Format Painter in Excel The Format Painter is a command that allows you to copy formatting from one cell to another cell in the same worksheet( or other worksheets or workbooks). It is a nifty tool that saves you lots of time. How to Use Format Painter in Excel The Format painter can be used to copy to single cells or ranges. Format Painter can be used by clicking on its button in the Ribbon. The best practice in creating a dashboard/report is to maintain consist
Read More

C

Chetan

Format Reports in Excel

Excel 2013 introduces the Format pane. It offers advanced formatting choices in clean, bright new task windows, and it is also highly useful. When you alter the theme, it is applied to all Power View Views in the Report and Sheets in the Workbook. You may also modify the size of the text in all of your Report Elements. You can add Background Images, alter the Font or Font Size for One Visualization, change the Font or Font Size for the entire page, and Format numbers in a Table, Matrix, etc. Dataset This is the major dataset that will be used in this case. Table Formatting Step 1: Formatting a report, begin with a table. Choose the following options: Months, Percentage of sales, Quarter, and Sales in billion. In Power View, a table with these fields is displayed. Step 2: Select any number and then go to the Design tab on the top of the ribbon and then select a percentage from the dropdown list. Step 3: You can now see that the entire column containing the selected cell is transformed into a percentage. Step 4: You can also change the font, Theme, and size of the table in power view. For that just go to the Power View tab on the top of the ribbon and from the theme section, y
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox