Excel 2013 All-in-One For Dummies E-Book

Greg Harvey

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2013

Seitenzahl: 1248

Beschreibung

The comprehensive reference, now completely up-to-date for Excel 2013! As the standard for spreadsheet applications, Excel is used worldwide - but it's not always user-friendly. However, in the hands of veteran bestselling author Greg Harvey, Excel gets a whole lot easier to understand! This handy all-in-one guide covers all the essentials, the new features, how to analyze data with Excel, and much more. The featured minibooks address Excel basics, worksheet design, formulas and functions, worksheet collaboration and review, charts and graphics, data management, data analysis, and Excel and VBA. * Covers the changes in the newest version as well as familiar tasks, such as creating and editing worksheets, setting up formulas, and performing statistical functions * Walks you through the new analysis tools that help make it easier to visualize data with the click of a mouse * Details new ways to explore your data more intuitively and then analyze and display your results with a single click Whether you're an Excel newbie or a veteran user to wants to get familiar with the latest version, Excel 2013 All-in-One For Dummies has everything you need to know.

Leseprobe

Excel®2013 All-in-One For Dummies®

Published byJohn Wiley & Sons, Inc.111 River StreetHoboken, NJ 07030-5774

www.wiley.com

Copyright © 2013 by John Wiley & Sons, Inc., Hoboken, New Jersey

Published by John Wiley & Sons, Inc., Hoboken, New Jersey

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2012956361

ISBN 978-1-118-51010-0 (pbk); ISBN 978-1-118-55018-2 (ebk); ISBN 978-1-118-62025-0 (ebk); ISBN 978-1-118-55021-2 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

About the Author

Greg Harvey has authored tons of computer books, the most recent being Excel 2013 For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough-and-tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, he went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.

His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience, the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.

Dedication

To all the students in my different computer classes who taught me so much about what’s really important and what’s not when it comes to using computer software.

Author’s Acknowledgments

I am always so grateful to the many people who work so hard to bring my book projects into being, and this one is no exception. If anything, I am even more thankful for their talents, given the size and complexity of an All-in-One.

This time, special thanks are in order to Andy Cummings and Katie Feltman for giving me this opportunity to write (and write and write) about Excel in this great All-in-One format. Next, I want to express great thanks to my project editor, Kim Darosett. Thanks also go to Russ Mullen for the great technical edit, to Sheree Montgomery for coordinating the book’s production, and to everybody at Wiley Publishing.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

Some of the people who helped bring this book to market include the following:

Acquisitions and Editorial

Senior Project Editor: Kim Darosett

Acquisitions Editor: Katie Feltman

Copy Editor: Virginia Sanders

Technical Editor: Russ Mullen

Senior Editorial Manager: Leah Michael

Editorial Assistant: Anne Sullivan

Senior Editorial Assistant: Cherie Case

Cover Photo: © iStockphoto.com / hakan dogu

Composition Services

Project Coordinator: Sheree Montgomery

Layout and Graphics: Carl Byers, Jennifer Creasey, Joyce Haughey

Proofreaders: Melissa D. Buddendeck, John Greenough

Indexer: BIM Indexing & Proofreading Services

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Kathleen Nebenhaus, Vice President and Executive Publisher

Composition Services

Debbie Stailey, Director of Composition Services

Excel® 2013 All-in-One For Dummies®

Visit www.dummies.com/cheatsheet/excel2013aio to view this book's cheat sheet.

Table of Contents

Introduction

About This Book

Foolish Assumptions

How This Book Is Organized

Book I: Excel Basics

Book II: Worksheet Design

Book III: Formulas and Functions

Book IV: Worksheet Collaboration and Review

Book V: Charts and Graphics

Book VI: Data Management

Book VII: Data Analysis

Book VIII: Macros and VBA

Conventions Used in This Book

Icons Used in This Book

Where to Go from Here

Book I: Excel Basics

Chapter 1: The Excel 2013 User Experience

Excel 2013’s New Look and Feel

Excel’s Start Screen

Excel’s Ribbon User Interface

Going behind the scenes to Excel’s Backstage view

Ripping through the Ribbon

Adjusting to the Quick Access toolbar

Fooling around with the Formula bar

What’s up with the Worksheet area?

Taking a tour of the Status bar

Getting Help

Launching and Quitting Excel

Starting Excel from the Windows 8 Start screen

Starting Excel from the Windows 7 Start menu

When it’s quitting time

Chapter 2: Customizing Excel 2013

Tailoring the Quick Access Toolbar to Your Tastes

Adding Ribbon commands to the Quick Access toolbar

Adding non-Ribbon commands to the Quick Access toolbar

Adding macros to the Quick Access toolbar

Exercising Your Options

Changing some of the more universal settings on the General tab

Changing common calculation options on the Formulas tab

Changing correction options on the Proofing tab

Changing various save options on the Save tab

Changing a whole lot of other common options on the Advanced tab

Customizing the Excel 2013 Ribbon

Using Office Apps

Add-In Mania

Managing Excel add-ins

Managing COM add-ins

Purchasing third-party add-ins

Book II: Worksheet Design

Chapter 1: Building Worksheets

Designer Spreadsheets

Take it from a template

Designing a workbook from scratch

It Takes All Kinds (Of Cell Entries)

What’s in a label?

What’s the value?

Data Entry 101

Data entry keyboard style

Doing data entry with the Touch keyboard

You AutoComplete this for me

You AutoCorrect this right now!

Constraining data entry to a cell range

Getting Excel to put in the decimal point

You AutoFill it in

Saving the Data

Saving workbooks in other commonly used file formats

Changing the default file location

Saving a new workbook in the old file format

Document Recovery to the Rescue

Chapter 2: Formatting Worksheets

Making Cell Selections

Selecting cells with the mouse

Selecting cells by touch

