Microsoft 365 Excel For Dummies

Introduction

Welcome to Microsoft 365 Excel! My goal in this book is to help you build efficient spreadsheets while maintaining data integrity, whether you're new to Excel or already have experience. For beginners, I guide you through getting oriented, and for experienced users, I aim to help you close any gaps in your knowledge. In my webinars, I often say, Either you work Excel, or it works you! Unfortunately, many users learn just enough to use Excel like a blunt instrument, so I chose the topics in this book specifically to help you move beyond that.

Remember Microsoft 365 Excel is an application you install on your desktop computer. In contrast, Excel for the Web (available at www.office.com/launch/Excel/) runs directly in your browser, and Excel Mobile is designed for use on phones or tablets. While there is significant overlap between these versions, this book focuses specifically on the Windows and macOS versions of Excel.

Microsoft Excel was first released for the Apple Macintosh on September 30, 1985. The AI-infused application we use today only faintly resembles that early version. While it's highly unlikely that anyone still uses a nearly 40-year-old version of Excel, you may still be working with Excel 2021, Excel 2019, or an even earlier version. Much of what I cover in this book applies to those versions, but in some chapters, you may find yourself reading about features you'll have access to only after you upgrade to a newer version of Excel.

About This Book

Microsoft Excel remained relatively static until 2010, when the Office 365 platform was first introduced, later rebranded as Microsoft 365 in 2017. Before then, new features were introduced in Excel every two to three years, a long and often frustrating wait (at least for me). Those days are behind us, as Microsoft 365 now provides ongoing updates with new features and improvements. However, not everyone receives updates at the same time, as users are divided into different channels:

Current Channel: Users receive monthly updates, with occasional out-of-band updates for critical fixes or security patches.

Monthly Enterprise Channel: Like the Current Channel, this provides monthly updates for business users.

Semi-Annual Enterprise Channel (Preview): This offers first access to features that will later be included in the Semi-Annual Enterprise Channel.

Semi-Annual Enterprise Channel: Updates are released twice a year, ensuring that only thoroughly evaluated, stable features are introduced.

Beta Channel: The cutting-edge channel where users who opt into the Microsoft 365 Insider program get early access to features still in development and testing.

Current Channel (Preview): A more stable version of the Beta channel — for users that are edgy, but not quite that edgy.

To check which channel you're in:

Windows: Go to File ⇒ Account — your channel and build appears next to the About Excel button.

macOS: Choose Help ⇒ Check for Updates, then click Advanced. Your channel (but not build number) appears in the Preferences dialog box.

Technical Stuff As you can see, Microsoft 365 Excel is a constantly evolving program. My editors and I have done our best to keep up with these changes and describe what, at times, has been a moving target. Think of it as Excel’s way of keeping track of its ever-evolving self — one incremental update at a time.

Warning Because updates occur so often in Excel, by the time this book is published, some features and screens may have changed. (On second thought, make that will have changed.)

Then there’s the matter of the subscription levels:

Individuals and Families: Microsoft 365 Personal and Family

Businesses: Business Basic (web/mobile only), Business Standard, and Business Premium, Apps for Business

Enterprises: E1 (web/mobile only), E3, E5

Education: A1 (web/mobile only) A3, A5

Government: G1, G3, G5 are like E1, E3, and E5, but add additional compliance for governmental requirements

All Microsoft 365 users have the same feature set for the most part, but some differences can arise, such as the Inquire add-in in the Enterprise versions that creates detailed reports on workbooks, can compare two versions of a workbook, can improve performance by cleaning up workbooks, and can build diagrams that show how cells and worksheets relate together. I discuss how to enable this add-in in Chapter 10.

Tip If you'd prefer a one-time purchase for Excel, Microsoft Office 2024 provides static versions of Excel and other Office apps. However, this version does not include access to the artificial intelligence features available in Microsoft 365 and will not receive any future feature or function updates.

Here’s how the book is broken down:

Part 1, Explaining Excel Fundamentals, helps you get oriented in Excel, covering basic calculations, formatting, data sorting and filtering, as well as the often-overlooked Table feature, which can enhance spreadsheet integrity and drop repetitive tasks.

Part 2, Mastering Formulas and Functions, introduces lookup functions for retrieving data within a workbook, transforming text without retyping, and incorporating decision-making into formulas. You also learn how to create dynamic formulas that spill into additional cells and explore formula tracing and debugging tools.

