Chapter 4: Formatting Cells and Ranges

Introduction to Excel taught you to enter data, format it and fill formulas down a column. The MO-210 exam goes further. It expects you to choose exactly what to paste, generate numbers with functions, use every alignment and number-format option, format many sheets at once, name ranges, and add small in-cell charts called sparklines. This chapter covers each of those skills.

4.1 Paste Special and Auto Fill

4.1.1 Paste Special

A regular paste copies everything about a cell: its value or formula, its formatting, its comments and its validation. Paste Special pastes only the parts you choose. Copy the cells, select the destination, then either click the arrow under Home › Paste for a gallery of options or choose Paste Special (Ctrl+Alt+V) for the full dialog box.

The options you'll use most:

Option	Pastes	Typical use
Values	Results only, no formulas or formatting	Turn formulas into fixed numbers
Values & Number Formatting	Results with their number formats	Fixed numbers that still look like currency or percentages
Formulas	Formulas, without formatting	Reuse a calculation in a differently formatted area
Formatting	Formatting only	Copy a look, like Format Painter
Keep Source Column Widths	Everything, plus the source column widths	Keep a pasted table's column widths
Transpose	Everything, with rows and columns swapped	Turn a column into a row
Paste Link	A formula that refers to the source cell (=Sheet1!B2)	Keep the destination in sync with the source
Picture / Linked Picture	An image of the cells	Put a snapshot of a range on a dashboard

The Paste Special dialog box also has an Operation section. Add, Subtract, Multiply and Divide combine the copied value with the values already in the destination. To raise every price in a column by 5 percent, type 1.05 in a spare cell, copy it, select the prices, and choose Paste Special › Values › Multiply. Skip blanks leaves destination cells alone wherever the copied range is empty.

4.1.2 Auto Fill

Introduction to Excel introduced the fill handle, the small square at the bottom right of a selection. Drag it to copy a value or formula, or to continue a series. Here are a few more details for the exam:

• Excel recognizes series of numbers (select two cells, such as 5 and 10, to set the step), dates, days (Mon, Tue…), months (Jan, Feb…), quarters (Q1, Q2…) and text with numbers (Item 1, Item 2…).
• Hold Ctrl while you drag to switch between copying and filling a series.
• Double-click the fill handle to fill down as far as the data in the next column goes.
• The Auto Fill Options button appears after you fill. It lets you switch between Copy Cells, Fill Series, Fill Formatting Only and Fill Without Formatting. For dates it adds Fill Days, Fill Weekdays, Fill Months and Fill Years.
• Home › Fill fills without dragging: Down (Ctrl+D), Right (Ctrl+R), Up and Left.

Chapter 9 covers the Series dialog box and Flash Fill, which are on MO-211.

4.2 Inserting and deleting cells, rows and columns

4.2.1 Several rows or columns at once

To insert several rows at once, select that many rows by dragging across their row headings, then right-click and choose Insert. Excel inserts the same number of rows above the selection. Columns work the same way and are inserted to the left. To delete rows or columns, select them and choose Delete.

You can also use Home › Insert › Insert Sheet Rows or Insert Sheet Columns, and Home › Delete. Ctrl++ (Ctrl and the plus sign) inserts and Ctrl+- deletes.

4.2.2 Inserting and deleting cells

Inserting a cell (instead of a whole row or column) pushes nearby cells out of the way. Select the cells where the new ones should go and choose Home › Insert › Insert Cells. Excel asks how to make room:

• Shift cells right moves cells in the same rows to the right.
• Shift cells down moves cells in the same columns down.
• Entire row and Entire column insert whole rows or columns.

Home › Delete › Delete Cells works the other way: Shift cells left or Shift cells up closes the gap.

4.3 Generating numbers

Two MO-210 functions create numbers instead of calculating them from your data.

4.3.1 RANDBETWEEN

RANDBETWEEN(bottom, top) returns a random whole number from bottom to top, including both. For example, =RANDBETWEEN(1, 100) returns a number from 1 to 100.

RANDBETWEEN is volatile: it picks a new number every time the worksheet recalculates, which happens whenever you change anything. To keep a set of random numbers, copy them and paste them back as Values.

4.3.2 SEQUENCE

SEQUENCE(rows, [columns], [start], [step]) returns a list of numbers in order. It's a dynamic array function: you type it in one cell, and the results spill into the cells below and to the right.