Selecting cells with the keyboard

You AutoSelect that range!

Selecting cells with Go To

Name that range!

Adjusting Columns and Rows

You AutoFit the column to its contents

Adjusting columns the old fashioned way

Setting a new standard width

Hiding out a column or two

Rambling rows

Formatting Tables from the Ribbon

Formatting Tables with the Quick Analysis Tool

Formatting Cells from the Ribbon

Formatting Cell Ranges with the Mini-Toolbar

Using the Format Cells Dialog Box

Assigning number formats

Altering the alignment

Fancy fonts and colors

Basic borders, fills, and patterns

Hiring Out the Format Painter

Using Cell Styles

Using the Number Format cell styles

Defining a custom cell style by example

Creating a new cell style from scratch

Merging styles into other workbooks

Conditional Formatting

Graphical conditional formatting

Formatting with the Quick Analysis tool

Identifying particular values or text entries in a cell range

Highlighting duplicate values in a cell range

Creating your own conditional formatting rules

Managing conditional formatting rules

Chapter 3: Editing and Proofing Worksheets

Opening a Workbook

Using the Open screen in the Backstage view

Using the Open dialog box

Opening more than one workbook at a time

Finding misplaced workbooks

Using the other Open options

Cell Editing 101

Undo and Redo

Get that out of here!

Can I just squeeze this in here?

A Spreadsheet with a View

“Zoom, zoom, zoom”

Freezing window panes

Saving custom views

Copying and Moving Stuff Around

Doing it with drag-and-drop

Carried away with cut-and-paste

Find and Replace This Disgrace!

Finding stuff

Finding and replacing stuff

Spell Checking Heaven

Changing the spelling options

Adding words to the custom dictionary

Looking Up and Translating Stuff

Marking Invalid Data

Eliminating Errors with Text to Speech

Chapter 4: Managing Worksheets

Reorganizing the Worksheet

Inserting and deleting columns and rows

Eradicating columns and rows

Adding new columns and rows

Splitting the worksheet into panes

Outlining worksheets

Reorganizing the Workbook

Renaming sheets

Designer sheets

Adding and deleting sheets

Changing the sheets

Group editing

“Now you see them; now you don’t”

Opening windows on different sheets

Working with Multiple Workbooks

Comparing windows on different workbooks

Transferring data between open windows

Transferring sheets from one workbook to another

Saving a workspace

Consolidating Worksheets

Consolidating by position

Consolidating by category

Linking consolidated data

Chapter 5: Printing Worksheets

Printing from the Excel 2013 Backstage View

Selecting the printer to use

Previewing the printout

Checking the paging in Page Layout view

Previewing the pages of the report

Quick Printing the Worksheet

Working with the Page Setup Options

Using the buttons in the Page Setup group

Using the buttons in the Scale to Fit group

Using the Print buttons in the Sheet Options group

Headers and Footers

Adding a ready-made header or footer

Creating a custom header or footer

Solving Page Break Problems

Printing the Formulas in a Report

Book III: Formulas and Functions

Chapter 1: Building Basic Formulas

Formulas 101

Formula building methods

Editing formulas

When you AutoSum numbers in a spreadsheet

Totals and sums with the Quick Analysis tool

Building formulas with operators

Using the Insert Function button

Copying Formulas

Absolute references

A mixed bag of references

Adding Array Formulas

Building an array formula

Editing an array formula

Range Names in Formulas

Defining range names

Naming constants and formulas

Using names in building formulas

Creating names from column and row headings

Managing range names

Applying names to existing formulas

Adding Linking Formulas

Controlling Formula Recalculation

Circular References

Chapter 2: Logical Functions and Error Trapping

Understanding Error Values

Using Logical Functions

Error-Trapping Formulas

Whiting-Out Errors with Conditional Formatting

Formula Auditing

Tracing precedents

Tracing dependents

Error checking

Changing the Error Checking options

Error tracing

Evaluating a formula

Removing Errors from the Printout

Chapter 3: Date and Time Formulas

Understanding Dates and Times

Changing the Regional date settings

Building formulas that calculate elapsed dates

Building formulas that calculate elapsed times

Using Date Functions

TODAY

DATE and DATEVALUE

DAY, WEEKDAY, MONTH, and YEAR

DAYS360

Other special Date functions

Using Time Functions

NOW

TIME and TIMEVALUE

HOUR, MINUTE, and SECOND

Chapter 4: Financial Formulas

Financial Functions 101

The PV, NPV, and FV Functions

Calculating the Present Value

Calculating the Net Present Value

Calculating the Future Value

The PMT Function

Depreciation Functions

Analysis ToolPak Financial Functions

Chapter 5: Math and Statistical Formulas

Math & Trig Functions

Rounding off numbers

POWER and SQRT

The SUM of the parts

Conditional summing

Statistical Functions

AVERAGE, MAX, and MIN

Counting cells

Using specialized statistical functions

Chapter 6: Lookup, Information, and Text Formulas

Lookup and Reference

Looking up a single value with VLOOKUP and HLOOKUP

Performing a two-way lookup

Reference functions

Information, Please . . .

Getting specific information about a cell

Are you my type?

Using the IS functions

Much Ado about Text

Using text functions

Concatenating text

Book IV: Worksheet Collaboration and Review

Chapter 1: Protecting Workbooks and Worksheet Data

Password-Protecting the File

Protecting the workbook when saving the file

Assigning a password to open from the Info screen

Entering the password to gain access

Entering the password to make changes

Changing or deleting a password

Protecting the Spreadsheet

Changing the Locked and Hidden cell formatting

Protecting the worksheet

Enabling cell range editing by certain users

Doing data entry in the unlocked cells of a protected worksheet

Protecting the workbook

Protecting a shared workbook

Chapter 2: Using Hyperlinks

Hyperlinks 101

Adding hyperlinks

