Chapter 1

Getting Started with Spreadsheets

IN THIS CHAPTER

Getting oriented in Excel

Creating and saving workbooks

Working with spreadsheet templates

Entering and editing data

Sharing workbooks with others

In this chapter, we show you how to get started with spreadsheets in Microsoft Excel. We cover the key features of the Excel interface, show you how to create and save Excel workbooks and enter and edit data in worksheets, and much more.

Exploring Excel's User Interface

Whenever you launch Excel, it eagerly greets you with a blank workbook featuring a single worksheet tab. You also see a tabbed command interface across the top that Microsoft calls the ribbon (see Figure 1-1). The ribbon includes a set of static tabs, along with other contextual tabs that pop up when Excel deems them necessary. Just above the ribbon sits a collection of icons known as the Quick Access Toolbar - your personal stash of frequently used commands, which we cover in the "Customizing the Quick Access Toolbar" section later in this chapter.

Below the ribbon, you see a row composed of three sections:

• Name Box: Most users rely on this area to see the address of the currently selected worksheet cell.

  You can do a surprising number of things in the Name Box - more than two dozen! Chapter 5 of this minibook provides more details.

• Insert Function button: Excel's real power comes from its hundreds of worksheet functions, which let you crunch numbers from simple sums to complex calculations using inputs called arguments. Clicking this button opens a search-friendly dialog box, followed by another dialog box that walks you through building the formula step-by-step. The "Leveraging worksheet functions" section later in this chapter offers a straightforward way to get started.
• Formula Bar: This expandable section shows what's inside the selected worksheet cell. If a cell has a formula, you see the result in the cell, while the Formula Bar reveals the formula itself. You can edit cell contents directly in the Formula Bar or within the cell itself. To do so, double-click a cell or press F2 (Windows) or ???+U (macOS).

FIGURE 1-1: Microsoft Excel's user interface.

Cells can contain up to 32,000 characters, including text, numbers, symbols, nonprintable characters like carriage returns, and formulas.

The Expand Formula Bar button sits on the right-hand side of the Formula Bar. Clicking it - or pressing Ctrl+Shift+U (Windows) or ???+Shift+U (macOS) - expands the Formula Bar to show up to 11 rows, letting you see long formulas but reducing the number of visible worksheet rows. You can also resize the Formula Bar by dragging its bottom edge, balancing the need to see more cell contents and keeping more of the worksheet in view.

Below the Formula Bar lies the worksheet grid, made up of a fixed number of rows and columns. Rows are numbered from 1 to 1,048,576, while columns follow a lettered system: A through Z for the first 26, AA through AZ for the next 26, continuing on until the final column, XFD.

Every worksheet contains 1,048,576 rows and 16,384 columns - more than 17 billion cells in total - giving you plenty of room to get lost in data.

Traversing the ribbon

The ribbon in Excel is divided into two groups: the main tabs, which are always present, and the tool tabs, which appear when they're needed. The main tabs are as follows:

• File: Opens the Backstage View, where Excel handles all the behind-the-scenes business - opening, saving, printing, sharing workbooks, and tweaking settings.

• Home: This is where Excel keeps the most-used commands for formatting, editing, sorting, and filtering.

  If all those icons are starting to blur together, think of ScreenTips as little cue cards. Hover over any ribbon or toolbar command, and Excel will remind you what it does. Windows users even get keyboard shortcuts - macOS users, not so much.

