Course chapters
Training · Track 14. Spreadsheets · Chapter 14.3 of 5
14.3 Tables, Find and Views 2:17
Format a range as a table, find and replace text, and save named views without changing the cells.
Shows Holarch 2.35.
What you’ll learn
- Format a range as a named table and use it in formulas
- Find and replace text across the workbook in one step
- Turn on filter buttons and sort a column
- Save a named view and switch between views
Organize the EO-1 mass budget: format the subsystem rows as a table with a name, banded rows and filter buttons, rename a subsystem everywhere with Find and Replace, then sort the table by mass in a named view shared with the project, and switch back to the Default view, which still shows the cells as stored.
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 your workbook from chapter 14.2.
- Select A2:E8, click Table, name it
Subsystemsand click OK. - Open Find and Replace, find
Comms, open the replace field, typeCommunications, click Find All and then Replace All. - Click in the table, open Sort & Filter and turn on Filter Buttons. On With contingency, choose Sort Z to A.
- Click Save View, name it
Heaviest first, choose Everyone with access and save. Switch between Default and Heaviest first.
You're done when the table is named Subsystems, Comms reads Communications, and the Heaviest first view sorts the rows while Default shows them in their order.
Check yourself
- How does a formula refer to the Mass column of the table Subsystems?
- Does sorting in a view change the cells?
- What does a view keep besides sort and filters?
Show the answers
How does a formula refer to the Mass column of the table Subsystems?
As Subsystems[Mass]: the table name with the column name in square brackets.
Does sorting in a view change the cells?
No. A view changes only how you see the sheet; switching back to Default shows the rows in their order.
What does a view keep besides sort and filters?
Hidden columns, column widths and frozen panes.
Read more
Transcript
Show the transcript (narration and what is on screen)
Introduction
Chapter three: tables, find and views. A table gives a block of rows a name and a style. Find and Replace changes text in one step. And views show the same cells in different ways.
In this chapter you organize the budget.
Tables
Select the headings and the six subsystems, A2 to E8, and click Table.
On screen: Name Box A2:E8 → Table.
The dialog shows the range, and that the first row holds the headers. Name the table Subsystems, and click OK.
On screen: Range A2:E8, headers; name Subsystems → OK.
The table has a styled header row and banded rows, and grows when you add a row below it. Its name works in formulas: Subsystems, with a column name in square brackets.
On screen: The table: header style, banded rows.
Find and Replace
One subsystem is abbreviated. Open Find and Replace. Find Comms, open the replace field, and type Communications.
On screen: Find ▾ → Comms; ⇄ → Communications.
Find All lists each match with its sheet and cell. Click Replace All: the name is changed everywhere it appears, in one undo step. Close the bar.
On screen: Find All → the results; Replace All → close.
Named views
Now a view. Views change how you see the sheet, never the cells. Click in the table, open Sort and Filter, and turn on Filter Buttons.
On screen: B3 → Sort & Filter ▾ → Filter Buttons.
Click the filter button on With contingency, and choose Sort Z to A: the heaviest subsystem comes first.
On screen: With contingency ▾ → Sort Z to A.
The views bar shows the unsaved change. Click Save View, name it Heaviest first, choose Everyone with access, and save it.
On screen: Save View → Heaviest first, Everyone with access → Save View.
A view keeps sort, filters, hidden columns, widths and frozen panes. Click Default: the rows are back in their order, because the cells never changed. Everyone with access can switch views without affecting anyone else.
On screen: Default → rows in order; Heaviest first.
Recap
To recap: format rows as a named table, use Find and Replace for changes in many cells, and save sorts and filters as views.
In the next chapter, you chart the budget and feed it from the model.