Cell Formatting, Conditional Formatting and Excel Tables | Free Excel Course: From Beginner to Advanced

What you'll learn

Apply number formats without changing the underlying value.
Highlight what matters automatically with conditional formatting.
Turn your data into Excel tables that grow on their own.
Sort, filter, freeze panes and print without surprises.
Build an accounts-receivable sheet that flags only overdue invoices.

📥 Practice this lesson

The Sales sheet is already an Excel table named tblSales. Turn on its Total Row, set it to sum the Amount column (it should show $122,759.50) and add data bars to Amount.

practice-excel.xlsx · Sales sheet

A well-formatted sheet isn't just prettier: it reads faster, looks professional and reduces mistakes. In this lesson you'll format numbers and dates, highlight data automatically with conditional formatting and convert your lists into Excel tables, the feature that saves most people more time than any other.

Ctrl + 1opens the full Format Cells dialog (Mac: Cmd + 1)

5families of conditional formatting, from simple rules to icon sets

1well-built table feeds your totals, charts and PivotTables

Number formats
Conditional formatting
Formula-based rules
Excel tables
Sort, filter and print

Number formats: the value stays, only the look changes

First principle: formatting does not change the data. If a cell contains 0.1834 and you apply a Percentage format with no decimals, you'll see 18%, but Excel keeps calculating with 0.1834. That's why you should never "round" by changing the format when the result must be exact: use the ROUND function.

Apply formats from Home → Number or press Ctrl + 1 to open the full Format Cells dialog:

Format	When to use it	Looks like
Number	Quantities with a thousands separator	12,500.00
Currency	Prices on invoices or price lists	$12,500.00
Accounting	Financial statements: lines up the $ on the left and shows zeros as a dash	$ 12,500.00
Percentage	Rates, margins, growth	18%
Short / Long Date	Dates	3/15/2026 · Sunday, March 15, 2026
Special → Zip Code	U.S. ZIP codes that start with zero	02108
Text	Codes that must stay exactly as typed (account or part numbers)	00752

Useful custom formats

In Ctrl + 1 → Custom you can build your own formats. Three worth knowing:

#,##0 "units" → shows 1,250 units and is still a number you can add up. This solves lesson 1's problem without typing text into the cell.
dddd → shows the weekday of a date (Monday).
#,##0.0;[Red]-#,##0.0 → negative numbers appear in red.

1. A cell displays 12.5% and you use it in a formula. What value does Excel calculate with?

12.5
0.125
The text "12.5%"

Formatting only changes the appearance. A percentage is a fraction: 12.5% is 0.125.

Conditional formatting: let Excel highlight what matters

Conditional formatting changes how a cell looks based on its value. You'll find it under Home → Conditional Formatting. The most practical uses:

Highlight Cells Rules

Greater than, less than, between, equal to, text that contains, dates and Duplicate Values.

Use it to: catch duplicate invoice numbers or emails.

Top/Bottom Rules

Top 10 items, bottom 10%, above average.

Use it to: spot your best customers at a glance.

Data Bars

A small bar chart inside each cell.

Use it to: compare sales without building a chart.

Color Scales and Icon Sets

Green-to-red heat maps, arrows or traffic lights.

Use it to: KPI tables by month or by region.

Here's what data bars look like on sales by salesperson from the practice file's Sales sheet (the longest bar is the highest value):

Sales by salesperson (January to September)

Rachel$25,029

Jason$24,101

Kevin$21,174

Megan$20,817

Emily$18,401

Brandon$13,238

Rounded to the nearest dollar. The exact amounts add up to $122,759.50, the sheet total.

Formula-based rules: the professional level

Choose New Rule → Use a formula to determine which cells to format and you can highlight entire rows. For example, to shade every order whose status (column E) is "Pending":

Select the whole data range, for example A2:F200.
Create the rule with this formula: =$E2="Pending"
Pick a fill color and click OK.

Notice the mixed reference from the last lesson: $E locks the column so every cell in the row looks at the status, while the unlocked 2 lets each row evaluate itself. To flag past-due dates in column D, the rule would be =$D2<TODAY().

Worked example: overdue invoices at a distributor in Atlanta

A food-service distributor in Atlanta wants its accounts-receivable sheet to turn red only for invoices that are unpaid and past due. Paid invoices shouldn't light up even if their due date has passed. The data is in A2:E5: invoice, customer, amount, due date (D) and status (E). The rule, applied to A2:E5:

=AND($E2="Unpaid", $D2<TODAY())

The AND function (covered in depth next lesson) requires both conditions to be true. If today were April 15, 2026, you'd see this:

Invoice	Customer	Amount	Due	Status	Highlighted?
INV-1031	Peachtree Deli	$1,250.00	3/31/2026	Unpaid	Yes: overdue and unpaid
INV-1032	Midtown Bakery	$860.00	4/5/2026	Paid	No: already paid
INV-1033	Buckhead Grill	$2,400.00	4/10/2026	Unpaid	Yes: overdue and unpaid
INV-1034	Decatur Market	$540.00	4/30/2026	Unpaid	No: not due yet

The collections clerk sees at a glance that $3,650 is overdue ($1,250 + $2,400) and whom to call first. Best of all, the sheet updates itself tomorrow, because TODAY() changes every day. Nobody has to color cells by hand.