• Insert: Think of this as Excel's "Add Stuff" tab. Spice things up with shapes, images, and text boxes (see Chapter 3 of this minibook), or drop in PivotTables and slicers (see Book 5, Chapter 2) or PivotCharts (see Book 5, Chapter 4).
• Page Layout: Fine-tunes how your worksheet looks when printed - margins, scaling, and other settings that help you wrestle Excel into submission - on paper, at least (see Chapter 3 of this minibook).
• Formulas: The command center for Excel's number-crunching wizardry. Dig into function libraries, troubleshoot formulas, and tweak calculation settings.
• Data: The Get & Transform Data section and Queries & Connections section let you pull in info from just about anywhere using Power Query (see Book 6, Chapter 5). The Data Types feature adds self-updating "smart" cells. The rest of the tab wrangles sorting, filtering (see Chapter 4 of this minibook), and what-if analysis, like scenario management (see Book 4, Chapter 2).
• Review: This tab helps you catch typos, flag accessibility issues (see Chapter 3 of this minibook), add comments (see the "Collaborating with Others" section of this chapter), and locking down spreadsheets to protect them.
• View: This tab lets you control what you see in Excel, including splitting worksheet windows (see Chapter 5 of this minibook), switching between custom views (Chapter 5 again), and adjusting zoom levels (Chapter 5 once more).
• Help: This tab connects you to support resources, troubleshooting information, and usage tips for Excel.

Depending on your rights and licensing, the Automate tab may appear, granting access to Office Scripts for automating repetitive tasks with JavaScript-based scripts. Any user can enable the Developer tab, packed with tools for creating and editing macros (see Book 8, Chapter 1).

SEARCHING EXCEL'S MENUS

If you're feeling adrift, get back on firm ground with Excel's multipurpose Search feature. Access it in one of the following ways:

• Click the Search field in Excel's title bar.
• Press Alt+Q (Windows) or ???+Ctrl+U (macOS).

Choose from suggestions based on recent or common actions, or start typing to generate a dynamic list of commands. To search within the worksheet, enter a term and select Find in Worksheet (Windows) or Find (macOS).

A second Search the Menus field appears when you right-click the worksheet frame or any cell. This search is limited to ribbon commands - use Find instead when hunting for data (covered in Chapter 5 of this minibook).

You can customize the ribbon by adding new command groups to existing tabs or creating entirely new tabs:

1. Open the Customization dialog box:
   • Windows: On the File tab of the ribbon, click Options, and then click Customize Ribbon to open the Excel Options dialog box. Alternatively, right-click the ribbon and select Customize the Ribbon.
   • macOS: Choose Excel???Preferences and click Ribbon & Toolbar (opens the Ribbon & Toolbar pane of the Excel Preferences dialog box).
2. Show or hide tabs by toggling the check boxes in the Customize the Ribbon list.
3. Edit an existing tab:
   1. Select a tab from the Customize the Ribbon list (defaults to Main Tabs). Select Tool Tabs or All Tabs to explore further.
   2. Click New Group (+ then New Group on macOS) to add a custom group.
   3. Rename it by clicking Rename (click . then Rename on macOS).
   4. Add commands by selecting them from the Choose Commands list and clicking Add >> (> on macOS). Use << Remove (< on macOS) to remove commands.
4. To create a new tab, click New Tab (+ then New Tab on macOS), and then rename it and add groups and commands as needed.

Here's a peek at some of the tool tabs that appear when a task calls for them:

• Header & Footer: This tab takes the stage when you decide what should grace the top or bottom of each page - because every sheet deserves a proper introduction and a grand finale (see Chapter 3 of this minibook).
• Analyze: This tab steps into the spotlight for PivotTables and PivotCharts (see Book 5, Chapters 2 through 4), giving you tools to connect, explore, and wrangle your data.
• Design: This tab makes a stylish appearance when you select a cell in an Excel table (see Chapter 4 of this minibook) or a PivotTable, or you click a chart or PivotChart.
• Format: This feature-specific tab appears for charts, PivotCharts, shapes, and images.
• Query: This tab enables you to edit, load, reuse, combine, and share data connections through Power Query (see Book 6, Chapter 5).

Enterprise and government users can - and frankly should - enable the Inquire add-in. This powerhouse of a tool helps you document workbooks, compare workbook versions, and fine-tune performance, making it a must-have for...