Part 3, Expanding Beyond the Basics, teaches you to navigate both small and large workbooks efficiently, create refreshable reports with PivotTables and PivotCharts, and apply data visualizations with conditional formatting. You also learn how to secure sensitive data and formulas.

Part 4, Automating Analysis, introduces Excel's AI-driven features and teaches you to streamline tasks by recording macros.

Part 5, The Part of Tens, provides ten useful keyboard shortcuts and equips you with ten disaster recovery techniques.

Before diving in, I must get some technical conventions out of the way:

Text that you're meant to type exactly as it appears in the book is formatted in monofont (which looks like text typed on an old typewriter).

Web addresses and programming code are also displayed in monofont. If you're reading a digital version of this book on a device connected to the internet, you can tap or click a web address to visit the site, like this: www.dummies.com.

To use Microsoft 365 Excel effectively, the minimum recommended technical requirements vary slightly depending on whether you're using a Windows or macOS device. For Windows users, a dual-core processor with a speed of 1.6 GHz or faster is needed, along with at least 4 GB of RAM for 64-bit systems (or 2 GB for 32-bit systems). You'll also need 4 GB of available hard disk space, a display with a resolution of at least 1280 x 768, and a DirectX 9-compatible graphics card with WDDM 2.0 or higher for Windows 10. The software is compatible with Windows 10, Windows 11, and recent versions of Windows Server, and an internet connection is needed for product activation, updates, and cloud features.

For macOS users, Microsoft 365 Excel needs an Intel processor or Apple Silicon (M1, M2, or later), with at least 4 GB of RAM and 10 GB of available disk space. A display resolution of 1280 x 800 or higher is recommended, and the operating system should be one of the three most recent versions of macOS. Like Windows, an internet connection is necessary for activation, updates, and some online features. Additionally, users on both platforms will need a current browser (such as Microsoft Edge, Chrome, Safari, or Firefox) and a Microsoft account to access all the features of Microsoft 365.

For best performance, particularly when working with large datasets or advanced tools like Power Query or PowerPivot, higher specifications — such as faster processors and added RAM — are recommended.

When I discuss a command to choose, I separate the elements of the sequence with a command arrow that looks like this: ⇒ . For example, when you see Data ⇒ Sort, it means you should activate the Data tab in Excel's ribbon interface and then choose the Sort command. Some ribbon commands have drop-down menus, and I’ll often include the corresponding keyboard shortcuts for added convenience.

Remember The ribbon interface sometimes collapses command groups based on your screen resolution or the size of the Excel window. If a command you’re looking for seems to be missing, try expanding the window or clicking the drop-down arrow in the collapsed group to reveal hidden options.

Foolish Assumptions

I had to make some assumptions about you while authoring this book, so here are my educated guesses:

You want to learn how to use a spreadsheet or improve how you already use spreadsheets.

You may want to analyze your data using Excel features and/or worksheet functions, and perhaps automate repetitive tasks.

You have a personal computer running Windows 10 or 11 (I drafted this book in Windows 10 because I have my reasons) or a Mac running macOS 12 Monterey or later.

Ideally, you have Microsoft 365 Excel on your computer, but much of this book also applies to older versions of Excel. In fact, a good part of this book can be used with Excel for the Web or even Google Sheets.

Icons Used in This Book

Throughout the book, I use icons to draw your attention to key concepts that I don't want you to miss. Sometimes these icons highlight tips to help you save time, while in other cases, they focus on keeping your spreadsheets safe and secure.

Tip This icon points out time-saving tricks or nuances that you may come across in Excel.

Remember This icon highlights tricky aspects of Excel that you should be aware of and keep in mind as you work.

Warning Caution: Contents Hot. Oh, wait — wrong type of warning! You won’t burn yourself if this book falls into your lap but do pay close attention to the warnings you encounter. They’re here to help you avoid issues that could cause problems in your spreadsheets or, more commonly, lead to unnecessary frustration.

Technical Stuff At times, I may include some geeky details about Excel, your web browser, or your computer. If technical stuff isn’t your cup of tea, feel free to skip over it without missing out on the main content.

Crossreference Think of this as an oh, by the way that points you to more details elsewhere in the book.