Follow that link!

Editing hyperlinks

Using the HYPERLINK Function

Chapter 3: Sending Workbooks Out for Review

Preparing a Workbook for Distribution

Adding properties to a workbook

Digitally signing a document

Workbook Sharing 101

Turning on file sharing

Modifying the Share Workbook options

Turning on change tracking

Merging changes from different users

Workbooks on Review

Adding comments

Marking up a worksheet with digital ink

Chapter 4: Sharing Workbooks and Worksheet Data

Sharing Your Workbooks Online

Sharing workbooks saved on your SkyDrive

E-mailing workbooks

Sharing workbooks with Instant Message

Presenting worksheets online

Editing worksheets online

Reviewing workbooks online

Excel 2013 Data Sharing Basics

Excel and Word 2013

Excel and PowerPoint 2013

Exporting Workbooks to Other Usable File Formats

Saving and exporting worksheets as PDF files

Saving worksheets as XPS files

Saving worksheets as HTML files

Book V: Charts and Graphics

Chapter 1: Charting Worksheet Data

Worksheet Charting 101

Embedded charts versus charts on separate chart sheets

Inserting recommended charts

Inserting specific chart types from the Ribbon

Inserting charts with the Quick Analysis tool

Creating a chart on a separate chart sheet

Refining the chart from the Design tab

Customizing chart elements from the Format tab

Customizing the elements of a chart

Formatting elements of a chart

Saving a customized chart as a template

Adding Sparkline Graphics to a Worksheet

Printing Charts

Chapter 2: Adding Graphic Objects

Graphic Objects 101

Manipulating graphics

Moving graphic objects to new layers

Aligning graphic objects

Grouping graphic objects

Managing graphic objects in the Selection task pane

Inserting Different Types of Graphics

Adding clip art

Downloading images on the Web

Inserting local pictures

Editing pictures

Formatting pictures

Drawing Graphics

Drawing predefined shapes

Adding text boxes

Inserting WordArt

Inserting SmartArt graphics

Adding Screenshots of the Windows Desktop

Using Themes

Book VI: Data Management

Chapter 1: Building and Maintaining Data Lists

Data List Basics

Designing the basic data list

Add new records to a data list

Eliminating records with duplicate fields

Sorting Data

Sorting records on a single field

Sorting records on multiple fields

Sorting the columns of a data list

Sorting a data list on font and fill colors and cell icons

Subtotaling Data

Chapter 2: Filtering and Querying a Data List

Data List Filtering 101

Filtering Data

Using AutoFilter

Using the Advanced Filter

Using the Database Functions

External Data Query

Retrieving data from Access database tables

Retrieving data from the web

Retrieving data from text files

Querying data from other data sources

Retrieving external data with Microsoft Query

Book VII: Data Analysis

Chapter 1: Performing What-If Scenarios

Using Data Tables

Creating a one-variable data table

Creating a two-variable data table

Exploring Different Scenarios

Creating new scenarios

Producing a summary report

Hide and Goal Seeking

Using the Solver

Setting up and defining the problem

Solving the problem

Changing the Solver options

Saving and loading a model problem

Creating Solver reports

Chapter 2: Generating Pivot Tables

Creating Pivot Tables

Pivot tables with the Quick Analysis tool

Recommended pivot tables

Manually created pivot tables

Formatting a Pivot Table

Refining the pivot table layout and style

Formatting the parts of the pivot table

Sorting and Filtering the Pivot Table Data

Filtering the report

Filtering individual Column and Row fields

Slicing the pivot table data

Using timeline filters

Sorting the pivot table

Modifying the Pivot Table

Changing the summary functions

Adding Calculated Fields

Changing the pivot table options

Creating Pivot Charts

Moving a pivot chart to its own sheet

Filtering a pivot chart

Formatting a pivot chart

Using the PowerPivot and Power View Add-Ins

Data modeling with PowerPivot

Switching between the Data View and Diagram View

Adding calculated columns courtesy of DAX

Creating visual reports with Power View

Book VIII: Macros and VBA

Chapter 1: Building and Running Macros

Macro Basics

Recording macros

Running a macro

Macro Security

Assigning Macros to the Ribbon and the Quick Access Toolbar

Adding your macros to a custom tab on the Ribbon

Adding your macros to custom buttons on the Quick Access toolbar

Chapter 2: VBA Programming

Using the Visual Basic Editor

Editing recorded macros

Writing new macros in the Visual Basic Editor

Creating Custom Excel Functions

Adding a description to a user-defined function

Using a custom function in your spreadsheet

Saving custom functions in add-in files

Cheat Sheet

Introduction

Excel 2013 All-in-One For Dummies brings together plain and simple information on using all aspects of the latest-and-greatest version of Microsoft Excel. It’s designed to be of help no matter how much or how little experience you have with the program. As the preeminent spreadsheet and data analysis software for all sorts of computing devices running Windows 7 or 8 (desktops, laptops, tablet PCs and even smartphones), Excel 2013 offers its users seemingly unlimited capabilities too often masked in technical jargon and obscured by explanations only a software engineer could love. On top of that, many of the publications that purport to give you the lowdown on using Excel are quite clear on how to use particular features without giving you a clue as to why you would want to go to all the trouble.

The truth is that understanding how to use the abundance of features offered by Excel 2013 is only half the battle, at best. The other half of the battle is to understand how these features can benefit you in your work; in other words, “what’s in it for you.” I have endeavored to cover both the “how to” and “so what” aspects in all my discussions of Excel features, being as clear as possible and using as little tech-speak as possible.

Fortunately, Excel 2013 is well worth the effort to get to know because it’s definitely one of the best data-processing and analysis tools that has ever come along. Its new Quick Analysis tool, Apps for Office, Flash Fill, and Recommended Charts and PivotTables, along with the tried-and-true Live Preview feature and tons of ready-made galleries, make this version of the program the easiest to use ever. In short, Excel 2013 is a blast to use when you know what you’re doing, and my great hope is that this “fun” aspect of using the program comes through on every page (or, at least, every other page).

