Thursday, October 09, 2014

Types of Cell Formatting & Uses in Excel

Have you ever typed a date, like 10/9/14, into a cell in Excel and ended up with a number like 41921 once you pressed enter or tabbed to a new cell?  This happens automatically because the cell is not correctly formatted for the type of data being entered.  If you know how to set the correct cell formatting for the data type, the fix is simple and fast.  This post covers the most common cell formatting options, how they work, and how they are best used.  To modify the formatting type of a worksheet, row, column, or cell, select the area to modify, right-click and select "Format Cells", then select the "Number" tab.

General
General formatting is the default formatting applied to all cells in a new worksheet.  Any text entered remains the same without any formatting applied.  When a fraction is entered, it is turned into a date if the numbers could represent a date.  For example, if 4/5 was entered today, it would be changed to 4/5/2014.  On the other hand if 15/16 was entered, it would remain in fraction form because this does not represent an actual date.

Number
Number formatting is used to enter whole numbers and automatically adds 2 decimal numbers by default.  Fractions entered are calculated into decimals and dates into numbers*.  When selecting number formatting check the box to add the 1,000 separator if preferred and increase or decrease the decimal numbers.  Text entered into a number cell remains text without any formatting being applied.  Use number when entering numbers and or text not representing money.


Currency
Currency formatting is used to represent money and by default adds the $ and two decimals to any numbers entered.  Fractions are turned into currency decimals and dates are turned into numbers*.  Set the number of desired decimals, or modify the currency symbol using the formatting menu.  Also, set the formatting type for any negative numbers entered so they blend in or stand out.  Text entered remains text without any formatting being applied.  Using currency formatting is perfect for budgets or keeping track of expenses like home repair or vacations.

Accounting
Accounting formatting is similar to the currency tab as it applies the $ and two decimals to any number entered by default.  The difference between accounting and currency formatting is that accounting lines up the currency symbol and the decimal in a series of numbers making them easier to read for accounting purposes.  If a fraction is entered, it is turned into a date, but only if the numbers could represent a date.  All other fractions remain as fractions.  Text and dates entered remain the same without any formatting being applied.  Use accounting formatting when it is visually important to have a series of numbers line up so the tens, hundreds, thousands and more are easily distinguishable.

Date
Date formatting is used to enter sets of dates into cells without any calculations being applied to the numbers in the date.  There are several different date formats to choose from including month number or name, days or years, day of the week and more.  If a fraction is entered, it is turned into a date, but only if the numbers could represent a date.  All other fractions entered remain as fractions.  Text entered remains the same without any formatting being applied.  Use date formatting when a series of entries will be dates to be sure they are not formatted into other numbers.

Time
Time formatting is used to enter times and have them automatically formatted adding either am or pm by the time formatting chosen.  If a regular number is entered, without a colon for time, it is turned into a date.  If a fraction is entered, it is turned into a date and time, but only if the numbers could represent a date.  Text and all other fractions entered remain the same without formatting being applied.  NOTE:  While only the time appears in the cell by default, if you click on a cell with a time a date is attached to the time and can be seen in the function bar at the top of the worksheet.

Percentage
Percentage formatting is used to be able to enter any numbers and have the % symbol and two decimals automatically applied.  Fractions entered are calculated and represented as percentages.  Dates and text entered remain the same without any formatting being applied other than the % symbol next to dates.  Use percentage formatting when doing budgeting, creating charts, or cost analysis to see where the majority of income and expenses are without looking at actual figures.

Fraction
Fraction formatting is used to allow fractions to be entered without calculating them into decimals.  Entered fractions are automatically reduced to their lowest form.  For example, 16/32 would be reduced to 1/2.  Whole numbers, text, and dates entered remain the same without formatting being applied.  Use fraction formatting when you need to be able to enter fractions and retain their value rather than having decimals calculated.

Text
Text formatting is used to allow any type of data to be entered without any calculations or automatic formatting to be applied.  For instance, entering a four digit number beginning with a zero into most cell types would drop the zero and leave a three digit number.  Text formatting allows the beginning zero to remain visible.  Text formatting is convenient when there are many different types of data in a series, a fraction, whole number, text, date, percentage and more, as it preserves their original format.  Text is often helpful when creating a quick spreadsheet or spreadsheet draft.

*Dates turned into numbers are calculated as the number of days since 12/31/1899 so this value is often useless.

Regardless of how you use Excel, what types of spreadsheets you create, or the kind of data being tracked, Excel can be an extremely helpful and efficient tool.  There are many types of data formatting, as well as multiple options for most of the types of formatting.  The more often Excel is used, the easier it is to see how many different ways it can be used and how many ways it can organize data to save time, prevent calculation errors, and export data into presentable charts.  However Excel is used, starting with the right formatting for the types of data entered is key in having accurate data and consistent results.

No comments:

Post a Comment