20,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2021
Get practical walkthroughs for the most useful Excel features Looking for easy-to-understand, practical guidance on how to go from Excel newbie to number crunching pro? Excel Workbook For Dummies is the hands-on tutorial you've been waiting for. This step-by-step guide is packed with exercises that walk you through the basic and advanced functions and formulas included in Excel. At your own speed, you'll learn how to enter data, format your spreadsheet, and apply the mathematical and statistical capabilities of the program. Work through the book from start to finish or, if you'd prefer, jump right to the section that's giving you trouble, whether that's data visualization, macros, analysis, or anything else. You can also: * Brush up on and practice time-saving keyboard shortcuts for popular commands and actions * Get a handle on multi-functional and practical pivot tables with intuitive practice exercises * Learn to secure your data with spreadsheet password protections Perfect for new users of Excel, Excel Workbook For Dummies is also the ideal resource for those who know their way around a spreadsheet but need a refresher on some of the more advanced features of this powerful program.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 708
Ähnliche
Microsoft® Excel® Workbook For Dummies®, 2nd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com
Copyright © 2022 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 the prior written permission of the Publisher. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Trademarks: Wiley, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation. 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: WHILE THE PUBLISHER AND AUTHORS HAVE USED THEIR BEST EFFORTS IN PREPARING THIS WORK, THEY 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 ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES, WRITTEN SALES MATERIALS OR PROMOTIONAL STATEMENTS FOR THIS WORK. THE FACT THAT AN ORGANIZATION, WEBSITE, OR PRODUCT IS REFERRED TO IN THIS WORK AS A CITATION AND/OR POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT THE PUBLISHER AND AUTHORS ENDORSE THE INFORMATION OR SERVICES THE ORGANIZATION, WEBSITE, OR PRODUCT MAY PROVIDE OR RECOMMENDATIONS IT MAY MAKE. THIS WORK IS SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING PROFESSIONAL SERVICES. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR YOUR SITUATION. YOU SHOULD CONSULT WITH A SPECIALIST WHERE APPROPRIATE. FURTHER, READERS SHOULD BE AWARE THAT WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. NEITHER THE PUBLISHER NOR AUTHORS SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR OTHER DAMAGES.
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 317-572-4002. For technical support, please visit https://hub.wiley.com/community/support/dummies.
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: 2021950990
ISBN: 978-1-119-83215-7 (pbk); ISBN: 978-1-119-83216-4 (ePDF); ISBN: 978-1-119-83217-1 (ePub)
Microsoft® Excel® Workbook For Dummies®
To view this book's Cheat Sheet, simply go to www.dummies.com and search for “Microsoft Excel Workbook For Dummies Cheat Sheet” in the Search box.
Table of Contents
Cover
Title Page
Copyright
Introduction
About This Book
Conventions Used in This Book
Foolish Assumptions
Beyond the Book
Icons Used in This Book
Where to Go from Here
Part 1: Building Worksheets
Chapter 1: Getting Familiar with the Excel Interface
Launching Excel
Identifying the Parts of the Excel Window
Selecting Commands in the Backstage View
Selecting Commands from the Ribbon
Selecting Commands on the Quick Access Toolbar
Chapter 2: Entering the Worksheet Data
Opening a New Workbook
Moving Around the Workbook
Selecting Cell Ranges
Making Cell Entries
Saving the Spreadsheet Data in a Workbook File
Chapter 3: Formatting the Worksheet
Resizing Columns and Rows
Cell Formatting Techniques
Hiding Columns and Rows
Chapter 4: Printing Worksheet Reports
Previewing Pages in the Worksheet and Backstage View
Adjusting Page Breaks
Adding Headers and Footers
Adding Print Titles to a Report
Modifying the Print Settings for a Report
Printing All or Part of the Workbook
Chapter 5: Modifying the Worksheet
Finding and Identifying the Region That Needs Editing
Selecting the Ranges to Edit
Editing Data Entries
Deleting and Inserting Data and Cells
Moving and Copying Data and Cells
Adding Notes to the Worksheet
Using Find and Replace and Spell Checking
Editing Multiple Worksheets
Part 2: Using Formulas and Functions
Chapter 6: Building Formulas
Building Formulas
Using External Reference Links
Controlling When Formulas Are Recalculated
Chapter 7: Copying and Correcting Formulas
Copying Formulas with Relative References
Copying Formulas with Absolute References
Copying Formulas with Mixed References
Using Range Names in Formulas
Building Array Formulas
Tracing and Eliminating Formula Errors
Dealing with Circular References
Chapter 8: Creating Date and Time Formulas
Constructing Date and Time Formulas
Working with Simple Date Functions
Working with Excel’s Fancier Date Functions
Working with the Time Functions
Chapter 9: Financial Formulas and Functions
Working with Financial Functions
Using the Basic Investment Functions
Figuring the Depreciation of an Asset
Chapter 10: Using Math Functions
Rounding Off Values
Finding Products, Powers, and Square Roots
Doing Fancier Sums
Chapter 11: Using Common Statistical Functions
Computing Averages
Finding the Highest and Lowest Values
Counting Cells
Using the Statistical Functions in Analysis ToolPak Add-in
Chapter 12: Using Lookup Functions
Looking Up Stuff with XLOOKUP
Returning Single Values from a Lookup Table
Chapter 13: Using Logical Functions
Working with the Logical Functions
Constructing Decision-Making Formulas
Constructing Error-Trapping Formulas
Chapter 14: Text Formulas and Functions
Constructing Text Formulas
Using Text Functions
Part 3: Working with Graphics
Chapter 15: Charting Worksheet Data
Understanding Excel Charts
Creating Charts
Formatting Charts
Editing Charts
Chapter 16: Adding Graphics to Worksheets
Understanding Graphic Objects
Adding Various Types of Graphic Objects
Part 4: Managing and Securing Data
Chapter 17: Building and Maintaining Tables
Creating a Table
Sorting Tables
Subtotaling a Table
Filtering a Table
Querying External Database Tables
Chapter 18: Protecting Workbooks and Worksheet Data
Password-Protecting the Workbook
Protecting the Worksheet
Doing Data Entry in a Protected Worksheet
Protecting the Entire Workbook
Part 5: Doing Data Analysis
Chapter 19: Performing What-If Analysis
Performing What-If Analysis with Data Tables
Analyzing Data with Scenarios
Goal Seeking in a Worksheet
Creating Complex Models with Solver
Chapter 20: Generating PivotTables
Working with PivotTables
Creating PivotTables
Modifying the PivotTable
Creating PivotCharts
Part 6: Macros and Visual Basic for Applications
Chapter 21: Using Macros
Creating Macros
Assigning Macros to the Quick Access Toolbar
Assigning Macros to the Ribbon
Chapter 22: Using the Visual Basic Editor
Using the Visual Basic Editor
Creating User-Defined Functions
Part 7: The Part of Tens
Chapter 23: Top Ten Features in Excel
The Excel Ribbon and Backstage View
Conditional Formatting
PivotTable Filtering with Slicers
Home Tab Formatting and Editing
Insert Tab Charting
Format as Table
The Zoom Slider on the Status Bar
Page Layout View
Style Galleries
Live Preview
Chapter 24: Top Ten Tips for Using Excel Like a Pro
Generating New Workbooks from Templates
Organizing Data on Different Worksheets
Creating Data Series with AutoFill
Using Range Names
Freezing Column and Row Headings
Preventing Data Entry Errors with Data Validation
Trapping Error Values
Saving Memory by Using Array Formulas
Using Outlines to Control the Display of Data
Using View Side by Side to Work with Two Workbooks
Index
About the Authors
Connect with Dummies
End User License Agreement
List of Tables
Chapter 5
Table 5-1 The Paste Options in the Paste Special Dialog Box
Chapter 6
Table 6-1 The Different Types of Operators in Excel Formulas
Table 6-2 Natural Order of Operator Precedence in Formulas
Chapter 15
Table 15-1 Typical Chart Elements
List of Illustrations
Chapter 1
FIGURE 1-1: The Excel program window as it appears immediately after launching ...
FIGURE 1-2: The Excel Backstage view with the Info button selected.
FIGURE 1-3: The Excel Options dialog box with the Customize Ribbon option selec...
FIGURE 1-4: Click the Customize Quick Access Toolbar button to open its pull-do...
FIGURE 1-5: The Excel Options dialog box with the Quick Access Toolbar option s...
FIGURE 1-6: The top part of the Excel window with the customized Quick Access T...
Chapter 2
FIGURE 2-1: The Backstage view's New screen enables you to search for a templat...
FIGURE 2-2: The new Spring Sale table after completing the initial data entry.
Chapter 3
FIGURE 3-1: The Number tab of the Format Cells dialog box gives you access to a...
Chapter 4
FIGURE 4-1: Displaying the column and margin markers in the Print Preview pane ...
Chapter 5
FIGURE 5-1: Sheet2 after transposing the pasted table into the cell range B10:F...
Chapter 6
FIGURE 6-1: The Formulas sheet with the results of the updated arithmetic and c...
FIGURE 6-2: The Insert Function dialog box helps you locate and add any Excel f...
FIGURE 6-3: The Formulas sheet with the added formulas displayed in their cells...
Chapter 7
FIGURE 7-1: The Function Arguments dialog box showing the final arguments for t...
FIGURE 7-2: The completed Loan Payment Table after copying the PMT function and...
FIGURE 7-3: The workbook with the Spring Sale worksheet displayed in the top wi...
FIGURE 7-4: Worksheet containing a 3-by-2 array in cell range B2:C4 and a 2-by-...
Chapter 10
FIGURE 10-1: A practice worksheet with ROUND, ROUNDUP, and ROUNDDOWN formulas.
FIGURE 10-2: A practice worksheet with PRODUCT, POWER, and SQRT formulas.
Chapter 12
FIGURE 12-1: A sample worksheet showing customer sales by country.
FIGURE 12-2: A sample worksheet with Price Lookup table immediately above the D...
FIGURE 12-3: A practice worksheet with Tip Schedule for looking up the tip amou...
Chapter 13
FIGURE 13-1: The Tip Schedule with alternate 15% and 20% tip percentages.
FIGURE 13-2: The Spring Sale table with Discounted and Discount Amount columns.
FIGURE 13-3: An empty Production Schedule worksheet with #DIV/0! Error values.
Chapter 14
FIGURE 14-1: The Client List worksheet with the names in all capital letters.
FIGURE 14-2: The concatenated worksheet after sorting the names and addresses i...
Chapter 15
FIGURE 15-1: A typical clustered column chart with standard chart elements.
FIGURE 15-2: Typical clustered column chart on its own chart sheet.
FIGURE 15-3: The new embedded clustered column chart immediately after creation...
FIGURE 15-4: The embedded clustered column chart after formatting.
FIGURE 15-5: The final 3-D stacked bar chart created from the original clustere...
Chapter 16
FIGURE 16-1: When you select a graphic object, Excel displays various handles f...
FIGURE 16-2: The right arrow and WordArt graphic objects after overlaying and r...
FIGURE 16-3: An ITB Invoice with resized kite clip art image added to the heade...
FIGURE 16-4: Completed worksheet with photos, header, and footer in the Backsta...
FIGURE 16-5: Completed 3-D clustered column chart with oval-shaped callout and ...
FIGURE 16-6: The 3-D clustered column chart with final WordArt graphic added to...
FIGURE 16-7: The Choose a SmartArt Graphic dialog box.
FIGURE 16-8: The completed organization chart formatted with a colorful and int...
Chapter 17
FIGURE 17-1: The first part of the Employee Table after subtotaling its salarie...
Chapter 18
FIGURE 18-1: The Protect Sheet dialog box when it first opens.
Chapter 19
FIGURE 19-1: The completed one-variable data table.
FIGURE 19-2: The completed two-variable data table.
FIGURE 19-3: Scenario Summary table showing quarterly projected income using cu...
Chapter 20
FIGURE 20-1: Creating a new PivotTable using the Employee table on Sheet2 of yo...
FIGURE 20-2: The PivotTable created for the Employee table immediately after sp...
FIGURE 20-3: Creating a PivotChart for the Employee table.
FIGURE 20-4: The Employee Table PivotChart after formatting.
Chapter 22
FIGURE 22-1: The Visual Basic Editor window as it normally appears when first o...
FIGURE 22-2: The buttons on the Standard toolbar in the Visual Basic Editor.
FIGURE 22-3: The Visual Basic Editor Code window after adding new commands to m...
FIGURE 22-4: The Visual Basic Editor Code window after adding the lines of code...
Guide
Cover
Title Page
Copyright
Table of Contents
Begin Reading
Index
About the Authors
Pages
i
ii
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
145
146
147
148
149
150
151
152
153
155
156
157
158
159
160
161
162
163
164
165
167
168
169
170
171
172
173
174
175
176
177
179
180
181
182
183
184
185
187
188
189
190
191
192
193
194
195
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
373
374
375
Introduction
Microsoft Excel is one of the most powerful and sophisticated software programs available today. That power and that sophistication mean Excel can do some extraordinary, just-this-side-of-miraculous things, but they also mean that Excel comes with a learning curve. Sure, you can get productive with Excel amazingly quickly, but if you want to do more than just scratch Excel's surface, then you need to get comfy with many of the program's deeper — and more useful — features.
Sounds like a plan, right? But what's the best way to get to know this wider Excel landscape? Well, consider what you might do when you arrive in a city you've never visited. One way to get the feel for the place would be to wander the streets willy-nilly. Nothing wrong with that, but it will probably mean your knowledge of the city is scattershot and skin-deep. If you want to really get to know the place, then you need a guidebook that tells you what's worth seeing, how to get there, and what to do when you arrive.
Excel is no different. To really get to know the vast “city” of Excel, clicking commands and cells at random won't get the job done. Instead, it's much better to take a few guided tours that tell you what features are worth learning, how to find those features, and how to use them. This gives you the practical, hands-on experience you need to really learn what Excel can do.
About This Book
As its name suggests, Microsoft Excel Workbook For Dummies is designed to give you the kind of hands-on experience with all the major aspects of the program you need to start using the program for business or home with a degree of confidence and efficiency. As you’d expect from this type of book, the workbook is primarily composed of questions and exercises that give you plenty of opportunities to experience the purpose and benefits of Excel’s many features.
It’s my hope that as a result of doing the exercises in this workbook, you’ll not only be in firm command of the basic skills necessary to work with confidence in Excel, but also have a good idea of the overall power of the program and what you can do with it.
Conventions Used in This Book
By convention, all the text entries that you type yourself appear in bold. When it comes to instructions in the exercises throughout the workbook, you’ll notice two conventions:
Ribbon commands often follow the sequence of the tab, command button, and drop-down menu option. For example, the command Home ⇒ Format ⇒ Column Width means that you should select the Home tab, followed by the Format command button, and then finally the Column Width option on the menu that appears.
Ribbon hot keys are often given following the Ribbon command sequence. For example, Alt+HOW selects the Home tab, followed by the Format command button, and then the Column Width option, entirely from the keyboard.
Filenames in the exercises are shown in full, including their filename extensions as in Exercise3-1.xlsx or Spring Furniture Sales.xlsx. Keep in mind, however, that the display of filename extensions may be turned off on your computer and, in that case, the filenames appear in file lists without their filename extensions as in Exercise3-1 and Spring Furniture Sales.
One other convention that you’ll notice used throughout the text is the display of the names for Excel Ribbon commands, Quick Access toolbar buttons, and dialog box options in the title case, wherein all major words are capitalized except for prepositions. The title case is used to make these names stand out from the rest of the text. Often, however, especially in the case of dialog box options, Microsoft does not always follow this convention, preferring to capitalize only the first letter of the option name.
Foolish Assumptions
I assume that you’re a new user of Microsoft Excel motivated to learn its essentials either for work or at home.
To complete most of the exercises in this workbook, you only need to have Microsoft Excel installed on a computer running a version of Microsoft Windows 10 or 11. And it doesn't matter whether you obtained Excel with a Microsoft 365 subscription or an Office 2021 purchase; this book covers the core functionality of both flavors of Excel. For some of the printing exercises, you will benefit from having a printer installed on your system (although you can complete most of their steps and get the gist of the lessons without actually printing the sample worksheets).
Beyond the Book
Some extra content for this book is available on the web. Go online to find the following:
The examples used in the book:
The downloadable practice files that come with this workbook are an integral part of the workbook experience. These files contain the practice material that you need to complete most of its exercises. You can find these here:
www.wiley.com/go/excelworkbookfd2e
Cheat Sheet:
Besides what you’re reading right now, this book also comes with a free access-anywhere Cheat Sheet that provides a handy reference for Excel's hot keys. To get this Cheat Sheet, go to
www.dummies.com
and type
Microsoft Excel Workbook For Dummies Cheat Sheet
in the Search box.
Icons Used in This Book
Icons are sprinkled throughout the text of this workbook in high hopes that they draw your attention to particular features. Some of the icons are of the heads-up type, whereas others are more informational in nature:
This icon indicates the start of a question-and-answer section in the workbook.
This icon indicates a hint that can help you perform a particular step in the exercise.
This icon indicates a tidbit that, if retained, can make your work somewhat easier in Excel.
This icon indicates a tidbit that is essential to the topic being discussed and is, therefore, worth putting under your hat.
This icon indicates a bit of trickery in the topic that, if ignored, can lead to some sort of trouble in your spreadsheet.
Where to Go from Here
This workbook is constructed such that you don’t have to start working through the exercises in Chapter 1 and end with those in Chapter 24. That being said, it's still to your benefit to complete all the exercises within a particular chapter, if not in a single work session, at least in a short time period.
If you’re a real newbie to Excel and have no experience with any of the earlier versions of the program, I urge you to complete the exercises in Part 1, Chapters 1 through 5, before you take off in your own direction. The exercises in this part are truly fundamental and are meant to give you a strong foundation in the basic features that all Excel users need to know.
Please keep in mind that I designed the exercises in this workbook to work with my Excel companion book, Excel All-In-One For Dummies. It can therefore provide you with additional information about the Excel features you’re using either at the time you go through the workbook exercises or afterwards. To facilitate this crossover usage, I have, wherever possible, used the same example files in the exercises of this workbook as you see illustrated and explained at length in the larger book.
Whatever you do next and wherever you go in this workbook, just be sure that you enjoy yourself!
Part 1
Building Worksheets
IN THIS PART …
Navigate the commands on the tabs of the Excel Ribbon and on the File menu in the Backstage View.
Customize the Quick Access toolbar and Excel Ribbon.
Perform basic data entry chores.
Format worksheet cells.
Print worksheet data.
Edit your worksheet data.
Chapter 1
Getting Familiar with the Excel Interface
IN THIS CHAPTER
Opening and identifying the different parts of the Excel screen
Selecting commands in the Excel Backstage view
Selecting commands on the Ribbon and Quick Access toolbar
Customizing the Ribbon and Quick Access toolbar
If the proverbial journey of a thousand miles begins with a single step, what should be the first step on your new Excel journey? You can take lots of possible directions, but might I humbly suggest a tour? Nothing exhaustive, mind you; just a quick look at the most basic elements of the Excel program window. Does that sound too elementary? You'd be surprised. One of the secrets of Excel proficiency and efficiency is knowing where to find the command, feature, or setting you want to work with next. Sure, maybe you really want to waste precious time clicking aimlessly around the Excel interface until your blood boils and jets of steam blast out of your ears. Hey, it's your life. But my guess is that doesn't sound appealing to you, not even a little. So: A tour it is, then.
To that end, the exercises in this first chapter are designed to get you familiar with the Excel interface. After doing these exercises, you should be comfortable with all aspects of the Excel window and the command structure and ready to do all the rest of the exercises in this book.
Launching Excel
Excel is one of the primary application programs included as part of Microsoft Office. To get proficient with Excel, you need to be familiar with all the various ways of launching the program.
Q. How many ways are there to start Excel?
A. You should be familiar with the two main methods:
Click Start and then click Excel in the All Apps list. If you're using Windows 11, you need to first click the Start menu's All Apps button and then click Excel.
Double-click an Excel workbook file in any folder on any drive to which your computer has access.
Q. Are there quicker ways to launch Excel?
A. Yes, you can use two other methods:
If you see an Excel icon pinned to the Start menu, click that icon.
If you see an Excel button pinned to the Windows taskbar, click that button.
Try It
Exercise 1-1: Pinning Excel to the Windows Start Menu and Taskbar
In Exercise 1-1, you pin Excel both to the Windows Start menu and to the Windows taskbar and then launch Excel using each of these two methods:
Click Start and then locate Excel in the All Apps list (remember to first click All Apps if you're running Windows 11).
Right-click Excel and then click Pin to Start.
Windows adds a pinned tile for Excel to the bottom of the Start menu.
In Windows 11, click Back to return to the main Start menu, right-click the pinned Excel icon, and then click Move to Top.
Windows moves the pinned Excel tile to the top-left corner of the Start menu.
Right-click the pinned Excel tile on the Start menu and then click Pin to Taskbar.
Windows adds a pinned Excel button to the taskbar.
If you don’t like where Windows pinned the Excel button, click and drag it to the left or right to your preferred location.
Windows moves the pinned Excel button along with the mouse pointer and you can drop the button in your preferred position.
Practice launching Excel by clicking the pinned Excel Start menu tile and then exiting the program. Then launch Excel again, this time by clicking the pinned Excel button on the Windows taskbar.
You can leave Excel running for Exercise 1-2.
Identifying the Parts of the Excel Window
When you launch Excel by opening the Windows Start menu and clicking Excel, the Home tab of Excel's Backstage view appears. Click the Blank Workbook thumbnail to start a fresh workbook, which contains a single worksheet named Sheet1.
Before you can start using Excel, you must be familiar with its window. Figure 1-1 shows you the Excel window as it appears when you launch a new workbook. Note the names of the different parts of the window before you perform Exercise 1-2.
FIGURE 1-1: The Excel program window as it appears immediately after launching a fresh workbook.
Q. What are the primary functions of the commands located on the File menu in the Excel Backstage View?
A. To open, close, save, print, and share your Excel workbook files as well as to modify the Excel program options.
Q. What’s the primary function of the Ribbon in Excel?
A. To group related Excel commands together and give you quick and easy access to these commands.
Q. What is the primary function of the Quick Access toolbar?
A. To enable you to quickly select Excel commands that you use all the time without having to open the File menu or use the Ribbon commands.
Selecting Commands in the Backstage View
Clicking the File tab opens the Excel Backstage view with a menu of options that appears down the left side. Almost all the commands on this menu are related to actions that affect the entire file, such as saving and printing. If you prefer, you can open this view and access the menu by pressing Alt+F (F for File) instead of clicking the File menu.
When you select any of the major options on the Backstage view — Home, New, Open, Info, Save, Save As, and so on — panels appear that bring together further related and commonly used options on the left side along with pertinent information on the right.
Try It
Exercise 1-2: Opening the Backstage View and Selecting Its Buttons
In this exercise, you get familiar with Excel's Backstage view and its commands by opening the Backstage view and selecting some of its buttons. Make sure that Excel is running and a blank workbook with its empty Sheet1 worksheet is active on your computer monitor.
Click the File tab to switch to the Excel Backstage view and display the menu of buttons (Home through Options) on the left side of this screen. By default, Excel selects the Home button.
Click the Info button on the menu.
The Info screen now appears to the immediate right of the Info button (see Figure 1-2). The Info screen is divided into two areas: the left side offers commands for working with the file, while the right side displays information about the file.
Now, click the Save As button.
You use the Save As screen to save a new workbook or to save an existing workbook with a new name or location.
Click Browse.
Excel opens the Save As dialog box where you can modify the name, location, and type of Excel workbook file before saving a copy of it.
Press the Esc (Escape) key on your keyboard to close the Save As dialog box.
Click the Share button.
Excel closes Backstage view and opens the Share dialog box, which enables you to share workbook files with co-workers and clients.
Press Esc to close the Share dialog box.
Press Ctrl+P (the shortcut key for printing in Excel).
Excel opens the Backstage view and displays the Print panel where you can preview the printout (when there’s data in your worksheet that can be printed) and change several print settings. Because you selected the Print panel from an empty worksheet, the message, “We didn't find anything to print” appears on the right panel where the first page of the workbook’s print preview normally appears.
Press Esc to return to the normal worksheet view and then press Alt+FT.
Doing this selects the Options command in the Backstage view, which in turn, opens the Excel Options dialog box. This dialog box contains all the options for changing the Excel program and worksheet options. These options are divided into categories General through Trust Center.
Make sure the General category is selected, use the Office Background list to select a background pattern, and then click OK to put the new setting into effect.
FIGURE 1-2: The Excel Backstage view with the Info button selected.
Selecting Commands from the Ribbon
The Excel Ribbon contains the bulk of all the commands that you use in creating, editing, formatting, and sharing your spreadsheets, charts, and tables. As shown in Figure 1-1, normally the Ribbon is divided into the following tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View. There's also a Help tab for accessing the Excel Help system. If you're using a touchscreen PC, you also see a Draw tab.
The commands that appear on each tab are then further divided into groups containing related command buttons. Also, many of these groups contain a dialog box launcher button that appears in the lower-right corner of the group. Clicking this button opens a dialog box of further options related to the group.
Try It
Exercise 1-3: Selecting Commands from the Ribbon
In Exercise 1-3, you practice selecting commands from the Ribbon. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Click the Formulas tab to displays its commands.
Note that the commands on the Formulas tab are divided into four groups: Function Library, Defined Names, Formula Auditing, and Calculation.
Press the Alt key.
Note the access-key letters that now appear on the File menu, title bar, and the Ribbon tabs.
If you prefer selecting Excel commands from the keyboard, you’ll probably want to memorize the following access keys for selecting these tabs:
Home tab: Alt+H
Insert tab: Alt+N
Draw tab: Alt+JI
Page Layout tab: Alt+P
Formulas tab: Alt+M
Data tab: Alt+A
Review tab: Alt+R
View tab: Alt+W
Help tab: Alt+Y2
Press W to display the contents of the View tab and then press VG to deselect the Gridlines check box and hide the worksheet gridlines.
Select the Gridlines check box to redisplay the gridlines in the worksheet.
As you may have noticed, the Ribbon takes up quite of bit of screen space that is otherwise used to display worksheet data. You can take care of this by setting Excel to minimize the Ribbon each time you select one of its commands to display only the tab names.
Click the Ribbon Display Options button (pointed out earlier in Figure 1-1) and then click Show Tabs Only.
Excel immediately minimizes the Ribbon to display only the tab names. Click a tab to display its buttons. Excel minimizes the Ribbon to its tab names once again after you select a tab’s commands or click outside the Ribbon.
Click Data on the minimized Ribbon.
Excel expands the Ribbon to display all the Data tab buttons.
Click anywhere in the worksheet area to minimize the Ribbon once again.
The only problem with this minimized Ribbon arrangement is that the temporarily expanded Ribbon covers the first three rows of the worksheet. This makes it very difficult to work with data at the top of the worksheet. For that reason, as well as to help you get comfortable with unfamiliar Ribbon commands, you'll work with the Ribbon expanded at all times in all remaining exercises in this workbook.
To return to having Excel display the entire Ribbon full-time, click any tab, click the Ribbon Display Options button and then click Always Show Ribbon.
The Ribbon now remains fully displayed at all times as you select any of its tabs and buttons without ever obscuring any part of the worksheet display.
Adding a custom tab to the Excel Ribbon
Excel enables you to customize the Ribbon by creating a custom tab to which you can then add your own groups of commands. When you create a custom tab, Excel automatically assigns an available hot key to it.
Try It
Exercise 1-4: Adding a Custom Tab to the Excel Ribbon
In Exercise 1-4, you practice adding a custom tab to the Ribbon. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Choose File ⇒ Options (Alt+FT) to open the Excel Options dialog box and then click the Customize Ribbon option.
Alternatively, right-click any part of the Ribbon and then click Customize the Ribbon.
Excel displays the Customize the Ribbon panel in the Excel Options dialog box. This panel is divided into two list boxes: Choose Commands From on the left side and Main Tabs on the right side (see Figure 1-3).
In the Main Tabs list box, click View to select it (be sure to click just the name View and not its check box) and then click New Tab.
Right below the View tab, Excel inserts a new tab with the generic name New Tab (Custom), which includes a new group with the generic name New Group (Custom). This custom tab and group appear between the View tab and the Developer tab in the Main Tabs list box.
Click New Tab (Custom) in the Main Tabs list box to select it and then click the Rename command button.
Excel opens the Rename dialog box where you can replace the generic New Tab display name with a descriptive name.
Replace New Tab by typing Misc (for Miscellaneous) in the Display Name text box and then click OK.
Misc (Custom) now appears in the Main Tabs list box sandwiched between View and Developer.
FIGURE 1-3: The Excel Options dialog box with the Customize Ribbon option selected.
Adding commands to groups on your custom tab
After you add a custom tab to the Excel Ribbon, you can then start adding the commands you want to appear on this tab. Just as with the standard Ribbon tabs, the commands you add to your own custom tab are arranged in groups. When you first create a custom tab, it contains only a single tab — named New Group (Custom) — into which to add your commands. You can, however, add other groups to the custom tab using the New Group command button as well as give these groups their own descriptive names using the Rename command button.
Try It
Exercise 1-5: Adding Commands to a Custom Tab
In Exercise 1-5, you practice adding commands to the custom tab you added to the Ribbon in the Exercise 1-4. Before you start this exercise, make sure that the Excel Options dialog box is still open with a Misc (Custom) tab appearing in the Main Tabs list box between View and Developer.
Click the New Group (Custom) listing under Misc (Custom) in the Main Tabs list box to select it and then click the Rename button.
Excel opens the Rename dialog box where you can replace the generic New Group name with your own descriptive name.
Replace New Group by typing Data Form in the Display Name text box and then clicking OK.
Data Form (Custom) now appears as the sole group on the Misc custom tab in the Main Tabs list box. Now, you’re ready to add the Form command button to the Data Form group that you can use later when completing some of the exercises in Chapter 17.
Use the Choose Commands From drop-down list to select Commands Not in the Ribbon.
Excel now displays an alphabetical list of commands that are not currently on the Ribbon.
Click the Form button in this Commands Not in the Ribbon list and then click Add.
Excel adds the Form button under the Data Form (Custom) group in the Main Tabs list box.
Click OK to close the Excel Options dialog box.
The custom Misc tab you just created now appears at the end of the Excel Ribbon.
Click the Misc tab.
The Misc tab is selected, displaying its sole Form button in the single Data Form group.
Click the Home tab to select it.
Selecting Commands on the Quick Access Toolbar
As its name implies, the purpose of the Quick Access toolbar is to give you speedy access to a few Excel tools. That sounds great! So why is the Quick Access toolbar hidden by default in the latest versions of Excel? Good question. The Quick Access toolbar used to appear in the Excel title bar (although you could change that position), so perhaps Microsoft figured the title bar was getting a tad overcrowded. Microsoft did keep the AutoSave switch and the Save button in the title bar, but these are no longer part of the Quick Access toolbar. (Also, Microsoft moved the Undo and Redo buttons to the Home tab.)
If you want to use the Quick Access toolbar, your first chore is to display it.
Displaying the Quick Access toolbar
To get the Quick Access toolbar onscreen, use any one of the following methods:
Right-click any part of the Ribbon and then click Show Quick Access Toolbar.
Click Ribbon Display Options (see
Figure 1-1
) and then click Show Quick Access Toolbar.
Choose File ⇒ Options (or press Alt+FT) to open the Excel Options dialog box, click Quick Access Toolbar, select the Show Quick Access Toolbar check box, and then click OK.
Customizing the Quick Access toolbar
The Quick Access toolbar appears as a strip below the Ribbon and starts off with just the Customize Quick Access Toolbar button, pointed out in Figure 1-4 that, when clicked, opens a pull-down menu. The options on this pull-down menu enable you to quickly customize the buttons that appear in the Quick Access toolbar. In addition, you can change the placement of the Quick Access toolbar by moving it up so that it appears immediately above the Ribbon.
Try It
Exercise 1-6: Customizing the Quick Access Toolbar
In Exercise 1-6, you practice customizing the contents and position of the Quick Access toolbar using options that appear on the Customize Quick Access Toolbar menu. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Click the Customize Quick Access Toolbar button and then click Show Above the Ribbon.
The Quick Access toolbar with its Customize Quick Access Toolbar button now appears immediately above the Ribbon.
Click the Customize Quick Access Toolbar button and then click Show Below the Ribbon.
The Quick Access toolbar returns to its default position below the Ribbon.
Click the Customize Quick Access Toolbar button and then click New.
Excel adds the New button to the Quick Access toolbar so that you can click it to create a new, blank workbook.
Repeat Step 3 to add the Open, Quick Print, and Spelling buttons to the Quick Access toolbar. If you're using Excel on a touchscreen device, also add the Touch/Mouse Mode button, which enables you to quickly switch into Touch mode for easier touch access to the Ribbon commands.
Use the ScreenTips attached to each button to verify that you’ve correctly added the New, Open, Quick Print, and Spelling buttons (as well as the Touch/Mouse mode button, if applicable) to the Quick Access toolbar, noting the shortcut keys listed.
FIGURE 1-4: Click the Customize Quick Access Toolbar button to open its pull-down menu.
Adding more commands to the Quick Access toolbar
When customizing the command buttons on the Quick Access toolbar, you aren’t limited to the selection of commands that appear on the Customize Quick Access Toolbar pull-down menu. Using command options that appear in the Excel Options dialog box, you can add buttons for any of the commands that appear on the Ribbon as well as some Excel commands that remain completely unavailable until you add them to the Quick Access toolbar.
Try It
Exercise 1-7: Using the Excel Options Dialog Box to Add Commands to the Quick Access Toolbar
In Exercise 1-7, you practice customizing the contents of the Quick Access toolbar using commands that appear in the Excel Options dialog box. Make sure that Excel is running and an empty Sheet1 worksheet is active on your computer monitor.
Click the Customize Quick Access Toolbar button and then click the More Commands option on its menu.
Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected (see Figure 1-5). This dialog box contains two list boxes:
The Choose Commands From list box on the left where you select the commands to add to the toolbar
The Customize Quick Access Toolbar list box on the right, showing the buttons on the toolbar and their order
To add a new command to the toolbar, you select it in the Choose Commands From list box and then click the Add button. To reorder the buttons on the toolbar, you click its command button in the Customize Quick Access Toolbar list box and then click the Move Up or Move Down buttons (with the black triangles pointing up and down, respectively) until the selected button is in the desired position.
Click the drop-down button on the Choose Commands From drop-down list box and then click the Commands Not in the Ribbon option on its drop-down menu.
The Choose Command From list box now contains only command buttons that are not found on the various tabs of the Excel Ribbon.
Click the AutoFormat command option in the Choose Commands From list box (the one with the lightning bolt on top of a small table) and then click the Add button.
The AutoFormat command option is now listed at the very bottom of your Customize Quick Access Toolbar list box, indicating that it is now the last button on the Quick Access toolbar.
Repeat Step 3 to add the AutoFilter and Draw Borders command options to the Quick Access toolbar.
Next, you want to modify the order in which the command buttons appear on your customized Quick Access toolbar so that they appear in this order arranged in three groups:
Open, Quick Print, and New File
AutoFormat, AutoFilter, and Draw Borders
Spelling and Touch/Mouse Mode (if you're using it)
FIGURE 1-5: The Excel Options dialog box with the Quick Access Toolbar option selected.
Click the Open command option in the Customize Quick Access Toolbar list box to select it and then click the Move Up button (the one with the black triangle pointing upward) until Open is the first command in this list.
Use the same technique to move the New File command button up until it appears below the Quick Print button.
Use the Move Down button to move the Spelling and Touch/Mouse Mode buttons so that they now appear in the same order below the Draw Borders button.
The command buttons for your customized version of the Quick Access toolbar now appear in the correct order in the Customize Quick Access Toolbar list box in the Excel Options dialog box. The only other thing you need to do is to divide them into groups by adding a vertical bar called a separator.
Click the New File command option in the Customize Quick Access Toolbar list box to select it and then click the Separator option at the very top of the Choose Commands From list box to select this option. Click the Add button.
Excel inserts a separator between the New File and AutoFormat buttons in the Customize Quick Access Toolbar list box.
Use this same technique to add a separator between the Draw Borders and Spelling buttons in the Customize Quick Access Toolbar list box.
Your customized Quick Access toolbar now contains three groups of command buttons created by the two separator options that appear after the New File button and the Draw Borders button.
Click OK to close the Excel Options dialog box.
Check the buttons on your customized toolbar against those shown in the toolbar in Figure 1-6.
FIGURE 1-6: The top part of the Excel window with the customized Quick Access Toolbar.
Chapter 2
Entering the Worksheet Data
IN THIS CHAPTER
Starting with a blank workbook
Moving around the workbook
Selecting cell ranges in a worksheet
Doing simple data entry in a worksheet
Using AutoFill to create data series and copy formulas
Saving the spreadsheet as an Excel workbook file
Getting Excel off the ground (which I covered in Chapter 1) is a necessary first step, but you end up with a blank worksheet staring you in the face. Now what? Ah, that's where you put on your hat that says, “Data Entry.” Data entry refers to filling in Excel's worksheet cells with labels, numbers, dates, and whatever other data you need to get the job done. Data entry is the bread and butter of any worksheet you create or edit. As a result of doing the exercises in this chapter, you get plenty of practice in launching Excel, moving around a new worksheet, the many aspects of doing data entry, and, most importantly, saving your work.
Opening a New Workbook
Each time you launch Excel (using any method other than double-clicking an Excel workbook file icon), you see the Start screen, which is like a scaled-down version of the Backstage view that I talk about in Chapter 1. You see buttons for Home, New, and Open, as well for Account, Feedback, and Options. Use Home to create a blank workbook (by clicking the Blank Workbook thumbnail) or to open a recent file; use New to create either a blank workbook or create a new workbook from one of Excel's many template files; use Open to launch an existing workbook file.
For now, click the Blank Workbook thumbnail either in the Home screen or the New screen. The blank workbook that Excel creates is given the temporary filename Book1 (subsequent blank workbooks are named Book2, Book3, and so on). If you want to start work on a spreadsheet in another workbook, click the New command button on your customized version of the Quick Access toolbar (assuming that you performed Exercise 1-6 in Chapter 1).
When Excel opens a blank workbook, the new workbook follows the Blank Workbook template (which controls the formatting applied to all its blank cells). You can also open new workbooks from other, specialized templates or from a workbook that you’ve already created. To do this, choose File ⇒ New or press Alt+FN. Excel opens the Backstage view's New screen where you can search for a template to use. Figure 2-1 shows the templates that appear when you run a search using the word personal.
FIGURE 2-1: The Backstage view's New screen enables you to search for a template.
Q. What’s so special about an Excel template file?
A. A template is a particular type of Excel file designed to automatically generate new workbooks that use both its data and formatting. Each time you create a new workbook based on a template, Excel opens a copy of the template file rather than the original (by appending a number to the template’s original filename). Excel template files use the filename extension .xltx to differentiate them from regular Excel workbook files that carry an .xlsx filename extension.
Q. How do you create templates out of your own Excel workbook files?
A. Build a spreadsheet in a new or existing workbook file. To this spreadsheet add all the required text and data, calculating formulas, and formatting required in all the files you will generate from its ensuing template. Choose File ⇒ Save As and then click Browse. In the Save As dialog box, select Excel Template (*.xltx) in the Save As Type drop-down list box and edit the filename (without removing the .xltx filename extension) before you click the Save button.
Try It
Exercise 2-1: Opening a New Workbook from the Blank Workbook Template
In Exercise 2-1, you launch Excel (if it's not already running) and then open a new workbook (Book2). Switch back to Book1 (notice the change in the Excel program title bar) and then close the workbook. Notice what happens to Book2 when you close Book1. Leave Book2 open for the next exercise.
To switch from Sheet1 of Book2 and make Sheet1 of Book1 active, hover the mouse pointer over the Excel taskbar button and then click the Book1 thumbnail that appears above the button. To switch back to Book2, hover the mouse pointer over the Excel taskbar button and then click the Book2 thumbnail that appears above the button. To close a workbook file, choose File ⇒ Close or press Alt+FC.
If necessary, launch Excel and then click Blank Workbook. In the program title bar, note that Book1 is the current workbook file.
Choose File ⇒ New or press Alt+FN.
Excel opens the Backstage view's New screen.
Click the Blank Workbook thumbnail.
Excel creates a new workbook file, indicated by Book2 in the program title bar. Hover the mouse pointer over the Excel taskbar button and note the appearance of Book1 and Book2 thumbnails above the Excel button.
Press Alt+Tab to make Book1 active. Hover the mouse pointer over the Excel taskbar button and then click the Book2 thumbnail that appears above the button to make Book2 active.
You can use either method to switch between open Excel workbook files.
With Book2 still active (indicated by Book2 in the program title bar), choose File ⇒ Close or press Alt+FC.
Excel closes Book2 and returns you to Book1. Note that the program would have prompted you to save changes to Book2 before closing it if you had made any data entries in the worksheet. Leave the Book1 workbook open for the next exercise.
Try It
Exercise 2-2: Open a New Workbook File from a Template File
In Exercise 2-2, you open a new workbook from the Personal Monthly Budget template installed with Excel.
Choose File ⇒ New or press Alt+FN.
Excel opens the Backstage view's New screen.
Click Budgets in the Suggested Searches section and then click the Personal Monthly Budget thumbnail.
Excel displays a preview and description of this template.
Click Create.
Excel downloads a copy of the Personal Monthly Budget template and then creates a new workbook from that copy. The new workbook's temporary name is Personal Monthly Budget1. This template is all set for you to start editing its values.
Choose File ⇒ Close to close the Personal Monthly Budget1 workbook file. If you made any changes to the file, you can click Don’t Save in the alert dialog box that appears if you don't want to save changes to this file.
Excel returns you to your Book1 workbook file that you leave open for the next exercise.
Moving Around the Workbook
The key to doing both data entry and data editing in any Excel worksheet is selecting the cell or cells you want to fill or modify. Selecting a cell almost always entails moving the cell cursor (or pointer) to another part of the current worksheet. Sometimes, it also involves activating a different worksheet in the workbook file.
Excel gives you plenty of choices in techniques for moving the cell cursor, some of which use the mouse and others of which use the keyboard.
Moving within the displayed area
Here’s a summary of the most important ways to move the cell cursor to a different cell within the area of the worksheet that's currently displayed onscreen:
Click the cell with the white-cross mouse pointer.
Press the arrow keys until the cell cursor is in the cell.
Click the Name box at the beginning of the Formula bar, enter the reference of the target cell (by column letter and row number as in D12), and press Enter.
Try It
Exercise 2-3: Moving the Cell Cursor in a Displayed Area of the Worksheet
For Exercise 2-3, make sure Sheet1 of the blank workbook, Book1, is active before you start practicing moving the cell cursor to different cells in the displayed area using the mouse, arrow keys, and Name box:
Move the cell cursor to cell F9 with the mouse.
Move the cell cursor to cell C13 using just the down arrow and left arrow keys.
Move the cell cursor to cell A1 using the Name box.
You can always move the cursor to cell A1 (also known as the Home cell) of any active worksheet simply by pressing Ctrl+Home.
Moving to a new area of the worksheet
Many times you have to make cell entries in areas that aren’t currently displayed in the active worksheet. One of quickest ways to do this is by entering the reference of the cell you want to go to in the Name box. You can also use any of the following techniques to scroll to new parts of the current worksheet:
To scroll up and down a window's worth of worksheet rows, press Page Up or Page Down or click the blank area above or below the scroll box in the vertical scroll bar.
To scroll left and right a window's worth of worksheet columns, click the blank area to the left or right of the scroll box in the horizontal scroll bar.
To scroll right and left an entire window at a time, press Alt+Page Down and Alt+Page Up, respectively.
To quickly scroll through rows or columns of the worksheet, hold down the Shift key as you drag the scroll box up or down in the vertical scroll bar, or left and right in the horizontal scroll bar.
If you use a mouse with a wheel button, scroll up and down the rows of the worksheet by rotating the wheel button forward (to scroll up) and backward (to scroll down).
If you use a mouse with a wheel button, pan through the rows and columns of the worksheet by clicking the wheel button and then dragging the triangular mouse pointer in the direction you want to scroll.
Scrolling is not the same as selecting! After scrolling to a new part of the worksheet in view, you still have to select a cell by clicking in it to set the cursor in it.
Try It
Exercise 2-4: Moving the Cell Cursor to Unseen Parts of a Worksheet
In Exercise 2-4, you practice moving the cell cursor to cells in the same worksheet that are not currently displayed on your screen.
Move the cell cursor to cell C125 with the Name box on the Formula bar.
Type c125 in the Name box and then press the Enter key.
Move the cell cursor to cell CA125 using the horizontal scroll bar.