Don't overdo it

If everything is highlighted, nothing stands out. Use one or two colors with a clear meaning (red = problem, green = target met) and clean out old rules in Conditional Formatting → Manage Rules: hundreds of stacked rules slow a workbook down.

2. You want to highlight the entire row of every order whose status (column D) is "Pending." You apply the format to A2:F100. Which formula do you use?

=D$2="Pending"
=$D$2="Pending"
=$D2="Pending"

$D locks the column so every cell in the row checks the status, and the unlocked row number changes so each order is evaluated on its own row.

Excel tables: the most underrated feature

A table (not just "a range with borders") is a special Excel object that makes your list smart. Click anywhere in your data and go to Insert → Table or press Ctrl + T, confirm "My table has headers" is checked and click OK.

Instantly you get:

Filter and sort buttons on every header.
Banded rows that are easier to read.
Automatic expansion: type in the next row and it joins the table, and every formula, format and chart that depends on it updates.
Calculated columns: type a formula in one cell of a column and it fills the whole column.
A Total Row: on the Table Design tab, check "Total Row" and pick Sum, Average, Count… for each column. These totals respect your filters.
Sticky headers: as you scroll, the column names replace the letters A, B, C.

Structured references

Inside a table, formulas use names instead of coordinates. If your table is named Sales (rename it under Table Design → Table Name), you can write:

=[@Quantity]*[@Price]        ← inside the table: quantity times price for this row
=SUM(Sales[Amount])          ← from any sheet: adds the whole Amount column

The second formula has a huge advantage: when you add new sales, the total includes them without you touching anything. With a normal range (=SUM(D2:D500)), sale number 501 would be left out.

Recommendation

Turn any list that will grow into a table: sales, expenses, inventory, customers. Tables are the foundation for the PivotTables and charts you'll build later in the course.

3. What's the main benefit of converting a data range into an Excel table?

It grows when you add rows and copies formulas down automatically
It locks the data so no one can edit it
It converts numbers to text so they don't change

Tables expand with every new row, keep formats and formulas consistent, and make totals and PivotTables include new data.

Sort and filter the right way

Sort by several columns: Data → Sort and add levels, for example Region (A to Z) and then Amount (largest to smallest).
Text, number and date filters: from a header's arrow you can filter "contains," "greater than," "this month" and more. Toggle filters with Ctrl + Shift + L (Mac: Cmd + Shift + F).
Filter by color: handy combined with conditional formatting.

Never sort a single column by selecting only that column: you'd separate the data from its row. Inside an Excel table, that risk disappears.

Common formatting mistakes (and how to avoid them)

Hiding decimals on an invoice and ending up with totals that are "off" by a few cents.

If the value must be rounded, use =ROUND(value, 2).

Entering ZIP codes like 02108 as numbers and losing the leading zero.

Use Special → Zip Code or Text format before typing, or start with an apostrophe: '02108.

Coloring overdue invoices by hand every morning.

A rule with TODAY() does it automatically and never forgets.

Copying rows with conditional formatting until you have dozens of duplicate rules.

Check Manage Rules and keep one rule applied to the whole table.

4. Your expense table has its Total Row turned on. How do you add a new expense so it's included in the total?

Type it right below the Total Row
Click the last data cell (just above the totals) and press Tab
Copy the Total Row and change its values

With the Total Row on, anything you type below it stays outside the table. Pressing Tab in the last data cell creates a new row inside the table, and the total picks it up.

Freeze panes and print without surprises

With long lists, lock the header row with View → Freeze Panes → Freeze Top Row. Before printing or saving as PDF, check the Page Layout tab: landscape orientation for wide tables, "Fit All Columns on One Page" in the print settings and Print Titles to repeat the headers on every page. U.S. Excel defaults to Letter paper (8.5 × 11 in); if you send files abroad, switch to A4 under Size.

Practice exercise

Convert your expense list into a table named Expenses.
Apply the Currency format to the Amount column.
Add a Total Row that shows the sum of Amount.
With conditional formatting, highlight expenses above the average in orange.
Add a new expense and confirm it's added to the total automatically.

See solution

Click inside the data → Insert → Table → check "My table has headers." In Table Design → Table Name, type Expenses.
Select the Amount column → Ctrl + 1 → Currency.
Table Design → Total Row; in the Amount total cell, choose Sum.
Select the Amount column of the table → Conditional Formatting → Top/Bottom Rules → Above Average → orange fill. The rule is dynamic: when the average changes, so does the highlighting.
Because the Total Row is on, don't type below it: click the last data cell and press Tab. A new row appears inside the table; type the expense and watch the total change.

Summary

Formatting changes how a value looks, not the value itself. Ctrl + 1 opens every option.
Formula-based conditional formatting can highlight entire rows.
Excel tables grow on their own, copy formulas down and make your totals future-proof.
With the Total Row on, add new rows with Tab from the last data cell.
Rules with TODAY() keep alerts like overdue invoices up to date automatically.

Next lesson: Excel IF Function: Nested IF, IFS, AND, OR and IFERROR. You'll make Excel decide on its own with one or several conditions, using real examples like grades, discounts and bonuses.