About This Book

As the name states, Excel 2013 All-in-One For Dummies is a reference. (Whether you keep it on your desk or use it to prop up your desk is your business.) This means that although the chapters in each book are laid out in a logical order, each stands on its own ready for you to dig into the information at any point.

As much as possible, I have endeavored to make the topics within each book and chapter stand on their own. When there’s just no way around relying on some information that’s discussed elsewhere, I include a cross-reference that gives you the chapter and verse (actually the book and chapter) for where you can find that related information if you’re of a mind to.

Use the full Table of Contents and Index to look up the topic of the hour and find out exactly where it is in this compilation of Excel information. You’ll find that although most topics are introduced in a conversational manner, I don’t waste much time cutting to the chase by laying down the main principles at work (usually in bulleted form) followed by the hard reality of how you do the deed (as numbered steps).

Foolish Assumptions

I’m only going to make one foolish assumption about you, and that is that you have some need to use Microsoft Excel 2013 in your work or studies. If pushed, I further guess that you aren’t particularly interested in knowing Excel at an expert level but are terribly motivated to find out how to do the stuff you need to get done. If that’s the case, this is definitely the book for you. Fortunately, even if you happen to be one of those newcomers who’s highly motivated to become the company’s resident spreadsheet guru, you’ve still come to the right place.

As far as your hardware and software go, I’m assuming that you already have Excel 2013 (usually as part of Microsoft Office 2013) installed on your computing device, using a standard home or business installation running under either Windows 7 or 8. I’m not assuming, however, that when you’re using Excel 2013 under Windows 7 or 8 that you’re sitting in front of a large screen monitor and making cell entries and command selections with a physical keyboard or connected mouse. With the introduction of Microsoft’s Surface tablet for Windows 8 and the support for a whole slew of different Windows tablets, you may well be entering data and selecting commands with your finger or stylus using the Windows Touch keyboard and Touch pointer.

To deal with the differences between using Excel 2013 on a standard desktop or laptop computer with access only to a physical keyboard and mouse and a touchscreen tablet or smartphone environment with access only to the virtual Touch keyboard and Touch pointer, I’ve outlined the touchscreen equivalents to common commands you find throughout the text such as “click,” “double-click,” “drag,” and so forth in the section that explains selecting by touch in Book I, Chapter 1.

Keep in mind that although most of the figures in this book show Excel 2013 happily running on Windows 7, you will see the occasional figure showing Excel running on Windows 8 in the rare cases (as when opening and saving files) where what operating system you’re using does make a difference.

This book is intended only for users of Microsoft Office Excel 2013! Because of the diversity of the devices that Excel 2013 runs on and the places where its files can be saved and used, if you’re using Excel 2007 or Excel 2010 for Windows, much of the file-related information in this book may only confuse and confound you. If you’re still using a version prior to Excel 2007, which introduced the Ribbon interface, this edition will be of no use to you because your version of the program works nothing like the 2013 version this book describes.

How This Book Is Organized

Excel 2013 All-in-One For Dummies is actually eight smaller books rolled into one. That way, you can go after the stuff in the particular book that really interests you at the time, putting all the rest of the material aside until you need to have a look at it. Each book in the volume consists of two or more chapters consisting of all the basic information you should need in dealing with that particular component or aspect of Excel.

In case you’re the least bit curious, here’s the lowdown on each of the eight books and what you can expect to find there.

Book I: Excel Basics

This book is for those of you who’ve never had a formal introduction to the program’s basic workings. Chapter 1 covers all the orientation material including how to deal with the program’s Ribbon user interface. Of special interest may be the section selecting commands by touch if you’re using Excel 2013 on a Windows touchscreen device that isn’t equipped with either a physical keyboard or mouse.

Chapter 2 is not to be missed, even if you do not consider yourself a beginner by any stretch of the imagination. This chapter covers the many ways to customize Excel and make the program truly your own. It includes information on customizing the Quick Access toolbar as well as great information on how to use and procure add-in programs that can greatly extend Excel’s considerable features.

Book II: Worksheet Design

Book II focuses on the crucial issue of designing worksheets in Excel. Chapter 1 takes up the call on how to do basic design and covers all the many ways of doing data entry (a subject that’s been made all the more exciting with the addition of voice and handwriting input).

Chapter 2 covers how to make your spreadsheet look professional and read the way you want it through formatting. Excel offers you a wide choice of formatting techniques, from the very simple formatting as a table all the way to the now very sophisticated and super-easy conditional formatting.

Chapter 3 takes up the vital subject of how to edit an existing spreadsheet without disturbing its design or contents. Editing can be intimidating to the new spreadsheet user because most spreadsheets contain not only data entries that you don’t want to mess up but also formulas that can go haywire if you make the wrong move.

Chapter 4 looks at the topic of managing the worksheets that contain the spreadsheet applications that you build in Excel. It opens the possibility of going beyond the two-dimensional worksheet with its innumerable columns and rows by organizing data three-dimensionally through the use of multiple worksheets. (Each Excel file already contains three blank worksheets to which you can add more.) This chapter also shows you how to work with and organize multiple worksheets given the limited screen real estate afforded by your monitor and how to combine data from different files and sheets when needed.

Chapter 5 is all about printing your spreadsheets, a topic that ranks only second in importance to knowing how to get the data into a worksheet in the first place. As you expect, you find out not only how to get the raw data to spit out of your printer but also how to gussy it up and make it into a professional report of which anyone would be proud.

Book III: Formulas and Functions