Formula	Result
=SEQUENCE(5)	1, 2, 3, 4, 5 down a column
=SEQUENCE(1, 12)	1 through 12 across a row
=SEQUENCE(10, 1, 100, 10)	100, 110, 120 … 190 down a column
=SEQUENCE(3, 4)	1 through 12 in a 3-row, 4-column block, filling across first

The spilled results have a blue border when you select any of them. Only the first cell holds the formula. The rest show the formula grayed out in the formula bar and can't be edited on their own. If something is in the way, the formula returns #SPILL!. Clear the blocking cells and the results appear.

4.4 Alignment and text control

The Home tab's Alignment group, and the Alignment tab of Format Cells (Ctrl+1), control where text sits in a cell.

4.4.1 Merging and unmerging

The Merge & Center button's arrow has four choices:

• Merge & Center joins the selected cells into one and centers the contents.
• Merge Across merges each selected row on its own. It's useful for several rows at once.
• Merge Cells joins the cells without centering.
• Unmerge Cells splits a merged cell back into separate cells.

Only the upper-left cell's value is kept when you merge. Excel warns you if other cells have data.

4.4.2 Alignment, orientation and indentation

Setting	Where
Vertical alignment (top, middle, bottom)	Top Align, Middle Align, Bottom Align buttons
Horizontal alignment (left, center, right)	Align Left, Center, Align Right buttons
Indentation	Increase Indent and Decrease Indent, or the Indent box in Format Cells
Orientation	The Orientation button: Angle Counterclockwise, Angle Clockwise, Vertical Text, Rotate Text Up, Rotate Text Down
An exact angle	Format Cells › Alignment › Orientation, with a value from -90 to 90 degrees

4.4.3 Wrap text and Shrink to fit

Wrap Text shows a long entry on several lines inside its cell, and the row grows taller to fit. To start a new line at an exact spot while you type, press Alt+Enter.

Shrink to fit (Format Cells › Alignment) makes the font smaller so the entry fits on one line in the cell. You can't use it together with Wrap Text.

4.5 Number formats, cell styles and Format Painter

4.5.1 Number formats

A number format changes how a value looks without changing the value itself. The Number Format list in Home's Number group has the common formats:

Format	1234.5 looks like	Notes
General	1234.5	The default
Number	1234.50	Two decimal places by default
Currency	$1,234.50	Symbol next to the number
Accounting	$ 1,234.50	Symbol at the left edge of the cell; zeros show as a dash
Short Date / Long Date	5/18/1903 / Monday, May 18, 1903	Dates are stored as numbers
Percentage	123450.00%	Multiplies by 100 for display
Fraction	1234 1/2
Scientific	1.23E+03
Text	1234.5	Treats the entry as text

The buttons below the list apply Accounting Number Format (its arrow chooses other currencies), Percent Style, Comma Style (Accounting without the symbol), and Increase Decimal and Decrease Decimal.

4.5.2 The Format Cells dialog box

Format Cells (Ctrl+1, or the launcher in the Number, Font or Alignment group) has every formatting option in six tabs: Number, Alignment, Font, Border, Fill and Protection. Use it when a task asks for something the ribbon doesn't offer directly, such as:

• a negative number style, like red with parentheses (Number tab, Number or Currency category)
• a particular date format, such as 14-Mar-2027 (Number tab, Date category)
• a double bottom border or a diagonal border (Border tab)
• a pattern or gradient fill (Fill tab, Fill Effects and Pattern Style)
• strikethrough, superscript or subscript (Font tab)

4.5.3 Cell styles

A cell style is a named set of formats: font, fill, borders, number format and alignment. Home › Cell Styles shows the built-in styles in groups:

• Good, Bad and Neutral
• Data and Model, such as Calculation, Input, Output, Note and Warning Text
• Titles and Headings, such as Title, Heading 1 through Heading 4, and Total
• Themed Cell Styles, colored fills that follow the workbook's theme
• Number Format: Comma, Comma [0], Currency, Currency [0] and Percent

Point at a style to preview it, and click to apply it. To create your own, format a cell the way you want, then choose Cell Styles › New Cell Style and give it a name. To change a style everywhere it's used, right-click it in the gallery and choose Modify.

4.5.4 Format Painter

Format Painter (Home › Format Painter, the paintbrush) copies formatting from one cell to others:

1. Select the cell whose formatting you want.
2. Click Format Painter, then click or drag over the destination cells.

Double-click Format Painter to keep it on so you can paint several ranges. Press Esc or click the button again to turn it off.

4.5.5 Clearing formatting

Home › Clear (the eraser) removes parts of a cell:

Choice	Removes
Clear All	Contents, formats, comments and notes
Clear Formats	Formatting only; the values stay
Clear Contents	Values and formulas; the formatting stays (same as pressing Delete)
Clear Comments and Notes	Comments and notes only
Clear Hyperlinks / Remove Hyperlinks	The link; Clear keeps the link's formatting, Remove takes it off too

4.6 Formatting grouped worksheets

When several sheets share the same layout, such as one sheet per month, you can group them and change them all at once. Anything you do to the active sheet while they're grouped happens on every sheet in the group, in the same cells: typing, formatting, inserting rows, page setup and headers and footers.

• Group adjacent sheets: click the first sheet's tab, then Shift+click the last.
• Group sheets that aren't next to each other: Ctrl+click each tab.
• Group every sheet: right-click any tab and choose Select All Sheets.

The title bar shows [Group] while sheets are grouped. To ungroup, right-click a tab and choose Ungroup Sheets, or click a tab that isn't in the group.

To copy content and formatting that already exists on one sheet to the others, group the sheets, select the range on the sheet that has it, and choose Home › Fill › Across Worksheets. Then choose All, Contents or Formats.

4.7 Named ranges

A named range (or defined name) gives a cell or range a meaningful name, such as TaxRate or Q1_Sales. You can use the name in formulas instead of the address, as in =Subtotal*TaxRate, and pick it from the Name Box to jump to it.

4.7.1 Naming rules

• A name starts with a letter, an underscore (_) or a backslash (\).
• It can contain letters, numbers, periods and underscores, but no spaces. Use Q1_Sales or Q1Sales.
• It can't look like a cell reference, such as A1 or R2C3.
• Names aren't case-sensitive. Sales and SALES are the same name.

4.7.2 Defining a name

There are three ways to name a range:

• The Name Box. Select the range, click in the Name Box, type the name and press Enter. This is the quickest way.
• Define Name. Select the range and choose Formulas › Define Name. Type the Name, choose its Scope, add an optional Comment, and check Refers to.
• Create from Selection. Select a range that includes its labels, such as a table with headings in the top row, and choose Formulas › Create from Selection. Check where the labels are: Top row, Left column, Bottom row or Right column. Excel names each column or row after its label and replaces spaces with underscores.

Scope decides where a name works. A name with Workbook scope can be used on any sheet. A name scoped to one worksheet can be used only on that sheet without a sheet prefix. Unless a task says otherwise, leave the scope as Workbook.

4.7.3 Using and managing names

• In a formula, type the name, or choose Formulas › Use in Formula, or press F3 to pick from a list. For example, =SUM(Q1_Sales).
• To go to a named range, choose it from the Name Box or from Go To (F5).
• The Name Manager (Formulas › Name Manager, Ctrl+F3) lists every name with its value, its reference, its scope and its comment. Use New, Edit and Delete there. To change the range a name refers to, select the name and edit Refers to at the bottom of the window.

4.8 Sparklines

A sparkline is a tiny chart inside one cell. It shows the trend in a row or column of numbers beside the numbers themselves.

4.8.1 Inserting sparklines

1. Select the cells where the sparklines should go, usually an empty column next to the data, one cell per row.
2. Choose Insert › Sparklines and one of the three types:
   • Line shows a trend over time.
   • Column compares values as small bars.
   • Win/Loss shows only whether each value is positive or negative.
3. In the Create Sparklines dialog box, enter the Data Range (for example, B2:M10). The Location Range is the cells you selected. Click OK.

Excel creates one sparkline per row of data and groups them, so formatting one formats them all.

4.8.2 Formatting sparklines

Select a sparkline to see the Sparkline tab:

Group	Use it to
Sparkline	Edit Data to change the data or location range.
Type	Switch between Line, Column and Win/Loss.
Show	Highlight the High Point, Low Point, Negative Points, First Point and Last Point, and show Markers (line sparklines only).
Style	Choose a style, and set the Sparkline Color and Marker Color.
Group	Axis options, such as giving every sparkline the same minimum and maximum so they can be compared; Group and Ungroup; Clear to delete sparklines.

4.9 Practice