Beyond the Book

This book comes, with two bonus chapters on automating data transformation with Power Query and dealing with many of Excel's frustrating prompts. You can find the bonus chapters here: www.dummies.com/go/excelfd.

In addition, this book comes with a free access-anywhere Cheat Sheet, which includes extra tips; a list of features available in Excel for Windows, but not in macOS; and a couple of macOS-exclusive features. To get this Cheat Sheet, simply go to www.dummies.com and type Microsoft 365 Excel For Dummies Cheat Sheet in the Search box.

Where to Go from Here

Part 1 covers the basics of Microsoft Excel, and even experienced users may uncover blind spots they didn't realize they had. Continue to Part 2 for an in-depth look at using worksheet formulas to automate calculations and transform data. Part 3 takes you beyond the basics, showing how to manage large spreadsheets, visualize data, and user-proof your work. Part 4 focuses on automation, including artificial intelligence and Visual Basic for Applications (VBA) macros. The book wraps up with Part 5, where you learn to turbo-charge your work with keyboard shortcuts and protect your spreadsheets from the many things that can go wrong.

Part 1

Explaining Excel Fundamentals

IN THIS PART …

Get started with spreadsheets.

Carry out basic calculations.

Format cells and ranges.

Sort, filter, and sift through data.

Enhance your spreadsheets with Excel tables.

Chapter 1

Getting Started with Spreadsheets

IN THIS CHAPTER

Bullet Getting oriented in Excel

Bullet Creating and saving workbooks

Bullet Working with spreadsheet templates

Bullet Entering and editing data

Bullet Sharing workbooks with others

Orienteering is an outdoor sport where participants navigate between waypoints on an unfamiliar course using nothing but a map and a compass. Think of it as a real-world treasure hunt — except instead of gold, you might find a mud puddle or a tree you’re pretty sure you’ve seen before. If that sounds a little too outdoorsy, consider the IKEA alternative: winding through an endless maze of bookshelves and meatballs, desperately searching for the exit.

Unlike both of those scenarios, this Excel adventure won’t leave you lost or questioning your life choices. No map, no compass — just a series of waypoints to guide you through the wilds of worksheets:

Using screenshots to draw your attention to various parts of Excel

Entering and editing data in an Excel worksheet

Creating and saving Excel workbooks, both from scratch and by way of prebuilt Excel templates

Sharing spreadsheets, collaborating in real-time, and keeping the conversation going

Our first stop is a Rosetta Stone of sorts, decoding what you're seeing onscreen when you launch Excel.

Exploring Excel’s User Interface

Whenever you launch Excel, it eagerly greets you with a blank workbook featuring a single worksheet tab — like a fresh notepad just waiting for you to scribble all over it. You also see a tabbed menu interface across the top that Microsoft insists on calling the ribbon — because apparently menu bar wasn't fancy enough. The ribbon includes a set of static tabs, along with other contextual tabs that pop up when Excel deems them necessary (whether you agree or not). Just above this ribbon spectacle sits a collection of icons known as the Quick Access Toolbar — your personal stash of frequently used commands, which I cover in the "Customizing the Quick Access Toolbar" section later in this chapter.

Below the ribbon, shown in Figure 1-1, you see a row composed of three sections, because two wouldn't be enough, and four would be excessive:

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

Tip You can do a surprising number of things in the Name Box — more than two dozen! No need to feel overwhelmed; think of this as a peek into just how much Excel can do. Chapter 11 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 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 on a cell or press F2 (Windows) or Cmd+U (macOS).

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

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

Tip The Expand Formula Bar button sits on the right-hand side of the Formula Bar. Clicking it — or pressing Ctrl+Shift+U (Windows) or Cmd+Shift+U (macOS) — expands the Formula Bar to show up to 11 rows. Just keep in mind that this reduces 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 twenty-six, AA through AZ for the next set, continuing on until the final column, XFD.

Technical Stuff Every worksheet contains 1,048,576 rows and 16,384 columns — over 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 headliners — main tabs that are always present — and the special guests, known as tool tabs. The rock star tabs that never leave the road, er screen, include:

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

Home: There's no place like the Home tab — it's where Excel keeps the most-used commands for formatting, editing, sorting, and filtering. Check here before you start clicking around like a lost tourist.