This book is all about calculations and building the formulas that do them. Chapter 1 covers formula basics from doing the simplest addition to building array formulas and using Excel’s built-in functions courtesy of the Function Wizard. It also covers how to use different types of cell references when making formula copies and how to link formulas that span different ­worksheets.

Chapter 2 takes up the subject of preventing formula errors from occurring and, barring that, how to track them down and eliminate them from the spreadsheet. This chapter also includes information on circular references in formulas and how you can sometimes use them to your advantage.

Chapters 3 through 6 concentrate on how to use different types of built-in functions. Chapter 3 covers the use of date and time functions, not only so you know what day and time it is, but actually put this knowledge to good use in formulas that calculate elapsed time. Chapter 4 takes up the financial functions in Excel and shows you how you can use them to both reveal and determine the monetary health of your business. Chapter 5 is concerned with math and statistical functions (of which there are plenty). Chapter 6 introduces you to the powerful group of lookup, information, and text functions. Here, you find out how to build formulas that automate data entry by returning values from a lookup table, get the lowdown on any cell in the worksheet, and combine your favorite pieces of text.

Book IV: Worksheet Collaboration and Review

Book IV looks at the ways you can share your spreadsheet data with others. Chapter 1 covers the important issue of security in your spreadsheets. Here, you find out how you can protect your data so that only those to whom you give permission can open or make changes to their contents.

Chapter 2 takes up the subject of building and using hyperlinks in your Excel spreadsheets (the same kind of links that you know and love on web pages on the World Wide Web). This chapter covers how to create hyperlinks for moving from worksheet to worksheet within the same Excel file as well as for opening other documents on your hard drive, or connecting to the Internet and browsing to a favorite web page.

Chapter 3 introduces Excel’s sophisticated features for sending out spreadsheets and having a team of people review and make comments on them. It also covers techniques for reviewing and reconciling the suggested changes.

Chapter 4 is concerned with sharing spreadsheet data with other programs that you use. It looks specifically at how you can share data with other Office 2013 programs such as Microsoft Word, PowerPoint, and Outlook. This chapter also discusses the variety of ways to share your workbooks files, all the way from inviting people to review or even edit them from your SkyDrive, attaching them to e-mail and instant messages, presenting them in online meetings, to publishing them on your social network pages such as Facebook, LinkedIn, Twitter, and the like.

Book V: Charts and Graphics

Book V focuses on the graphical aspects of Excel. Chapter 1 covers charting your spreadsheet data in some depth. Here, you find out not only how to create great-looking charts but also how to select the right type of chart for the data that you’re representing graphically.

Chapter 2 introduces you to all the other kinds of graphics that you can have in your spreadsheets. These include graphic objects that you draw as well as graphic images that you import, including clip art included in Microsoft Office, as well as digital pictures and images imported and created with other hardware and software connected to your computer.

Book VI: Data Management

Book VI is concerned with the ins and outs of using Excel to maintain large amounts of data in what are known as databases or, more commonly, data lists. Chapter 1 gives you basic information on how to set up a data list and add your data to it. This chapter also gives you information on how to reorganize the data list through sorting and how to total its numerical data with the Subtotal feature.

Chapter 2 is all about how to filter the data and extract just the information you want out of it (a process officially known as querying the data). Here, you find out how to perform all sorts of filtering operations from the simplest, which involves relying upon the AutoFilter feature, to the more complex operations that use custom filters and specialized database functions. Finally, you find out how to perform queries on external data sources such as those maintained with dedicated database management software for Windows such as Microsoft Access or dBASE as well as those that run on other operating systems such as DB2 and Oracle.

Book VII: Data Analysis

Book VII looks at the subject of data analysis with Excel; essentially how to use the program’s computational capabilities to project and predict possible future outcomes. Chapter 1 looks at the various ways to perform what-if scenarios in Excel. These include analyses with one- and two-input variable data tables, doing goal seeking, setting a series of different possible scenarios, and using the Solver add-in.

Chapter 2 is concerned with the topic of creating special data summaries called pivot tables that enable you to analyze large amounts of data in an extremely compact and modifiable format. Here, you find out how to create and manipulate pivot tables as well as build pivot charts that depict the summary information graphically. In addition, you’ll get an introduction to using the PowerPivot for Excel 2013 and Power View add-ins to perform more sophisticated types of data analysis on the Data Model that’s represented in your Excel pivot table.

Book VIII: Macros and VBA

Book VIII introduces the subject of customizing Excel through the use of its programming language called Visual Basic for Applications (VBA for short). Chapter 1 introduces you to the use of the macro recorder to record tasks that you routinely perform in Excel for later automated playback. When you use the macro recorder to record the sequence of routine actions (using the program’s familiar menus, toolbars, and dialog boxes), Excel automatically records the sequence in the VBA programming language.

Chapter 2 introduces you to editing VBA code in Excel’s programming editor known as the Visual Basic Editor. Here, you find out how to use the Visual Basic Editor to edit macros that you’ve recorded that need slight modifications as well as how to write new macros from scratch. You also find out how to use the Visual Basic Editor to write custom functions that perform just the calculations you need in your Excel spreadsheets.

Conventions Used in This Book

This book follows a number of different conventions modeled primarily after those used by Microsoft in its various online articles and help materials. These conventions deal primarily with Ribbon command sequences and shortcut or hot key sequences that you encounter.

Excel 2013 is a sophisticated program that uses the Ribbon interface first introduced in Excel 2007. In Chapter 1, I explain all about this Ribbon interface and how to get comfortable with its command structure. Throughout the book, you may find Ribbon command sequences using the shorthand developed by Microsoft whereby the name on the tab on the Ribbon and the command button you select are separated by arrows, as in

Home⇒Copy

This is shorthand for the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the Home tab on the Ribbon (if it’s not already displayed) and then click the Copy button (that sports the traditional side-by-side page icon).

Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by vertical bars, as in

