Intermediate Excel Tutorial: Level Up Beyond the Basics

Once you know how to enter data, use basic formulas, and format cells, it is time to learn the intermediate Excel skills that separate casual users from productive professionals. This tutorial covers conditional formatting, data validation, named ranges, intermediate formulas, and productivity shortcuts. If you need a refresher on fundamentals, see our Excel tutorial for beginners first.

Key Takeaways

• Conditional formatting highlights cells automatically based on rules — making patterns, outliers, and trends visible at a glance without manual formatting.
• Data validation restricts what values can be entered in a cell — dropdown lists, number ranges, and custom rules prevent data entry errors.
• Named ranges replace cell references like A1:A50 with descriptive names like SalesData — making formulas easier to read, write, and maintain.

How Do I Use Conditional Formatting in Excel?

Select a range, go to Home > Conditional Formatting, and choose a rule type — Excel automatically highlights cells that meet your criteria with colors, icons, or data bars.

Built-In Rules

1. Select the range you want to format.
2. Go to Home > Conditional Formatting.
3. Choose a rule category:

Rule Type	Example	Use Case
Highlight Cell Rules	Greater Than, Less Than, Between, Equal To	Flag values above/below thresholds
Top/Bottom Rules	Top 10%, Bottom 10 Items	Identify best/worst performers
Data Bars	Colored bars proportional to value	Visual comparison within a column
Color Scales	Green-Yellow-Red gradient	Heatmap visualization
Icon Sets	Arrows, flags, traffic lights	Status indicators

Custom Formula Rule

For complex conditions, use a formula:

1. Select the range > Conditional Formatting > New Rule.
2. Select Use a formula to determine which cells to format.
3. Enter a formula that returns TRUE/FALSE (e.g., =AND(B2>100, C2="North")).
4. Click Format to set the highlight color.
5. Click OK.

The formula is evaluated relative to the first cell in the selection — use mixed references ($B2 locks the column, B$2 locks the row) to control how the formula adjusts across cells.

How Do I Set Up Data Validation in Excel?

Select the cells where you want to restrict input, go to Data > Data Validation, and define the allowed values — dropdown lists, number ranges, dates, or custom formulas.

Create a Dropdown List

1. Select the cells where you want the dropdown.
2. Go to Data > Data Validation.
3. Under Allow, select List.
4. In the Source field, either:
5. Type values separated by commas: North,South,East,West
6. Reference a range: =$F$1:$F$4
7. Click OK.

Common Validation Rules

Allow Type	Settings	Example
Whole number	Between 1 and 100	Age field
Decimal	Greater than 0	Price field
List	Source: range or comma-separated	Department dropdown
Date	Between start and end date	Project timeline
Text length	Less than or equal to 50	Short description field
Custom	Formula: =ISNUMBER(A1)	Enforce data type

Input Message and Error Alert

In the Data Validation dialog: – Input Message tab: displays a tooltip when the cell is selected (e.g., “Enter a value between 1 and 100”) – Error Alert tab: displays an error when invalid data is entered (Stop, Warning, or Information style)

Data validation protects your spreadsheets from incorrect entries — essential for shared workbooks and dashboards.

How Do I Create and Use Named Ranges?

Select a range, click in the Name Box (left of the formula bar), type a descriptive name, and press Enter — use the name in formulas instead of cell references.

Create a Named Range

Method 1: Name Box 1. Select the range (e.g., A2:A50). 2. Click the Name Box (shows cell reference like “A2”). 3. Type a name (e.g., SalesData). 4. Press Enter.

Method 2: Name Manager 1. Go to Formulas > Name Manager > New. 2. Enter a Name, optional Comment, and the Refers to range. 3. Click OK.

Use Named Ranges in Formulas

Without Named Range	With Named Range
=SUM(A2:A50)	=SUM(SalesData)
=VLOOKUP(B2, D2:F100, 3, FALSE)	=VLOOKUP(B2, ProductTable, 3, FALSE)
=AVERAGE(Sheet2!C2:C500)	=AVERAGE(MonthlySales)

