Chapter 9: Advanced Data Management

The Manage and format data domain is the largest part of MO-211, about a third of the exam. It covers tools for filling, formatting, checking and organizing data that go beyond the everyday ones. You'll fill cells by example, design your own number formats, stop bad data from being entered, collapse data into outlines with subtotals, remove duplicates, and write conditional formatting rules of your own instead of choosing from the built-in list.

9.1 Flash Fill and advanced fill series

9.1.1 Flash Fill

Flash Fill fills a column by following an example you type. It's the quickest way to split, combine or reformat text without writing a formula. Suppose column A holds names like Torres, Maya:

1. In B2, type the result you want for the first row, such as Maya.
2. Choose Data › Flash Fill (or Home › Fill › Flash Fill), or press Ctrl+E.

Excel fills the rest of the column by following the same pattern. If it guesses wrong, type a second example in B3 and run Flash Fill again.

Flash Fill can:

• split text, such as first names out of full names or area codes out of phone numbers
• combine columns, such as Maya Torres from a First column and a Last column
• reformat text, such as turning 5551234567 into (555) 123-4567 or mtorres into MTorres

9.1.2 The Series dialog box

The fill handle continues simple series. The Series dialog box gives exact control. Select the starting cell (or the range to fill), then choose Home › Fill › Series:

Setting	Choices
Series in	Rows (fill across) or Columns (fill down).
Type	Linear adds the step value each time (10, 20, 30). Growth multiplies by it (2, 4, 8). Date steps by a date unit. AutoFill works like the fill handle.
Date unit	For dates: Day, Weekday (skips Saturdays and Sundays), Month or Year.
Step value	The amount to add or multiply by each time. Negative values count down.
Stop value	The value to stop at. With a stop value, you only need to select the first cell.
Trend	Fits a straight line (Linear) or curve (Growth) to the values you selected and extends it, instead of using a step.

For example, to list the first day of every month in 2028 down column A: type 1/1/2028 in A2, select A2, choose Fill › Series, then choose Columns, Date, Month, step 1 and stop 12/1/2028.

You can also right-drag the fill handle. When you release the mouse button, a menu offers Fill Weekdays, Fill Months, Linear Trend, Growth Trend and Series.

9.2 RANDARRAY

RANDARRAY([rows], [columns], [min], [max], [whole_number]) returns an array of random numbers that spills like SEQUENCE.

Argument	Meaning	Default
rows, columns	How many rows and columns of numbers	1 and 1
min, max	The smallest and largest possible values	0 and 1
whole_number	TRUE for whole numbers, FALSE for decimals	FALSE

Formula	Result
=RANDARRAY(5)	Five decimals between 0 and 1 down a column
=RANDARRAY(10, 1, 1, 100, TRUE)	Ten whole numbers from 1 to 100
=RANDARRAY(4, 3, 50, 75)	A 4-by-3 block of decimals between 50 and 75

Like RANDBETWEEN, RANDARRAY is volatile and returns new numbers whenever the worksheet recalculates. To keep them, copy the spilled range and paste it back as values.

9.3 Custom number formats

When none of the built-in formats fits, write your own. Open Format Cells (Ctrl+1), choose the Number tab, choose Custom, and type a format code in the Type box. The sample above it shows the result. A custom format, like any number format, changes only how the value looks, not the value itself.

9.3.1 Digit placeholders and symbols

Code	Meaning	1234.5 in … shows
0	A digit, showing zeros	00000.00 → 01234.50
#	A digit, without extra zeros	#.## → 1234.5
?	A digit, with spaces in place of extra zeros (lines up decimals)	#.?? → 1234.5 (with a space)
, between digits	Thousands separator	#,##0 → 1,235
, at the end	Divides by 1,000 for display	#,##0.0,"K" → 1.2K
"text"	Shows the text as typed	0" units" → 1235 units
@	Stands for text typed in the cell	"Dept: "@ shows Dept: Sales for Sales
_ followed by a character	Adds a space the width of that character	#,##0_) lines up with numbers in parentheses
* followed by a character	Repeats the character to fill the cell	$* #,##0 puts the $ at the left edge

9.3.2 Sections

A format can have up to four sections separated by semicolons:

positive ; negative ; zero ; text

