Enter and Format Data: Numbers, Dates, Text and Tidy Tables

Many Excel problems that look mysterious are really data problems. A total that does not add up, a date that will not sort, a column that refuses to calculate: very often the cause is that Excel is reading a value as the wrong type. This lesson teaches you how Excel sees data, so you can enter it correctly and fix it when it goes wrong. Then you will format your sales table so a manager can read it at a glance.

Three kinds of values

• Numbers. Can be added, averaged and compared. By default they are aligned to the right of the cell.
• Text. Words and anything that is not a number, such as names and codes. Aligned to the left by default.
• Dates and times. Excel stores a date as a number: the count of days since the start of 1900. So 1 June 2024 is a number that Excel shows as a date. This is why you can subtract two dates to get the days between them. A time is a fraction of a day.

This left and right alignment is a quick test. If a number sits on the left of its cell, Excel is probably treating it as text, and it will not add up.

Numbers stored as text: the classic problem

Sometimes numbers arrive as text. For example, a column copied from a website or an accounting program, or a number typed with an apostrophe in front. Signs: the number is left-aligned, there is a small green triangle in the corner of the cell, and SUM ignores it. How to fix it:

1. Select the cells with the problem.
2. Click the yellow warning icon that appears and choose Convert to Number.
3. If there is no icon, try multiplying: type 1 in an empty cell, copy it, select the problem cells, choose Paste Special, Multiply, then delete the helper cell.
4. Another approach is Data, Text to Columns, Finish on the selected column, which re-reads each cell.

The opposite issue also happens. Codes like 007 or phone numbers with a leading zero lose the zero because Excel treats them as numbers. Format those cells as Text before typing, or type an apostrophe first: '007.

Dates

Dates are tricky because countries write them differently. 03/04/2024 is 3 April in India, the UK and most of Africa, but 4 March in the United States. Excel follows the regional setting of your computer. To avoid confusion in shared files, enter dates in a clear form such as 03-Apr-2024, or use the unambiguous 2024-04-03.

• If a date is left-aligned, Excel did not recognise it, and it is text. Retype it in a form your computer accepts, or use Text to Columns, Date to convert a whole column.
• To show the same date in a different style, change the format (below), not the value.
• Press Ctrl+; (Ctrl and semicolon) to enter today's date as a fixed value.

Number formats

A format changes how a value looks, not what it is. A cell holding 0.18 can show as 0.18, 18% or 18.00% without changing the number. Select cells, press Ctrl+1 to open Format Cells, and choose a category.

• Number with a set number of decimals and an optional thousands separator.
• Currency or Accounting with a symbol such as ₹, £, $ or any other. It only changes the look. Excel does not convert between currencies.
• Percentage. Typing 18% stores 0.18. Applying the percent format to 0.18 shows 18%.
• Date in many styles, and Time.
• Text to keep exactly what you type.

A useful rule: store the number, format the look. Do not type Rs. 1,200 into a cell. Type 1200 and apply a format. Then the cell can still be added.

Make a table easy to read

Good formatting is not decoration. It makes a table quick to read and shows that you care about details. Apply these to your sales table.

1. Select the headings in row 1 and make them bold with Ctrl+B. Give them a fill colour from the Home tab, and centre them.
2. Select the Price and Amount columns, press Ctrl+1 and choose Number with 0 decimals and a thousands separator. Use the style your region uses.
3. Select the Date column and choose a date format like 01-Jun.
4. Double-click the line between two column letters to auto-fit the width to the longest entry. Do this for every column.
5. Add thin borders around the table: Home, Borders, All Borders.
6. Freeze the heading row so it stays visible when you scroll: View, Freeze Panes, Freeze Top Row.

Things to avoid

• Merged cells in your data. Merging cells for a title is fine above the table, but not inside it, because sorting and filtering break. Use Center Across Selection (in Format Cells, Alignment) instead.
• Blank rows and columns inside a table. They split it into pieces.
• Several tables stacked on one sheet with no space between. Keep one table per sheet or leave an empty row and column between them.
• Too many colours and fonts. Two or three colours is enough.
• Hidden rows or columns you forgot. They can hide errors.

The AutoFill handle

In the bottom right corner of a selected cell is a small square called the fill handle. Drag it to copy a value or continue a pattern. If you type Monday and drag, you get Tuesday, Wednesday and so on. If you type 1 and 2 in two cells, select both and drag, you get 3, 4, 5. Double-click the handle to fill down as far as the data in the next column goes. This is very handy for formulas.

Your task

1. Format your sales table as described: bold coloured headings, auto-fit columns, number format on Price and Qty, borders and a frozen top row.
2. Type the text 007 in a new cell and see what happens. Then format a cell as Text and type it again.
3. Type 3/4/2024 in a cell. Check if it reads as 3 April or 4 March. Change the format to show the month name.
4. Use the fill handle to produce the days Monday to Sunday.

How to check it worked

• Headings are bold and the top row stays visible when you scroll.
• You can tell which cells hold numbers and which hold text by looking at their alignment.
• You can explain why a date should be entered in an unambiguous form.