Formulas⇒CalculationOptions⇒Manual

This is shorthand for the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it’s not already displayed) and then click the Calculation Options command button followed by the Manual drop-down menu option.

The book occasionally encourages you to type something specific into a specific cell in the worksheet. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, still have to press the Enter key or click the Enter button on the Formula bar to make the entry stick.

When Excel isn’t talking to you by popping up message boxes, it displays highly informative messages in the status bar at the bottom of the screen. This book renders messages that you see onscreen like this:

CALCULATE

This is the message that tells you that Excel is in manual recalculation mode (after using the earlier Ribbon command sequence) and that one or more of the formulas in your worksheet are not up to date and are in sore need of recalculation.

Occasionally I give you a hot key combination that you can press in order to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Hot key combinations are written like this: Alt+FS or Ctrl+S. (Both of these hot key combos save workbook changes.)

With the Alt key combos, you press the Alt key until the hot key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start typing the hot key letters. (By the way, you type all ­lowercase hot key letters — I only put them in caps to make them stand out in the text.)

Hot key combos that use the Ctrl key are of an older vintage, and they work a little bit differently because, on a physical keyboard, you have to hold down the Ctrl key as you type the hot key letter. (Again, type only lowercase letters unless you see the Shift key in the sequence as in Ctrl+Shift+C.)

Finally, if you’re really observant, you may notice a discrepancy between the capitalization of the names of dialog box options (such as headings, option buttons, and check boxes) as they appear in the book and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.

Icons Used in This Book

The following icons are strategically placed in the margins throughout all eight books in this volume. Their purpose is to get your attention, and each has its own way of doing that.

This icon denotes some really cool information (in my humble opinion) that will pay off by making your work a lot more enjoyable or productive (or both).

This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you may end up taking a detour that wastes valuable time.

This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you’ll be sorry. I reserve this icon for those times when you can lose data and otherwise screw up your spreadsheet.

This icon denotes a tidbit only for Excel users who are running Excel 2013 on some sort of touchscreen device such as a Windows tablet or smartphone.

This icon denotes a tidbit that makes free use of (oh no!) technical jargon. You may want to skip these sections (or, at least, read them when no one else is around).

Where to Go from Here

The question of where to go from here couldn’t be simpler, go to Chapter 1 and find out what you’re dealing with. Which book you go to after that is a matter of personal interest and need. Just go for the gold and don’t forget to have some fun while you’re digging!

Occasionally, Wiley’s technology books are updated. If this book has technical updates, they’ll be posted at www.dummies.com/go/excel2013aioupdates.

Book I

Excel Basics

Visit www.dummies.com for great Dummies content online.

Chapter 1: The Excel 2013 User Experience

Chapter 2: Customizing Excel 2013

Chapter 1: The Excel 2013 User Experience

In This Chapter

Getting to know Excel 2013’s Start screen and program window

Selecting commands from the Ribbon

Unpinning the Ribbon

Using Excel 2013 on a touchscreen device

Getting around the worksheet and workbook

Launching and quitting Excel

Excel 2013 relies primarily on the onscreen element called the Ribbon, which is the means by which you select the vast majority of Excel commands. In addition, Excel 2013 sports a single toolbar (the Quick Access toolbar), some context-sensitive buttons and command bars in the form of the Quick Analysis tool and mini-bar, along with a number of task panes (such as Clipboard, Research, Thesaurus, and Selection to name a few).

Among the features supported when selecting certain style and formatting commands is the Live Preview, which shows you how your actual worksheet data will appear in a particular font, table formatting, and so on before you actually apply it. Excel also supports an honest-to-goodness Page Layout view that displays rulers and margins along with headers and footers for every worksheet. Page Layout view has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. The Backstage view attached to the File tab on the Excel Ribbon enables you to get at-a-glance information about your spreadsheet files as well as save, share, preview, and print them. Last but not least, Excel 2013 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially with the program’s Live Preview.

Excel 2013’s New Look and Feel

If you’ve been using earlier versions of Excel (especially Excel 2007 or Excel 2010), the first thing you notice about the Excel 2013 user interface is its rather flat (as though you’ve gone from 3-D to 2-D) and decidedly less colorful display. Gone entirely are the contoured command buttons and color-filled Ribbon and pull-down menu graphics along with any hint of the gradients and shading so prevalent in the earlier versions. The Excel 2013 screen is so stark that even its worksheet column and row borders lack any color, and the shading is reserved for only the columns and rows that are currently selected in the worksheet itself.

This new look and feel for Excel 2013 (indeed, all the Office 2013 apps) is all part of the Windows 8 user experience. This latest version of the Windows operating system was developed primarily with tablets and smartphones in mind, devices where touch often is the means of selecting and manipulating screen objects. With an eye toward making this touch experience as satisfying as possible, Microsoft redesigned the interface of both its new operating system and Office 2013 application programs: It attempted to reduce the graphical complexity of many screen elements as well as make them as responsive as possible on touchscreen devices.

The result is a snappy Excel 2013, regardless of what kind of hardware you run it on. And the new, somewhat plainer and definitely flatter look, while adding to Excel 2013’s robustness on any device, takes nothing away from the program’s functionality.

The greatest thing about the new look of Office 2013 is that each of its application programs features a different predominant color. Excel 2013 features a green color long associated with the program. Green appears throughout the program’s colored screen elements, including the Excel program and file icon, the status bar, the outline of the cell pointer, the shading of highlighted and selected Ribbon tabs, and menu items. This is in stark contrast to the last few versions of Excel where the screen elements were all predominately blue, the color traditionally associated with Microsoft Word.

Excel’s Start Screen