For example, #,##0;(#,##0);"–";@ shows positive numbers normally, negative numbers in parentheses, zero as a dash, and text as typed. If you give only two sections, the first applies to positive numbers and zero.

9.3.3 Colors and conditions

Put a color name in square brackets at the start of a section: [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White] or [Yellow].

[Blue]#,##0;[Red](#,##0);[Black]0

A condition in square brackets replaces the usual positive/negative rule:

[Red][<60]0" - Fail";[Green][>=90]0" - Excellent";0

This shows scores under 60 in red with - Fail, scores of 90 or more in green with - Excellent, and other scores plainly.

9.3.4 Dates and times

Code	Shows	Code	Shows
d	7	m	3
dd	07	mm	03
ddd	Sun	mmm	Mar
dddd	Sunday	mmmm	March
yy	27	yyyy	2027
h / hh	Hours	mm after h	Minutes
ss	Seconds	AM/PM	12-hour clock
[h]	Elapsed hours beyond 24	[mm]	Elapsed minutes

So dddd, mmmm d, yyyy shows Sunday, March 7, 2027, and [h]:mm shows a total of 30 hours as 30:00 instead of 6:00.

9.4 Data validation

Data validation controls what people can type in a cell. It can limit entries to numbers in a range, dates in a period, items from a list, or anything a formula approves, and it shows a message or an error when someone tries something else.

Select the cells and choose Data › Data Validation. The dialog box has three tabs.

9.4.1 Settings

The Allow list sets the kind of entry:

Allow	Use it to	Example
Any value	Remove a restriction (the default)
Whole number	Allow whole numbers in a range	between 1 and 10
Decimal	Allow any number in a range	greater than or equal to 0
List	Allow only items from a list, with a drop-down arrow	Pending,Approved,Denied or =$H$2:$H$5
Date	Allow dates in a range	between 1/1/2027 and 12/31/2027
Time	Allow times in a range	between 8:00 AM and 6:00 PM
Text length	Limit the number of characters	equal to 5
Custom	Allow entries that make a formula TRUE	=COUNTIF($A:$A,A2)=1 (no duplicates)

For most types, the Data list chooses the comparison: between, not between, equal to, greater than and so on. Then fill in the values, which can be numbers, cell references or formulas.

For a List, type the items in Source separated by commas, or select a range. Check In-cell dropdown to show the arrow. Ignore blank allows the cell to be left empty.

9.4.2 Input Message

The Input Message tab shows a note whenever someone selects the cell, such as "Enter the number of nights (1–14)." Type a Title and an Input message.

9.4.3 Error Alert

The Error Alert tab sets what happens when someone types something not allowed. Choose a Style:

• Stop blocks the entry. The person must retry or cancel.
• Warning asks "Continue?" The person can choose Yes to keep the entry.
• Information shows a message, and the entry is kept when the person clicks OK.

Type a Title and an Error message to replace Excel's generic one.

9.4.4 Finding and removing validation

• Data › Data Validation › Circle Invalid Data circles cells that break their rules, such as values entered before the rule was added. Clear Validation Circles removes the circles.
• Clear All in the Data Validation dialog box removes the rules from the selected cells.
• Go To Special › Data validation selects every cell with a rule.

9.5 Grouping, subtotals and removing duplicates

9.5.1 Grouping and outlines

Grouping rows or columns lets you collapse them into one summary line and expand them again. Excel adds an outline at the left (for rows) or above (for columns), with:

• level buttons (1, 2, 3…) that show only that level of detail
• minus and plus buttons that collapse or expand one group

To group rows, select the detail rows (not the summary row) and choose Data › Group › Group, or press Shift+Alt+Right Arrow. To ungroup, select them and choose Data › Ungroup, or press Shift+Alt+Left Arrow.

Other outline commands:

• Group › Auto Outline creates groups automatically from summary formulas, such as SUM rows below detail rows.
• Ungroup › Clear Outline removes the whole outline without changing the data.
• Show Detail and Hide Detail expand or collapse the selected group.
• The launcher in the Outline group opens Settings, where Summary rows below detail chooses whether summary rows are below or above their groups.

9.5.2 Subtotals

The Subtotal command inserts a subtotal row for each group of records and a grand total at the end, and it outlines the result automatically. It works on a sorted range, not a table.