Tip 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. Drop in PivotTables, PivotCharts, and slicers (see Chapter 12), or spice things up with shapes, images (safe-for-work only, please), and text boxes (see Chapter 3).

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).

Formulas: The command center for Excel’s number-crunching wizardry. Dig into function libraries, troubleshoot formulas, and tweak calculation settings — because sometimes, Excel needs a little nudge to do the math right (see Chapters 6 through 10).

Data: Where Excel turns into a data-guzzling machine. The Get & Transform Data and Queries & Connections sections let you pull in info from just about anywhere using Power Query (see the bonus chapter Automating Data Transformation with Power Query available at www.dummies.com/go/excelfd). The Data Types feature (see Chapter 15) adds self-updating smart cells — because why settle for static data that just sits there? The rest of the tab wrangles sorting, filtering (see Chapter 4), and what-if analysis, like scenario management (see Chapter 18).

Review: This tab is like Excel's safety net — catching typos, flagging accessibility issues (see Chapter 3), adding comments (see the Collaborating with Others section of this chapter), and locking down spreadsheets to protect them from ahem enthusiastic but error-prone users (see Chapter 14).

View: The toolkit for controlling what’s visible in Excel. Freeze rows and columns in place (see Chapter 4), switch between custom views and adjust zoom levels (see Chapter 11), or step into the world of automation with macros and Excel’s Macro Recorder (see Chapter 16). Because sometimes, seeing is believing.

Help: Need somebody? Not just anybody? This tab (or, on macOS, the Help menu) connects users to support resources, troubleshooting information, and usage tips for Excel — so you can get by with a little help from this tab (or menu).

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 Cmd+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 11).

Technical Stuff 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 Chapter 16) — perfect for those ready to tinker under Excel’s hood.

Despite its name, the ribbon isn’t particularly fluid — the built-in commands refuse to budge. If it’s making you feel tied up in knots, cut through the clutter by adding new command groups to existing tabs or creating entirely new tabs. Here’s how to try your hand at menu design:

Open the Customization dialog box:

Windows: Choose File ⇒ Options ⇒ Customize Ribbon or right-click the ribbon and select Customize the Ribbon (opens the Excel Options dialog box).

macOS: Choose Excel ⇒ Preferences ⇒ Ribbon & Toolbar (opens the Ribbon & Toolbar dialog box).

Show or hide tabs by toggling the checkboxes in the Customize the Ribbon list.

Edit an existing tab:

Select a tab from the Customize the Ribbon list (defaults to Main Tabs). Select Tool Tabs or All Tabs to explore further.

Click New Group (+ then New Group on macOS) to add a custom group.

Rename it by clicking Rename (… then Rename on macOS).

Add commands by selecting them from the Choose Commands list and clicking Add >> (> on macOS). Use << Remove (< on macOS) to remove commands.

To create a new tab, click New Tab (+ then New Tab on macOS), then rename it and add groups and commands as needed.

Here's a peek at some of the tool tabs that may make special appearances — stepping on stage 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).

Analyze: This tab steps into the spotlight for PivotTables and PivotCharts (see Chapter 12), 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 5), a PivotTable, or click on a chart or PivotChart (all covered in Chapter 12). While Design may not always be in the name, similar variations appear for Sparklines, slicers, and Timelines (you guessed it, all in Chapter 12).

Format: This feature-specific tab appears for charts, PivotCharts, shapes, and images — because sometimes your data needs a little wardrobe upgrade.

Query: This tab enables you to edit, load, reuse, combine, and share data connections through Power Query — because why do the heavy lifting when you can make Excel do it for you? See the bonus chapter Automating Data Transformation with Power Query (available at www.dummies.com/go/excelfd).

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

Customizing the Quick Access Toolbar

The Quick Access Toolbar is like Excel's junk drawer — full of useful stuff, but only if you take the time to set it up. You can stash your favorite commands above or below the ribbon. By default, it includes AutoSave, Save, Undo, and Redo, which I cover later in the chapter. In Excel for Windows, tapping Alt turns your screen into an eye chart of alphanumeric shortcuts for every command on the toolbar and ribbon — macOS users must use their mouse. To make it your own:

Add a single command:

Windows: Right-click a ribbon command and select Add to Quick Access Toolbar

