Course chapters
Training · Track 14. Spreadsheets · Chapter 14.2 of 5
14.2 Formats and Layout 3:16
Format numbers with format codes, highlight cells by value, merge a title and freeze the headings.
Shows Holarch 2.35.
What you’ll learn
- Merge and center a title and make headings bold
- Fit column widths to their contents
- Apply percent and custom number format codes
- Highlight values with a conditional formatting rule
- Freeze the heading rows
Format the EO-1 mass budget: merge and center a title over the columns, make the headings bold, fit the column widths, show the contingency as percentages, give the masses a custom format code that adds the unit, highlight the heaviest subsystems with a conditional format, and freeze the title and headings so they stay in view.
Project files
- Starting project (.holarch.json; Manage Projects → Project Files ▾ → Import Project (.json)…)
- Finished project (.holarch.json; Manage Projects → Project Files ▾ → Import Project (.json)…)
The video plays from holarch.app; see the Privacy Policy.
Practice
Do this yourself in Holarch, on CubeSat EO-1.
- Open the chapter's starting file, or the workbook from chapter 14.1, on the Budget sheet.
- Type
EO-1 Mass Budgetin A1. Select A1:E1 and click Merge & Center. Select A1:E2 in the Name Box and click Bold. - Double-click the right edge of the B, D and E column headers to fit them.
- Select D3:D8 and click Percent. Select C3:C8, open Format Cells → Custom and type
0.00" kg". Do the same for E3:E10, and give E11 the code0.0%. - Select E3:E8 and open Conditional Formatting → Highlight Cells Rules → Greater Than; type
2. - Select A3 and choose Freeze Panes. Scroll down: rows 1 and 2 stay in view.
You're done when masses read in kg, the margin reads as a percentage, subsystems over 2 kg are highlighted and the headings stay put when you scroll.
Check yourself
- What does the format code 0.00" kg" do to the value 2.5?
- Why does selecting A3 before Freeze Panes keep rows 1 and 2 in view?
- Does a conditional formatting rule change the cell's value?
Show the answers
What does the format code 0.00" kg" do to the value 2.5?
It shows 2.50 kg. The value stays the number 2.5, so formulas still calculate with it.
Why does selecting A3 before Freeze Panes keep rows 1 and 2 in view?
Freeze Panes freezes the rows above and the columns to the left of the active cell.
Does a conditional formatting rule change the cell's value?
No. It changes only how the cell looks while its condition holds.
Read more
Transcript
Show the transcript (narration and what is on screen)
Introduction
Chapter two: formats and layout. A format changes how a value looks, never the value itself, so formulas keep calculating with the full number.
In this chapter you make the budget easy to read.
Title and headings
Give the sheet a title. In A1, type EO-1 Mass Budget.
On screen: A1: EO-1 Mass Budget.
Select A1 to E1 in the Name Box. Open Merge, and choose Merge and Center: the five cells become one, with the title in the middle.
On screen: Name Box A1:E1 → Merge ▾ → Merge & Center.
Make the title and the headings bold: select them, and click Bold, or press Command B.
On screen: Name Box A1:E2 → Bold.
The subsystem names are wider than their column. Double-click the right edge of column B's heading, and the column fits its longest entry. Do the same for D and E.
On screen: Double-click the header borders of B, D and E: the columns fit.
Number formats
Now the numbers. Select the contingencies, D3 to D8, and click Percent. Zero point one shows as ten percent.
On screen: Name Box D3:D8 → %.
Select the masses, C3 to C8, and open Number Format. The menu lists the common formats, with a sample of each. Choose More Number Formats.
On screen: Name Box C3:C8 → Number Format ▾ → More Number Formats….
Format Cells shows the format as a code. Choose Custom, and type zero point zero zero, then a space and k g, in quotes. Text in quotes is shown as it is, so each mass reads in kilograms. The sample shows the result. Click OK.
On screen: Category: Custom; code 0.00 " kg" → sample → OK.
The masses with contingency get the same code. Select E3 to E10, open More Number Formats again, choose Custom, and type the code.
On screen: Name Box E3:E10 → More Number Formats… → Custom → 0.00 " kg" → OK.
The margin is a fraction. Select E11, and give it the code zero point zero percent: a percentage with one decimal.
On screen: E11 → More Number Formats… → Custom → 0.0% → OK.
Conditional formatting
Conditional formatting colors cells by their values. Select E3 to E8, open Conditional Formatting, then Highlight Cells Rules, and choose Greater Than.
On screen: Name Box E3:E8 → Conditional Formatting ▾ → Highlight Cells Rules ▸ → Greater Than….
Type two, keep the red fill, and click OK. The two heaviest subsystems stand out, and the color follows the values when they change.
On screen: Value 2 → OK: Payload Camera and Structure and Thermal turn red.
Freeze panes
Last, freeze panes keep the title and headings in view while you scroll. Select A3, open Freeze, and choose Freeze Panes: everything above the active cell stays put.
On screen: A3 → Freeze ▾ → Freeze Panes.
Scroll the sheet down. The rows move under the frozen title and headings.
On screen: Scroll down, then back up.
Recap
To recap: merge and center a title, fit columns by double-clicking their edge, choose formats from the menu or write a format code, color cells with a rule, and freeze the headings.
In the next chapter, you turn the budget into a table, find and replace text, and save views.