1. Sort the data by the column you want to group by, such as Region.
2. Click a cell in the data and choose Data › Subtotal.
3. Set the options:
   • At each change in: the column you sorted by.
   • Use function: Sum, Count, Average, Max, Min and so on.
   • Add subtotal to: the columns to total.
   • Replace current subtotals: uncheck it to add a second set of subtotals, such as averages under sums.
   • Page break between groups and Summary below data.
4. Click OK.

Excel inserts rows with the SUBTOTAL function, such as =SUBTOTAL(9,D2:D14), and a grand total row. Use the outline's level buttons to see only the grand total (level 1), the subtotals (level 2) or everything (level 3). To remove the subtotals, open Data › Subtotal again and click Remove All.

9.5.3 The SUBTOTAL function

The rows that the Subtotal command inserts, and a table's total row, use the SUBTOTAL function. Its first argument picks the calculation:

Calculation	Includes manually hidden rows	Ignores manually hidden rows
AVERAGE	1	101
COUNT	2	102
COUNTA	3	103
MAX	4	104
MIN	5	105
SUM	9	109

Both sets ignore rows hidden by a filter. SUBTOTAL also ignores other SUBTOTAL results in its range, so a grand total doesn't count the subtotals twice.

9.5.4 Removing duplicate records

Remove Duplicates deletes rows that repeat other rows. Click a cell in the data and choose Data › Remove Duplicates (or Table Design › Remove Duplicates in a table):

• Check My data has headers if the first row is headings.
• Check the columns that must match for rows to count as duplicates. With all columns checked, only identical rows are removed. With only Email checked, every row with an email address already seen is removed, even if other columns differ.
• Click OK. Excel reports how many duplicates it removed and how many unique values remain.

Excel keeps the first occurrence and removes later ones.

9.6 Custom conditional formatting rules

Lesson 8 of Introduction to Excel covered the built-in conditional formats: highlight rules, top/bottom rules, data bars, color scales and icon sets. MO-211 adds custom rules, which you create in the New Formatting Rule dialog box, and rules that use formulas.

9.6.1 Creating a custom rule

Select the cells and choose Home › Conditional Formatting › New Rule. Pick a rule type:

Rule type	Use it to
Format all cells based on their values	Build a color scale, data bar or icon set with your own colors, types and thresholds. For icon sets, you can set each icon's value and type (number, percent, formula, percentile) and choose Show Icon Only.
Format only cells that contain	Format by value (between, greater than…), specific text, dates occurring, blanks or errors.
Format only top or bottom ranked values	Format the top or bottom n items or n percent.
Format only values that are above or below average	Including one, two or three standard deviations above or below.
Format only unique or duplicate values	Highlight duplicates or values that appear once.
Use a formula to determine which cells to format	Format cells wherever a formula is TRUE.

Click Format to choose the number format, font, border and fill.

9.6.2 Rules that use formulas

A formula rule formats each cell where the formula returns TRUE. Write the formula as if for the top-left cell of the selection. Excel adjusts it for every other cell, just as if you'd copied it.

The dollar signs decide what the formula looks at:

• Format a whole row based on one column. Select A2:F50 and use =$E2>1000. The $ locks column E, so every cell in a row checks that row's value in E. The row number isn't locked, so each row checks itself.
• Compare each cell with one fixed cell. Select B2:B50 and use =B2>$H$1. Each cell compares itself with H1.
• Combine conditions. =AND($C2="West",$E2>1000) formats rows for the West region over 1,000. =OR($F2="Late",$F2="Lost") formats rows with either status.
• Use dates. =$D2<TODAY() formats rows whose due date has passed.
• Shade every other row. =MOD(ROW(),2)=0 formats even-numbered rows.

9.6.3 Managing rules

Home › Conditional Formatting › Manage Rules opens the Conditional Formatting Rules Manager:

• Show formatting rules for chooses the current selection, the whole worksheet or another sheet.
• New Rule, Edit Rule and Delete Rule change the list. Duplicate Rule copies one to start a similar rule.
• Applies to shows each rule's range. Change it by typing or selecting a new range.
• The up and down arrows change the order. Rules higher in the list are applied first. When two rules set the same property, such as fill color, the higher rule wins.
• Stop If True stops Excel from applying lower rules to a cell once that rule is TRUE.

To remove rules, use Delete Rule here, or Conditional Formatting › Clear Rules › from Selected Cells or from Entire Sheet.

9.7 Practice