Named ranges make formulas self-documenting — anyone reading =SUM(Q1Revenue) understands it immediately, while =SUM(Sheet3!B2:B92) requires investigation. For more on lookup formulas with named ranges, see our Excel lookup table tutorial.

What Intermediate Formulas Should I Learn?

Master these 10 formulas to handle most intermediate Excel tasks — conditional calculations, text manipulation, date functions, and error handling.

Conditional Formulas

Formula	Syntax	Use Case
IF	=IF(condition, true, false)	Return different values based on a condition
IFS	=IFS(cond1, val1, cond2, val2, ...)	Multiple conditions without nesting
COUNTIF	=COUNTIF(range, criteria)	Count cells matching criteria
SUMIF	=SUMIF(range, criteria, sum_range)	Sum values where criteria match
AVERAGEIF	=AVERAGEIF(range, criteria, avg_range)	Average values where criteria match

Lookup Formulas

Formula	Syntax	Use Case
XLOOKUP	=XLOOKUP(value, lookup, return)	Modern lookup (M365/2021+)
INDEX/MATCH	=INDEX(return, MATCH(value, lookup, 0))	Flexible lookup (all versions)

Text and Date Formulas

Formula	Example	Result
TEXT	=TEXT(A1, "MM/DD/YYYY")	Formats date as text
CONCAT	=CONCAT(A1, " ", B1)	Joins text strings
IFERROR	=IFERROR(A1/B1, "N/A")	Returns “N/A” instead of error

For VLOOKUP specifically, see our Excel VLOOKUP tutorial. For pivot tables, see our pivot table tutorial.

What Keyboard Shortcuts Should Intermediate Users Know?

These 15 shortcuts save hours per week by eliminating repetitive mouse clicks for common Excel operations.

Shortcut	Action
Ctrl+T	Convert range to Table
Ctrl+Shift+L	Toggle filters on/off
Alt+=	AutoSum selected range
Ctrl+Shift+;	Insert current time
Ctrl+;	Insert current date
Ctrl+D	Fill down (copy cell above)
Ctrl+R	Fill right (copy cell to left)
Ctrl+`	Toggle formula view
F4	Toggle absolute/relative reference ($)
Ctrl+Shift+~	Apply General number format
Ctrl+Shift+$	Apply Currency format
Ctrl+Shift+%	Apply Percentage format
Alt+Enter	New line within a cell
Ctrl+Shift+Enter	Enter array formula (legacy)
Ctrl+Space	Select entire column

The most impactful shortcut to learn is F4 — pressing it while editing a formula cycles through absolute reference modes (A1 → $A$1 → A$1 → $A1), saving significant time when building formulas.

How Do I Use Excel Tables Effectively?

Convert any data range to an Excel Table (Ctrl+T) for automatic formatting, structured references, auto-expanding ranges, and built-in filtering — Tables are the foundation of professional Excel work.

Convert to a Table

1. Click anywhere in your data range.
2. Press Ctrl+T.
3. Confirm the range and check My table has headers.
4. Click OK.

Table Benefits

Feature	Regular Range	Excel Table
Auto-expand	No — formulas break	Yes — new rows included automatically
Structured references	=SUM(A2:A100)	=SUM(Table1[Revenue])
Auto-filter	Manual (Ctrl+Shift+L)	Built-in on every column
Formatting	Manual	Auto-styled with banded rows
Total row	Manual formula	Toggle on/off, choose function
Pivot table source	May need updating	Auto-updates

Total Row

1. Click anywhere in the table.
2. Go to Table Design > check Total Row.
3. Click any cell in the total row to choose a function (Sum, Average, Count, etc.).

Tables are the single most underused intermediate feature. Converting your data to Tables before building dashboards, pivot tables, or lookup formulas prevents the most common Excel frustration: formulas that miss new data.

For building dashboards with Tables and pivot tables, see our Excel dashboard tutorial. If you need Excel with all intermediate and advanced features, Microsoft Office 2024 Professional Plus ($199.99) includes the full Excel desktop application.

Frequently Asked Questions

---