macOS: Unfortunately, you can't add commands on the fly, but you can use the Finetuning instructions

Finetuning:

Windows: Go to File ⇒ Options ⇒ Quick Access Toolbar to open the Excel Options dialog box.

macOS: Go to Excel ⇒ Preferences ⇒ Ribbon & Toolbar to open the Ribbon & Toolbar dialog box.

From there, you can shuffle icons, relocate the toolbar, and decide whether command labels should appear as ScreenTips.

Starting over (if your customizations spiral into chaos):

Windows: In the Excel Options dialog box, click Rest and choose whether to restore just the Quick Access Toolbar or reset all ribbon customizations.

macOS: Click … in the Ribbon & Toolbar dialog box then reset the toolbar or include the ribbon in your do-over.

Technical Stuff You already know that Ctrl is the go-to key for most keyboard shortcuts in Windows. Alt has some quirks, especially when numbers get involved. Tapping Alt lets you trigger Quick Access Toolbar shortcuts using either the number pad or the numbers row up top. However, hold down Alt while pressing a number on the number pad summons special characters instead — like Alt+7 giving you a bullet (•). Other numbers conjure up different symbols, which is either a handy trick or an unexpected detour, depending upon what you are trying to do.

One of my favorite areas to explore is Commands Not in the Ribbon list within the Choose Commands From list. This hidden trove contains hundreds of commands left off the ribbon — either due to space constraints or feature deprecation. Here are a few gems I often add to my Quick Access Toolbar:

AutoFilter: A one-click wonder that filters a normal range of cells based on whatever's in the selected cell. But don't get any wild ideas — this trick doesn't work within Excel tables (see Chapter 5).

Custom Views: Think of this as Excel's version of quick-change artist, letting you swap between saved worksheet and workbook layouts. I cover Custom Views in Chapter 11.

Full Screen: Clears away the ribbon, Formula Bar, and Status Bar so you can focus on your data — or just make Excel look extra dramatic. Press Escape to bring everything back.

Tip The Customize Quick Access Toolbar drop-down lets you decide whether to apply changes For All Documents (customizing the toolbar on this device) or for a specific workbook. Workbook-specific toolbars stick with the file, so they can follow you to other computers — like a loyal sidekick.

Activating worksheets

Chapter 11 takes a deep dive into navigating worksheets and workbooks, so I'll keep things brief here. Each document you create in Excel is called a workbook, which consists of one or more worksheets — each represented by a tab at the bottom of the worksheet grid. Think of worksheets as pages in a ledger, neatly (or not so neatly) organized within your workbook.

Here are a few ways that you can activate a worksheet tab:

Click the tab: Simple and straightforward.

Sheet navigation arrows: Found just above the Status Bar, these let you nudge the visible tabs to the left or right — provided you have more tabs than can fit comfortably on screen.

Activate dialog box: Right-click the sheet navigation arrows to summon the Activate dialog box (Figure 1-2). Pick a worksheet from the list and click OK — or double-click a sheet name to skip the extra step.

Remember The Activate dialog box only lists visible worksheets — so if a sheet is playing hide-and-seek, it won't show up there. To track down hidden worksheets, use the Navigation task pane (covered in Chapter 11). For more on making sheets disappear (or reappear), check out the upcoming Hiding or unhiding worksheets section.

FIGURE 1-2: The Activate dialog box.

Exploring the Status Bar

The Status Bar — Excel's command center that hides out in plain sight — keeps you in the loop with various feedback mechanisms and quick-access tools. Here's a rundown of the default options (as shown in Figure 1-3):

Cell Mode Indicator: Your cell's current mood, expressed in one word:

Ready: Just sitting there, minding its own business.

Enter: Actively receiving data or a formula.

Point: Waiting for you to pick cells for a formula, like a kid in a dodgeball lineup.

Edit (Windows Only): Mid-editing, hoping you don't regret your choices.

Crossreference Chapter 17 gets into the nitty-gritty of Enter, Point, and Edit modes.

AutoFilter Status: Tells you how many records match your filter criteria — so you're not left wondering why half your data mysteriously vanished. Filtering gets the full treatment in Chapter 4.

Calculate Status: Signals that some values may be outdated because Automatic Calculation is turned off or when Excel enters Manual Calculation mode due to a large data set. Clicking it or pressing F9 (Windows only) recalculates outdated formulas in the open workbook.

