Several sheets, named ranges and cleaning messy data
Combine monthly sheets with 3D references and lookups, name your ranges, clean messy lists and find and remove duplicates safely.
In this lesson you will
- Refer to other sheets
- Combine sheets with 3D references
- Use named ranges
- Clean and de-duplicate lists safely
About 16 minutes
When your data lives in many places
Sunil keeps one sheet for each month, January, February and March, and wants a first-quarter summary. He also has a customer list where ‘North’, ‘north ‘ and ‘ NORTH’ count as three different regions. Real work is rarely one tidy sheet. This lesson covers combining data from several sheets, naming ranges so formulas read clearly, and cleaning and de-duplicating a messy list.
Referring to another sheet
Write the sheet name, an exclamation mark and the cell: =Jan!B2. If the sheet name has spaces, put it in quotes: =’Sales Jan’!B2. Easiest of all: type =, click the other sheet’s tab, click the cell and press Enter. Excel writes the reference for you.
Three ways to combine months
A test workbook had three sheets, Jan, Feb and Mar, each with North, South and East revenue in B2:B4, and a Summary sheet. Three different formulas were really calculated:
| Formula | Result |
|---|---|
| = |
730 |
| = |
570 |
| SUMIF on each sheet (East) | 700 |
| =SUM(B2:B4) (grand total) | 2000 |
The 3D reference Jan:Mar!B3 means ‘cell B3 on every sheet from Jan to Mar’. It is compact, but every sheet between the first and last tab is included, and the cell must be in the same place on each sheet. The SUMIF route is safer when the layout of each month differs, because it looks up the region by name instead of by position.
Named ranges
A name is a label for a cell or range. Select it, type a name in the Name Box (no spaces, such as TaxRate or SalesData) and press Enter. Now formulas read like sentences: =
Cleaning a messy list
Seven region entries were typed in a test sheet: ‘ north ‘, ‘North’, ‘SOUTH’, ‘south ‘, ‘East’, ‘east’ and ‘North’. After =
The cleaning routine
- Copy the raw data to a new sheet and never touch the original.
- Add helper columns with TRIM, PROPER or SUBSTITUTE to standardise text.
- Convert text numbers and text dates to real ones.
- Flag duplicates with COUNTIF and review them.
- Paste values over the helper columns, delete the originals and then use Data, Remove Duplicates if needed.
- Re-count: the cleaned list should match what you expect.
Useful clean-up tools
- Remove Duplicates deletes rows that match on the columns you choose. Keep a copy first.
- Go To Special, Blanks selects empty cells so you can fill or delete them at once.
- Find and Replace with Options to match the entire cell.
- Paste Special, Values turns formulas into plain results.
- Data, Text to Columns converts text numbers in one step.
Consolidating
Data, Consolidate adds up ranges from several sheets or files by label without writing formulas. It is quick, but it does not update unless you tick Create links. For regular monthly reports, a single list with a Month column and a PivotTable is usually better than one sheet per month, because new months are simply new rows.
Practice
Make three monthly sheets with the same layout and a Summary that adds them with a normal formula and with a 3D reference. Then type a list of ten names or regions with extra spaces and mixed capitals, clean it with formulas, flag repeats and count the unique entries. Compare your count with a PivotTable of the cleaned list.
The unique count from SUMPRODUCT should match the number of rows in a PivotTable of the cleaned column. If it does not, a space or hidden character is still in the data; compare LEN of two entries that look the same.
Cleaning the only copy. If you delete the wrong rows with Remove Duplicates and have saved, the data is gone. Work on a copy and keep the raw sheet.
Linking files and keeping them healthy
A formula can also read from another workbook, and Excel writes the file path into it. This is useful but fragile: if someone moves or renames the source file, the links break and Excel asks whether to update them. Keep linked files together in one folder, avoid renaming, and use Data, Edit Links to see and repair connections. For anything shared, copy the figures you need into the workbook as values, or use one workbook with several sheets instead of many linked files.
A tidy workbook layout
- One sheet for raw data, never edited by hand.
- One sheet for settings such as rates, targets and lists, with named cells.
- One or more sheets for calculations and reports.
- A first sheet called Notes that explains what each sheet is and when it was last updated.
- Consistent sheet names without spaces, which keep references short.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
How do you refer to cell B2 on a sheet named Jan?
- Jan.B2
- B2(Jan)
- Jan:B2
- Jan!B2
Sheet name, exclamation mark, cell.
What does =SUM(Jan:Mar!B3) add?
- Only B3 on Jan
- B3 on every sheet from Jan to Mar
- A column
- Sheet names
It is a 3D reference.
Why use names such as TaxRate?
- They hide cells
- They protect the sheet
- Formulas become easier to read
- They change colours
Names also behave like absolute references.
What does =COUNTIF($B$2:B2,B2)>1 flag?
- Repeats after the first occurrence
- All values
- Blank cells
- Errors
The range expands as you copy it down.
What should you do before using Remove Duplicates?
- Delete the headers
- Merge cells
- Work on a copy
- Close Excel
Deletions are hard to undo after saving.
Summary
- Refer to another sheet with SheetName!
Cell. - 3D references add the same cell across sheets; lookups work when layouts differ.
- Names make formulas readable.
- Clean on a copy: TRIM, PROPER, flag duplicates, then remove.
Questions
Should I use one sheet per month or one list?
One list with a Month column is usually better, because a PivotTable then handles new months by itself.
Why does my 3D reference give the wrong answer?
Check the tab order, because every sheet between the first and last tab is included, and make sure the cell is in the same place on each.
Can I protect my raw data?
Yes. Keep it on its own sheet and use Review, Protect Sheet, or save it as a separate file.