When you first launch Excel 2013, the program opens up an Excel Start screen similar to the one shown in Figure 1-1. This screen is divided into two panes. The left pane lists recently opened workbooks and contains an Open Other Workbooks link. The right pane contains a Search Online Templates text box with links to common searches (Budget, Invoice, Calendars, and so on) followed by your user account name, e-mail, and photo, if you use one. Below you see thumbnails of various different templates that you can use in opening a new Excel workbook file.

Figure 1-1: The Excel 2013 program window as it appears immediately after launching the program.

The first template thumbnail displayed here is called Blank Workbook, and you select this thumbnail to start a new spreadsheet of your own design. The second thumbnail is called Take a Tour, and you select this thumbnail to open a workbook with five worksheets that enable you to play around with several of the nifty new features in Excel 2013.

I encourage you to take the time to open the Take a Tour template and play with its worksheets. When you click this thumbnail, Excel opens a new Welcome to Excel workbook where you can experiment with using the new Flash Fill feature to fill in a series of data entries; the Quick Analysis tool to preview the formatting, charts, totals, pivot tables, and sparklines you can add to a table of data; and the Recommended Charts command to create a new chart, all with a minimum of effort. After you’re done playing with these features, you can close the workbook by choosing File⇒Close or pressing Ctrl+W and then clicking the Don’t Save button in the alert dialog box that asks you whether you want to save your changes.

Following the Blank Workbook and Take a Tour template thumbnails, you find all sorts of standard templates that you can select to use as the basis for new worksheets. These templates run the gamut from budget spreadsheets to academic calendars. (See Book II, Chapter 1 for more on creating new workbooks from ready-made and custom templates.)

Excel’s Ribbon User Interface

When you first open a new, blank workbook, Excel 2013 opens up a single worksheet (with the generic name, Sheet1) in a new workbook file (with the generic filename, Book1) inside a program window such as the one shown in Figure 1-2.

The Excel program window containing this worksheet of the workbook is made up of the following components:

File tab: When clicked, this tab opens the Backstage view, which contains a bunch of file-related options including Info, New, Open, Save, Save As, Print, Share, Export, Close, and Account, as well as Options, which enables you to change Excel’s default settings.

Quick Access toolbar: You can click the Save, Undo, and Redo buttons to perform common tasks to save your work and undo and redo editing changes. You can also click the Customize Quick Access Toolbar button to the immediate right of the Redo button to open a drop-down menu containing additional common commands such New, Open, Quick Print, and so on, as well as to customize the toolbar, change its position, and minimize the Ribbon.

Ribbon: Most Excel commands are contained on the Ribbon. They are arranged into a series of tabs ranging from Home through View.

Formula bar: This displays the address of the current cell along with the contents of that cell.

Worksheet area: This area contains all the cells of the current worksheet identified by column headings, which use letters along the top, and row headings, which use numbers along the left edge, with tabs for selecting new worksheets. You use a horizontal scroll bar on the bottom to move left and right through the sheet and a vertical scroll bar on the right edge to move up and down through the sheet.

Status bar: This bar keeps you informed of the program’s current mode and any special keys you engage, and it enables you to select a new worksheet view and to zoom in and out on the worksheet.

When using Excel 2013 on a touchscreen device such as a Windows tablet or other touch-enabled computer, the Ribbon Display Options are automatically set to Tabs (so that associated commands appear only when you tap a tab) and the Quick Access toolbar contains a Touch/Mouse Mode button. Tap this button followed by the Touch option on its drop-down menu to spread out the tabs and their command buttons on the Ribbon. That way you have a fighting chance of correctly selecting them with your finger or stylus.

Figure 1-2: The Excel 2013 program window as it appears after first opening a blank workbook when both Ribbon tabs and commands are displayed.

Going behind the scenes to Excel’s Backstage view

At the top of the Excel 2013 program window, immediately below the Excel program button and the Save button on the Quick Access toolbar, you find the File menu button (the green one with “File” in white letters to the immediate left of the Home tab).

When you click the File menu button, the Excel Backstage view appears. The screen in this view contains a menu of file-related options running down a column on the left side and, depending upon which option is selected, some panels containing both at-a-glance information and further command options.

At first glance, the File menu button may appear to you like a Ribbon tab — especially in light of its rectangular shape and location immediately left of the Ribbon’s initial Home tab. Keep in mind, however, that this important file control is technically a command button that, when clicked, leads directly to a totally new screen with the Backstage view. This screen has its own menu options but contains no Ribbon command buttons whatsoever.

After you click the File menu button to switch to the Backstage view, you can then select the Back button (with the left-pointing arrow) that appears above the Info menu item to return to the normal worksheet view or you can simply press the Esc key.

Getting the lowdown on the Info screen

When you choose File⇒Info at the top of File menu in the Backstage view, an Info screen similar to the one shown in Figure 1-3 appears.

On the left side of this Info screen, you find the following four command ­buttons:

Protect Workbook to encrypt the Excel workbook file with a password, protect its contents, or verify the contents of the file with a digital signature (see Book IV, Chapters 1 and 3 for more on protecting and signing your workbooks)

Inspect Workbook to inspect the document for hidden metadata (data about the file) and check the file’s accessibility for folks with disabilities and compatibility with earlier versions of Excel (see Book IV, Chapter 3 for details on using this feature)

Figure 1-3: The Excel Backstage view displaying the Info screen with permissions, distribution, version commands, and more.

Versions to recover or delete draft versions saved with Excel’s AutoRecover feature (see Book II, Chapter 1 for more on using AutoRecover)

Browser View Options to control what parts of the Excel workbook can be viewed and edited by users who view it online on the Web

On the right side of the Info screen, you see a list of various and sundry bits of information about the file:

Properties lists the Size of the file as well as any Title, Tags, and Categories (to help identify the file when doing a search for the workbook) assigned to it. To edit or add to the Title, Tags, or Categories properties, click the appropriate text box and begin typing. To add or change additional file properties, including the Company, Comments, and Status properties, click the Properties drop-down button and then select Show Document Panel or Advanced Properties from its drop-down menu. Select Show Document Panel to open the Document panel in the regular worksheet window where you can edit properties such as Author, Title, Subject, and Keywords and to add comments. Select the Advanced Properties option to open the workbook’s Properties dialog box (with its General, Summary, Statistics, Contents, and Custom tabs) to change and review a ton of file properties.

Related Dates lists the date the file was Last Modified, Created, and Printed.

Related People lists the name of the workbook’s author as well as the name of the person who last modified the file. To add an author to the workbook file, click the Add an Author link that appears beneath the name of the current author. If the workbook file is new and you’ve never saved it on disk, the words “Not Saved Yet” appear after Last Modified By.

The Open File Location check box appears under the Related Document heading. Select it to open the folder containing the current workbook file, where you can find associated workbook files to work with.

The Show All Properties link, when clicked, expands the list of Properties to include text fields for Comments, Template, Status, Categories, Subject, Hyperlink Base, and Company that you can edit.

Sizing up other File menu options

Immediately below the Info option at the very top of the File menu, you find the commands you commonly need for working with Excel workbook files, such as creating new workbook files as well as saving, opening, and closing files. (See Book II, Chapter 1 for more on saving and closing files and Book II, Chapter 3 for more on opening them.)

The New command immediately below Info displays a New screen, which, just like the Excel Start screen, displays a thumbnail list of all the available spreadsheet templates. (See Book II, Chapter 1 for more on creating and using workbook templates.)

Beneath the Save As command you find the Print option that, when selected, displays a Print screen. This screen contains the document’s current print settings (that you can modify) on the left side and a preview area that shows you the pages of the printed worksheet report. (See Book II, Chapter 5 for more on printing worksheets using the Print Settings panel in the Backstage view.)

Below the Print command you find the Share option, which displays a list of commands for sharing your workbook files online. Beneath this, you find an Export option used to open the Export screen, where you find options for converting your workbooks to other file types as well as controlling the browsing options when the workbook is viewed online in a web browser. (See Book IV, Chapter 4 for more about sharing workbook files online as well as converting them to other file formats.)

Checking user and product information on the Account screen

Below the Close option that is used to close a workbook file (hopefully, after saving all your edits) on the File menu, you find the Account option. You can use this option to review account-related information on the Backstage Account screen. When displayed, the Account screen gives you both user and product information.

On the left side of the Account screen, your user information appears, including all the online services to which you’re currently connected. These services include social media sites such as Facebook, Twitter, and LinkedIn, as well as the more corporate services such as your SkyDrive, SharePoint team site, and Office 365 account.

To add an online service to this list, click the Add a Service button at the bottom and select the service to add on the Images & Videos, Storage, and Sharing continuation menus. To manage which accounts appear on the list, highlight the name and click the Remove button to take it off the list. To manage the settings for a particular service, click the Manage button and then edit the settings online.

Use the Office Background drop-down list box that appears between your user information and the Connected Services list on the Account screen to change the pattern that appears in the background of the title bar of all your Office 2013 programs. By default, Office 2013 uses a Clouds pattern. You can change the background by selecting a new pattern from the Office Background drop-down menu on the Excel Account screen or have no pattern displayed by selecting None from the menu. Just be aware that any change you make here affects the title areas of all the Office 2013 programs you run on your device (not just the Excel 2013 program window).

On the right side of the Account screen, you find the Product information. Here you can see the activation status of your Office programs as well as review the version number of Excel that is installed on your device. Because many licenses allow up to five installations of Office 2013 on different devices (desktop computer, laptop, Windows tablet, and smartphone, for example), you can select the Show Additional Licensing Information link and then click the Manage Account link that appears to go online. There, you can check how many Office installations you still have available and, if need be, manage the devices on which Office 2013 is activated.

Ripping through the Ribbon

The Ribbon (shown in Figure 1-4) groups related commands together with the goal of showing you all the most commonly used options needed to perform a particular Excel task.

Figure 1-4: Excel’s Ribbon consists of a series of tabs containing command buttons arranged into different groups.

The Ribbon is made up of the following components:

Tabs: Excel’s main tasks are brought together and display all the commands commonly needed to perform that core task.

Groups: Related command buttons can be organized into subtasks normally performed as part of the tab’s larger core task.

Command buttons: Within each group you find command buttons that you can select to perform a particular action or to open a gallery. Note that many command buttons on certain tabs of the Excel Ribbon are organized into mini-toolbars with related settings.

Dialog Box launcher: This button is located in the lower-right corner of certain groups and opens a dialog box containing a bunch of additional options you can select.

To get more of the Worksheet area displayed in the program window, you can minimize the Ribbon so that only its tabs are displayed. (In fact, this Tabs display option is the default setting for Excel 2013 running on touchscreen computers like the Microsoft Surface tablet.)

You can minimize the Ribbon by doing any of the following:

Click the Collapse the Ribbon button (the button with the caret symbol in the lower-right corner of the Excel Ribbon).

Double-click a Ribbon tab.

Press Ctrl+F1.

Click the Shows Tabs item on the Ribbon Display Options button’s drop-down menu.

To redisplay the entire Ribbon and keep all the command buttons on the selected tab displayed in the program window, click the tab and then select the Pin the Ribbon button (the one with the push-pin icon that replaces the Unpin the Ribbon button). You can also do this by double-clicking one of the tabs or pressing Ctrl+F1 a second time, or even by selecting the Show Tabs and Commands item on the drop-down menu that appears when you click or tap the Ribbon Display Options button.

When you work in Excel with the Ribbon minimized, the Ribbon expands each time you select one of its tabs to show its command buttons, but that tab stays open only until you select one of its command buttons. The moment you select a command button, Excel immediately minimizes the Ribbon again so that only the tabs display.