Technical Stuff Press Ctrl+Alt+F9 (Windows) or Cmd+Shift+= (macOS) to force a full recalculation of all formulas across all open workbooks. Windows users can also press Ctrl+Shift+Alt+F9 to rebuild the dependency tree — an internal structure that tracks relationships between formulas and the cells they reference — and then recalculate everything. Mac users can achieve a similar result by toggling Manual Calculation mode (via Excel ⇒ Preferences ⇒ Calculation) and then switch back to Automatic to force a full recalculation.

Remember Unlike most Status Bar features, the AutoFilter and Calculate settings cannot be toggled on or off — they appear only when relevant, like pop-up guests in your spreadsheet party.

Accessibility Assistant: Indicates if an accessibility issue has been detected — click the icon to open the Accessibility Checker (Chapter 3) where you can review and fix potential concerns so that your workbook is inclusive and easy to navigate for all users.

AutoCalculate Functions: Instantly crunches numbers when you select multiple cells, serving up average, count, numerical count, minimum, maximum, and sum — like a tiny data butler.

Tip Click any calculation in the Status Bar to copy the value to the clipboard, letting you paste it elsewhere like the Excel wizard you are.

View Modes: Located out on the right-hand side, these buttons let you switch between Normal (standard spreadsheet view), Page Layout (Excel's best guess at how your data will break across pages), and Page Break Preview (a true print preview, complete with headers and footers, so you can see exactly what will make it on the printed page).

Zoom Slider: Don't worry, this won't pull you into yet another online meeting. Located on the far right, this lets you zoom in until your data takes over the screen or zoom out until it vanishes into microscopic oblivion. In Windows, percentage zoom level appears next to it — click the number to open the Zoom dialog box for precise control.

FIGURE 1-3: The Status Bar.

You can customize the Status Bar by turning features on or off. Just right-click anywhere on it and toggle the options you want. For example, I often disable the Zoom Slider because I inevitably graze it while using the horizontal scroll bar — instantly throwing my worksheet into either billboard mode or microscopic territory, neither of which is particularly helpful. Other options you may wish to enable include:

Sheet Number: Displays where the current worksheet stands in the lineup — along with the total number of sheets in the workbook. It’s a handy way to confirm whether you’re on Sheet 3 of 27 or just hopelessly lost.

Workbook Statistics: Click to open a dialog box that provides a quick summary of key details of the workbook, including the number of sheets, cells with data, tables, charts, and more.

Caps Lock, Num Lock, Scroll Lock Indicators: Letting you know if you've purposefully engaged one of these settings — or if you're typing LIKE YOU'RE SHOUTING and wondering why.

Warning Scroll Lock is Excel for Windows's little gremlin, waiting for the perfect moment to ruin your day. When active, the arrow keys stop moving the selection and instead sends the whole worksheet gliding around like a greased-up air hockey puck. To disable it, press ScrLk again — if your keyboard even has one. If not, search for On-Screen Keyboard in Windows, and click ScrLk to wrestle back control.

Macro Recording: When enabled, a button appears that allows you to start or stop the Macro Recorder — because why not make Excel do the heavy lifting? More on that in Chapter 16.

Entering and Editing Data

If you're itching to create a spreadsheet from scratch, you're in luck — after touring the Excel interface, it's time to build a simple task tracker. This example introduces basic data entry, formatting, and formulas while providing a preview of features explored in more detail later in the book.

Starting a task tracker

Here's how to get started with building a task tracking spreadsheet:

Add the following column titles (referred to as headers in Excel) to a blank Excel worksheet — if you don’t already have one open, try closing and reopening Excel, or check the "Creating new workbooks" section later in this chapter:

Cell A1:Task

Cell B1:Due Date

Cell C1:Priority

Cell D1:Status

Cell E1:Time Spent (Hours)

Tip Column headers organize data, make the spreadsheet easier to read, and improve ease of use with certain Excel features, such as sorting lists (Chapter 4) and report writing with PivotTables (Chapter 12).

Enter the following example tasks (inspired by The Jersey Shore) into the cells below:

A2:Gym

A3:Tan

A4:Laundry

A5:Party

In column B, add due dates for each task:

Type 1/1 (the one day of the year that some folks actually go to the gym) in cell B2, then press Enter.

In unformatted cells, Excel converts entries in m/d or mm/dd format to d-mmm, with the hidden year defaulting to the current year. To display the full date in m/d/yyyy format, select Home ⇒ Number Format drop-down ⇒ Short Date.

Tip Applying the Short Date format in advance to cells allows you to save keystrokes by omitting the year for dates within the current year.

Type 04/01/2026 in cell B3, then press Enter.

Excel pranks you by dropping the leading zeroes — see Chapter 2 for how to display them if needed.

Overwrite the values in cells B2:B3 with yesterday's date and today's date, respectively, using the m/d format.

Excel adds the current year automatically since both cells have date formats applied.

Drag the Fill Handle from B2, shown in Figure1-4, to cell B5 to fill the series of dates.

Tip To create a series of month-end dates, enter two consecutive month-end dates in adjacent cells, select both, and then drag the Fill Handle down. Excel will recognize the pattern and extend it accordingly. This works for any data where Excel can identify a logical sequence.

Use column C to track priority:

C2:High

C3:Medium

C4:Low

C5:High

Tip As you may have noticed in cell D5, Excel can autofill entries based on similar entries within the current column of the current region ⇒ simply press Enter when the desired entry appears after typing matching characters.

Use column D to track status:

D2:Completed

D3:In Progress

D4:Pending

D5:Never Ending

Tip Chapter 14 covers creating in-cell drop-down lists with the Data Validation feature, streamlining data entry and ensuring consistent inputs.

Use column E to track hours:

E2:2

E3:1.5

E4:3

E5:4

FIGURE 1-4: Use the Fill Handle.

Applying basic formatting

A common next step is to dress up data:

Select the header cells (a necessary step before applying formatting):

Mouse action: Click once on cell A1, hold down the left mouse button, and drag across to cell E1.

Keyboard action: Use the Up Arrow key to return to cell A1, hold down Shift, and the Right Arrow to select across to cell E1.

Tip To select the entire contiguous block of cells that surround the active cell, press Ctrl+A (Windows) or Cmd+A (macOS) to select the current region. If you press the shortcut again, Excel selects the entire worksheet.

Apply basic formatting (seeChapter2for more advanced formatting options):

9781394317103-ma025 Bold: Choose Home ⇒ Bold or press Ctrl+B (Windows) or Cmd+B (macOS).

9781394317103-ma027 Underline: Choose Home ⇒ Underline or press Ctrl+U (Windows) or Cmd+U (macOS).

Tip Some commands, such as Underline, include a drop-down button that provides additional functionality, such as the Double Underline command.

9781394317103-ma039 Center: Choose Home ⇒ Center to align the text in the center of each cell. While there's no built-in shortcut, see the "Customizing the Quick Access Toolbar" section earlier in this chapter to create your own.

9781394317103-ma041 Many formatting commands function as toggles, such as Bold or Underline, while others require selecting an alternative option, such as Left Align when choosing not to center text in a cell.

9781394317103-ma040 Select cell E1, then choose Home ⇒ Wrap Text.

Excel automatically adjusts the row height to ensure all data within the cell is visible.

Technical Stuff Line breaks for wrapped text are based on the column width. To insert a manual line break, press Alt+Enter (Windows) or Option+Return (macOS).

To format the hours with two decimal places, select cells E2:E5, and then choose Home ⇒ Comma Style.

9781394317103-ma042 If necessary, adjust the decimal places by clicking Home ⇒ Increase Decimal or Home ⇒ Decrease Decimal.

To adjust the width of column D, click the letter D at the top of the worksheet to select the entire column, and then go to Home, and then choose Home ⇒ Format ⇒ AutoFit Column Width.

Remember Sometimes Excel misinterprets your intent. For example, selecting cell D4 and choosing Home ⇒ Format ⇒ AutoFit Column Width reduces the column width to fit the seven characters in Pending, instead of displaying Never Ending in full, as you might have expected. To avoid this, select the entire column or the relevant range first, or double-click the column's right edge when the double-headed arrow appears to auto-fit to the widest cell contents. See Chapter 3 for more on adjusting column widths.

Leveraging worksheet functions

Worksheet functions are Excel's built-in magic tricks — prepackaged formulas that crunch numbers, manipulate data, and make