14,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
A fast, up-to-date, and convenient desk reference for Excel users of any skill level
Drawn from the most important tasks in the latest bestselling Excel Bible , Excel Quick and Easy is your ticket to data mastery. Excel veterans Michael Alexander and Dick Kusleika distil the most essential and useful tasks you need to understand about the world's most popular spreadsheet program, from functions to charts, graphs, formulas and more.
Prepare for a whirlwind tour of Excel, packed with simple and step-by-step guides to common and lesser-known Excel features. This book shows you how to:
- Create new spreadsheets and workbooks by entering and editing worksheet data
- Simplify working with large amounts of data by naming and moving ranges
- Make calculations and draw conclusions from your data by using formulas
- Visualize and present your data by creating functional charts
The secret weapon in your productivity arsenal
Being great at Excel is quickly becoming a standard expectation for a ton of employers and organizations, in all sorts of industries. Sharpening your skills can boost your workplace performance and make it easier to land promotions or find new roles. Excel Quick and Easy makes it a breeze to develop the proficiencies that help you stand out from your peers.
Unique features of this book
- Step-by-step guides to the most commonly used and productive Excel tasks, from basic worksheet operations to formatting spreadsheets for hardcopy printing
- Intuitive explanations for making your data tell a compelling story with visualizations, including charts, graphs, and tables
- Advanced number-crunching techniques, including formulas and functions, that help you unlock fresh insights and new conclusions from your data
Excel Quick and Easy is the perfect reference for brand-new Excel users trying to get up-to-speed quickly and confidently. It's also a must-read for anyone migrating from another spreadsheet program, like Google Sheets, or more experienced Excel users who need to solidify and improve their skills.
If you're tired of stumbling through your spreadsheets and just “surviving” in Excel, grab a copy of Excel Quick and Easy and supercharge your productivity. You'll refine your understanding of the basics, learn brand-new skills and features, and become the Excel expert that every office desperately needs.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 319
Veröffentlichungsjahr: 2025
Ähnliche
Table of Contents
Cover
Table of Contents
Title Page
CHAPTER 1: Introducing Excel
Understanding What Excel Is Used For
Understanding Workbooks and Worksheets
Moving Around a Worksheet
CHAPTER 2: Creating Your First Excel Workbook
Getting Started on Your Worksheet
Filling In the Month Names
Entering the Sales Data
Formatting the Numbers
Making Your Worksheet Look a Bit Fancier
Summing the Values
Creating a Chart
Printing Your Worksheet
Saving Your Workbook
CHAPTER 3: Entering and Editing Worksheet Data
Exploring Data Types
Entering Text and Values into Your Worksheets
Entering Dates and Times into Your Worksheets
Modifying Cell Contents
CHAPTER 4: Introducing Formulas and Functions
Understanding Formula Basics
Entering Formulas into Your Worksheets
Editing Formulas
Using Cell References in Formulas
CHAPTER 5: Moving Beyond Formula Basics
Introducing Formula Variables
Correcting Common Formula Errors
Using Advanced Naming Techniques
Working With Formulas
CHAPTER 6: Performing Basic Worksheet Operations
Learning the Fundamentals of Excel Worksheets
Working With Rows and Columns
CHAPTER 7: Working with Excel Ranges
Understanding Cells and Ranges
Copying or Moving Ranges
Using Names to Work with Ranges
CHAPTER 8: Working with Tables
Understanding a Table's Structure
Creating a Table
Adding Data to a Table
Sorting and Filtering Table Data
Changing the Table's Appearance
Using Formulas in Tables
CHAPTER 9: Formatting Worksheets
Getting to Know the Formatting Tools
Formatting Your Worksheet
Using Named Styles for Easier Formatting
Understanding Document Themes
CHAPTER 10: Using Conditional Formatting
Specifying Conditional Formatting
Using Graphical Conditional Formats
Creating Formula-Based Rules
Conditional Formatting Formula Examples
Working with Conditional Formats
CHAPTER 11: Getting Started with Excel Charts
What Is a Chart?
Basic Steps for Creating a Chart
Modifying and Customizing Charts
CHAPTER 12: Understanding Chart Types
Choosing a Chart Type
Newer Chart Types for Excel
CHAPTER 13: Printing Your Work
Doing Basic Printing
Changing Your Page View
Adjusting Common Page Setup Settings
Adding a Header or a Footer to Your Reports
APPENDIX: Excel Keyboard Shortcuts
Copyright
End User License Agreement
List of Tables
Chapter 1
TABLE 1.1 Parts of the Excel screen that you need to know
TABLE 1.2 Excel worksheet movement keys
Chapter 4
TABLE 4.1 Example formulas and their results
TABLE 4.2 Operators used in formulas
TABLE 4.3 Example formulas using operators and their results
TABLE 4.4 Operator precedence in Excel formulas
Chapter 5
TABLE 5.1 Excel error values
TABLE 5.2 Reference operators for ranges
Chapter 6
TABLE 6.1 Characters you can't use in sheet names
Chapter 7
TABLE 7.1 Go To Special options
Chapter 11
TABLE 11.1 Limitations of Excel charts
Chapter 13
TABLE 13.1 Where to change printer settings
TABLE 13.2 Header and footer buttons and their functions
List of Illustrations
Chapter 1
FIGURE 1.1 The Excel screen has many useful elements that you will use often...
FIGURE 1.2 The active cell is the one with the dark border—in this case, cel...
Chapter 2
FIGURE 2.1 Your worksheet after you've entered the column headings and month...
FIGURE 2.2 Your worksheet after you've created the formulas
FIGURE 2.3 Your worksheet after you've converted the range to a table
FIGURE 2.4 The table and chart
Chapter 3
FIGURE 3.1 You can use values, text, and formulas to create useful Excel wor...
FIGURE 3.2 The Formula bar, expanded in height to show more information in t...
FIGURE 3.3 When you're editing a cell, the Formula bar enables two new icons...
FIGURE 3.4 You can use the Advanced tab in the Excel Options dialog box to s...
FIGURE 3.5 This series was created by using AutoFill.
FIGURE 3.6 AutoCorrect allows you to create shorthand abbreviations for text...
FIGURE 3.7 Excel's built-in data form can simplify many data-entry tasks.
Chapter 4
FIGURE 4.1 Excel displays a drop-down list when you enter a formula.
FIGURE 4.2 You can insert a function by selecting it from one of the functio...
FIGURE 4.3 The Insert Function dialog box
FIGURE 4.4 The Function Arguments dialog box
FIGURE 4.5 Copying a formula that contains relative references
FIGURE 4.6 Formula references to the sales tax cell should be absolute.
FIGURE 4.7 Using mixed cell references
Chapter 5
FIGURE 5.1 Using the
LET
function to simplify a nested
IF
statement
FIGURE 5.2 Using the
LET
function to simplify a
SWITCH
statement
FIGURE 5.3 Excel allows you to name a formula that doesn't exist in a worksh...
FIGURE 5.4 You can use a range intersection formula to determine values.
FIGURE 5.5 Creating names for all values in a table
Chapter 6
FIGURE 6.1 Use the Arrange Windows dialog box to arrange all open non-minimi...
FIGURE 6.2 Use the sheet tab controls to activate a different worksheet or t...
FIGURE 6.3 Excel's warning that you might be losing some data
FIGURE 6.4 Use the Move Or Copy dialog box to move or copy worksheets in the...
FIGURE 6.5 You can't insert a new row or column if it causes nonblank cells ...
FIGURE 6.6 You can insert partial rows or columns by using the Insert dialog...
Chapter 7
FIGURE 7.1 When you select a range, it appears highlighted, but the active c...
FIGURE 7.2 Excel enables you to select noncontiguous ranges.
FIGURE 7.3 The worksheets in this workbook are laid out identically.
FIGURE 7.4 In Group mode, you can work with a three-dimensional range of cel...
FIGURE 7.5 Use the Go To Special dialog box to select specific types of cell...
FIGURE 7.6 The Find And Replace dialog box, with its options displayed
FIGURE 7.7 The Find And Replace dialog box, with its results listed
FIGURE 7.8 The Paste icons on the context menu provide more control over how...
FIGURE 7.9 Use the Clipboard task pane to copy and paste multiple items.
FIGURE 7.10 Use Paste All to convert multiple columns to one.
FIGURE 7.11 Excel offers several pasting options, with preview. Here, the in...
FIGURE 7.12 The Paste Special dialog box
FIGURE 7.13 Transposing a range changes the orientation as the information i...
FIGURE 7.14 Create names for cells or ranges by using the New Name dialog bo...
FIGURE 7.15 Use the Create Names From Selection dialog box to name cells usi...
FIGURE 7.16 Use the Name Manager to work with range names.
Chapter 8
FIGURE 8.1 The areas that make up a table
FIGURE 8.2 Use the Create Table dialog box to verify that Excel guessed the ...
FIGURE 8.3 Each column in a table has sorting and filtering options.
FIGURE 8.4 A table after performing a three-column sort
FIGURE 8.5 Using the Sort dialog box to specify a three-column sort
FIGURE 8.6 This table is filtered to show the information for only one area....
FIGURE 8.7 Specifying a more complex numeric filter
FIGURE 8.8 Use the Insert Slicers dialog box to specify which slicers to cre...
FIGURE 8.9 The table is filtered by two slicers.
FIGURE 8.10 Excel offers many different table styles.
FIGURE 8.11 Use this dialog box to create a new table style.
FIGURE 8.12 A simple table with three columns of information
FIGURE 8.13 A drop-down list enables you to select a summary formula for a t...
FIGURE 8.14 The Difference column contains a formula.
FIGURE 8.15 The formula AutoComplete feature is useful when creating a formu...
Chapter 9
FIGURE 9.1 Simple formatting can greatly improve the appearance of your work...
FIGURE 9.2 The Mini toolbar appears above or below the right-click context m...
FIGURE 9.3 The Font tab of the Format Cells dialog box gives you many additi...
FIGURE 9.4 You can choose many different font formatting options for your wo...
FIGURE 9.5 The full range of alignment options is available on the Alignment...
FIGURE 9.6 The same text, displayed with three types of horizontal alignment...
FIGURE 9.7 Merge worksheet cells to make them act as if they were a single c...
FIGURE 9.8 Rotate text for additional visual impact.
FIGURE 9.9 Use the Borders drop-down list to add lines around worksheet cell...
FIGURE 9.10 Use the Border tab of the Format Cells dialog box for more contr...
FIGURE 9.11 Excel displays samples of predefined cell styles.
FIGURE 9.12 Use the Style dialog box to modify named styles.
FIGURE 9.13 The elements in this worksheet use the default theme.
FIGURE 9.14 The worksheet after applying a different theme
FIGURE 9.15 Built-in Excel theme choices
FIGURE 9.16 Use this dialog box to specify two fonts for a theme.
FIGURE 9.17 If you're feeling creative, you can specify a set of custom colo...
Chapter 10
FIGURE 10.1 The length of the data bars is proportional to the track length ...
FIGURE 10.2 Two examples of color scale conditional formatting
FIGURE 10.3 Use the New Formatting Rule dialog box to customize a color scal...
FIGURE 10.4 Using an icon set to indicate the status of projects
FIGURE 10.5 Changing the icon assignment rule
FIGURE 10.6 Using a modified rule and eliminating an icon makes the table mo...
FIGURE 10.7 Creating a conditional formatting rule based on a formula
FIGURE 10.8 Highlighting a row, based on a matching name
FIGURE 10.9 Using conditional formatting to apply formatting to alternate ro...
FIGURE 10.10 Conditional formatting produces these groups of alternating sha...
Chapter 11
FIGURE 11.1 A simple column chart depicts the sales volume for each month.
FIGURE 11.2 This line chart displays two data series.
FIGURE 11.3 Activating a chart displays additional tabs on the E...
FIGURE 11.4 A chart on a chart sheet
FIGURE 11.5 Parts of a chart
FIGURE 11.6 A pie chart
FIGURE 11.7 A 3D column chart
FIGURE 11.8 This data would make a good chart.
FIGURE 11.9 The icons in the Charts group expand to show a gallery of chart ...
FIGURE 11.10 A column chart with two data series
FIGURE 11.11 The column chart, after swapping the row/column orientation
FIGURE 11.12 The Change Chart Type dialog box
FIGURE 11.13 One-click design variations of a column chart
FIGURE 11.14 Using the Format Data Series task pane
FIGURE 11.15 Use the Move Chart dialog box to move an embedded chart to a ch...
Chapter 12
FIGURE 12.1 The same data, plotted by using six chart types
FIGURE 12.2 This clustered column chart compares monthly sales for two produ...
FIGURE 12.3 This stacked column chart displays sales by product and depicts ...
FIGURE 12.4 This 100% stacked column chart displays monthly sales as a perce...
FIGURE 12.5 A 3D column chart
FIGURE 12.6 If you have lengthy category labels, a bar chart may be a good c...
FIGURE 12.7 A line chart often can help you spot trends in your data.
FIGURE 12.8 This line chart displays three series.
FIGURE 12.9 This 3D line chart does not present the data very well.
FIGURE 12.10 A pie chart with one slice exploded
FIGURE 12.11 A bar of pie chart that shows detail for one of the pie slices...
FIGURE 12.12 An XY chart shows the relationship between two variables.
FIGURE 12.13 A stacked area chart
FIGURE 12.14 This 3D area chart is not a good choice.
FIGURE 12.15 Plotting ski sales using a radar chart with 12 categories and t...
FIGURE 12.16 A surface chart
FIGURE 12.17 A bubble chart
FIGURE 12.18 The four stock chart subtypes
FIGURE 12.19 Plotting temperature data with a stock chart
FIGURE 12.20 Displaying a student grade distribution using a histogram chart...
FIGURE 12.21 A Pareto chart displays the number of complaints graphically.
FIGURE 12.22 A waterfall chart showing positive and negative net cash flows...
FIGURE 12.23 A box & whisker chart that summarizes data for four groups
FIGURE 12.24 A sunburst chart that depicts a music collection by genre and s...
FIGURE 12.25 A treemap chart that depicts a music collection by genre and su...
FIGURE 12.26 A funnel chart visualizing the value in each stage of a sales p...
FIGURE 12.27 Map charts are ideal for visualizing location-based data.
FIGURE 12.28 Map charts come with unique customization options.
Chapter 13
FIGURE 13.1 In Normal view, dotted lines indicate page breaks.
FIGURE 13.2 In Page Layout view, the worksheet resembles printed pages.
FIGURE 13.3 Page Break Preview allows you to drag page breaks and print area...
FIGURE 13.4 The Margins tab of the Page Setup dialog box
FIGURE 13.5 Use the Sheet tab of the Page Setup dialog box to specify rows o...
FIGURE 13.6 A header image can be used as a watermark.
FIGURE 13.7 This three-part header is one of Excel's predefined headers.
Guide
Cover
Table of Contents
Title Page
Begin Reading
Appendix: Excel Keyboard Shortcuts
Copyright
End User License Agreement
Pages
i
1
2
3
4
5
6
7
8
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
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
ii
217
Excel® Quick and Easy
Michael Alexander
Dick Kusleika
CHAPTER 1Introducing Excel
IN THIS CHAPTER
Understanding what Excel is used for
Learning the parts of an Excel window
Moving around a worksheet
This chapter is an introductory overview of Excel 365. Excel 365 runs on Windows, macOS, the web, iOS, iPadOS, and Android, though not all functions are available outside of Windows and macOS. If you're already familiar with a previous version of Excel, reading (or at least skimming) this chapter is still a good idea.
Understanding What Excel Is Used For
Excel is the world's most widely used spreadsheet software and is part of the Microsoft Office suite. Other spreadsheet software is available, but Excel is by far the most popular and has been the world standard for many years.
Much of the appeal of Excel is its versatility. Excel's forte, of course, is performing numerical calculations, but Excel is also useful for nonnumeric applications. Here are just a few uses for Excel:
Crunching numbers:
Create budgets, tabulate expenses, analyze survey results, and perform just about any type of financial analysis you can think of.
Creating charts:
Create a variety of highly customizable charts.
Organizing lists:
Use the row-and-column layout to store lists efficiently.
Manipulating text:
Clean up and standardize text-based data.
Accessing other data:
Import data from a variety of sources such as databases, text files, web pages, and many others.
Creating graphical dashboards:
Summarize a large amount of business information in a concise format.
Creating graphics and diagrams:
Use shapes and illustrations to create professional-looking diagrams.
Automating complex tasks:
Perform a tedious task with a single mouse click with Excel's macro capabilities.
Understanding Workbooks and Worksheets
An Excel file is called a workbook. You can have as many workbooks open as you need, and each one appears in its own window. By default, Excel workbooks use an .xlsx file extension.
NOTE
In old versions of Excel, every workbook opened in a single Excel window. Beginning with Excel 2013, each workbook opens in its own window. This change makes Excel work more like other Office applications and gives you the opportunity to put different workbooks on different monitors more easily.
The tabs in a workbook are called worksheets. Each workbook contains one or more worksheets, and each worksheet consists of individual cells. Each cell can contain a number, a formula, or text. A worksheet also has an invisible drawing layer, which holds charts, images, and diagrams. Objects on the drawing layer sit over the top of the cells, but they are not in the cells like a number or formula. You switch to a different worksheet by clicking its tab at the bottom of the workbook window. In addition, a workbook can store chart sheets; a chart sheet displays a single chart and is accessible by clicking a tab.
Don't be intimidated by all the different elements that appear within Excel's window. You don't need to know what all of them mean to use Excel effectively. And after you become familiar with the various parts, it all starts to make sense and you'll feel right at home.
Figure 1.1 shows you the more important bits and pieces of Excel. As you look at the figure, refer to Table 1.1 for a brief explanation of the items shown.
TABLE 1.1 Parts of the Excel screen that you need to know
Name
Description
Column letters
Letters range from A to XFD—one for each of the 16,384 columns in the worksheet. You can click a column heading to select an entire column or click between the column letters and drag to change the column width.
File button
Click this button to open Backstage view, which contains many options for working with your document (including printing) and setting Excel options.
Formula bar
When you enter information or formulas into a cell, it appears in this bar.
Horizontal scrollbar
Use this tool to scroll the sheet horizontally.
Macro recorder indicator
Click to start recording a Visual Basic for Applications (VBA) macro. The icon changes while your actions are being recorded. Click again to stop recording.
Name box
This box displays the active cell address or the name of the selected cell, range, or object.
New Sheet button
Add a new worksheet by clicking the New Sheet button (which is displayed after the last sheet tab).
Page view buttons
Click these buttons to change the way the worksheet is displayed.
Quick Access Toolbar
This customizable toolbar holds commonly used commands. The Quick Access Toolbar is always visible, regardless of which tab is selected.
Ribbon
This is the main location for Excel commands. Clicking an item in the tab list changes the Ribbon that is displayed.
Ribbon Display Options
A drop-down control that offers three options related to displaying the Ribbon.
Row numbers
Numbers range from 1 to 1,048,576—one for each row in the worksheet. You can click a row number to select an entire row or click between the row numbers and drag to change the row height.
Search box
Use this control to find commands or have Excel issue a command automatically. Alt+Q is the shortcut to access the Search box.
Selected cell indicator
This dark outline indicates the currently selected cell or range of cells. (There are 17,179,869,184 cells on each worksheet.)
Sheet tabs
Each of these notebook-like tabs represents a different sheet in the workbook. A workbook can have any number of sheets, and each sheet has its name displayed in a sheet tab.
Sheet tab controls
Use these buttons to scroll the sheet tabs to display tabs that aren't visible. You can also right-click to get a list of sheets.
Status bar
This bar displays various messages as well as summary information about the range of cells selected. Right-click the status bar to change which messages are displayed.
Tab list
Use these commands to display a different Ribbon.
Title bar
This displays the name of the program and the name of the current workbook. It also holds the Quick Access Toolbar (on the left), the Search box, and some control buttons that you can use to modify the window (on the right).
Vertical scrollbar
Use this tool to scroll the sheet vertically.
Window controls
There are three controls for minimizing the current window, maximizing or restoring the current window, and closing the current window, which are common to virtually all Windows applications.
Zoom control
Use this to zoom your worksheet in and out.
Moving Around a Worksheet
This section describes various ways to navigate the cells in a worksheet.
Every worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled A through XFD). Column labeling works like this: After column Z comes column AA, which is followed by AB, AC, and so on. After column AZ comes BA, BB, and so on. After column ZZ is AAA, AAB, and so on.
FIGURE 1.1 The Excel screen has many useful elements that you will use often.
The intersection of a row and a column is a single cell, and each cell has a unique address made up of its column letter and row number. For example, the address of the upper-left cell is A1. The address of the cell at the lower right of a worksheet is XFD1048576.
At any given time, one cell is the active cell. The active cell is the cell that accepts keyboard input, and its contents can be edited. You can identify the active cell by its darker border, as shown in Figure 1.2. If more than one cell is selected, the dark border surrounds the entire selection, and the active cell is the light-colored cell within the border. Its address appears in the Name box. Depending on the technique you use to navigate through a workbook, you may or may not change the active cell when you navigate.
The row and column headings of the active cell appear in a different color to make it easier to identify the row and column of the active cell.
FIGURE 1.2 The active cell is the one with the dark border—in this case, cell C11.
NOTE
Excel is also available for devices that use a touch interface. This book assumes you have a traditional keyboard and mouse, so it doesn't cover the touch-related commands. Note that the drop-down control in the Quick Access Toolbar has a Touch/Mouse Mode command. In Touch mode, the Ribbon and Quick Access Toolbar icons are placed farther apart.
Navigating With Your Keyboard
Not surprisingly, you can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you'd expect: The down arrow moves the active cell down one row, the right arrow moves it one column to the right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.)
TIP
You can use the keyboard to scroll through the worksheet without changing the active cell by turning on Scroll Lock, which is useful if you need to view another area of your worksheet and then quickly return to your original location. Just press Scroll Lock and use the navigation keys to scroll through the worksheet. When you want to return to the original position (the active cell), press Ctrl+Backspace and then press Scroll Lock again to turn it off. When Scroll Lock is turned on, Excel displays Scroll Lock in the status bar at the bottom of the window.
The Num Lock key on your keyboard controls the way the keys on the numeric keypad behave. When Num Lock is on, the keys on your numeric keypad generate numbers. Many keyboards have a separate set of navigation (arrow) keys located to the left of the numeric keypad. The state of the Num Lock key doesn't affect these keys.
Table 1.2 summarizes all the worksheet movement keys available in Excel.
TABLE 1.2 Excel worksheet movement keys
Key
Action
Up arrow (↑) or Shift+Enter
Moves the active cell up one row
Down arrow (↓) or Enter
Moves the active cell down one row
Left arrow (←) or Shift+Tab
Moves the active cell one column to the left
Right arrow (→) or Tab
Moves the active cell one column to the right
PgUp
Moves the active cell up one screen
PgDn
Moves the active cell down one screen
Alt+PgDn
Moves the active cell right one screen
Alt+PgUp
Moves the active cell left one screen
Ctrl+Backspace
Scrolls the screen so that the active cell is visible
Ctrl+Home
Moves the active cell to A1
Ctrl+End
Moves the active cell to the bottom-rightmost cell on the worksheet's used range
↑
*
Scrolls the screen up one row (active cell does not change)
↓
*
Scrolls the screen down one row (active cell does not change)
←
*
Scrolls the screen left one column (active cell does not change)
→
*
Scrolls the screen right one column (active cell does not change)
* With Scroll Lock on
Navigating With Your Mouse
To change the active cell by using the mouse, just click another cell and it becomes the active cell. If the cell that you want to activate isn't visible in the workbook window, you can use the scrollbars to scroll the window in any direction. To scroll one cell, click either of the arrows on the scrollbar. To scroll by a complete screen, click either side of the scrollbar's scroll box. To scroll faster, drag the scroll box or right-click anywhere on the scrollbar for a menu of shortcuts.
TIP
If your mouse has a wheel, you can use it to scroll vertically. Also, if you click the wheel and move the mouse in any direction, the worksheet scrolls automatically in that direction. The more you move the mouse, the faster you scroll.
Press Ctrl while you use the mouse wheel to zoom the worksheet. If you prefer to use the mouse wheel to zoom the worksheet without pressing Ctrl, choose File ➪ Options and select the Advanced section. Place a check mark next to the Zoom On Roll With IntelliMouse option.
Using the scrollbars or scrolling with your mouse doesn't change the active cell—it simply scrolls the worksheet. To change the active cell, you must click a new cell after scrolling.
CHAPTER 2Creating Your First Excel Workbook
IN THIS CHAPTER
Introducing Excel with a step-by-step hands-on session
This chapter presents an introductory, hands-on session with Excel. If you haven't used Excel, you may want to follow along on your computer to get a feel for how this software works.
In this example, you create a simple monthly sales projection table plus a chart that depicts the data.
Getting Started on Your Worksheet
Start Excel and make sure you have an empty workbook displayed. To create a new, blank workbook, press Ctrl+N (the shortcut key for File ➪ New ➪ Blank Workbook). Enter some sales projections in the new workbook.
The sales projections will consist of two columns of information. Column A will contain the month names, and column B will store the projected sales numbers. You start by entering some descriptive titles into the worksheet. Here's how to begin:
Select cell A1 (the upper-left cell in the worksheet) by using the navigation (arrow) keys, if necessary.
The Name box displays the cell's address.
Type Month into cell A1 and press Enter.
Depending on your setup, either Excel moves the selection to a different cell or the pointer remains in cell A1.
Select cell B1, type Projected Sales, and press Enter.
The text extends beyond the cell width, but don't worry about that for now.
Filling In the Month Names
In this step, you enter the month names in column A.
Select cell A2 and type Jan (an abbreviation for January).
At this point, you can enter the other month name abbreviations manually, or you can let Excel do some of the work by taking advantage of the AutoFill feature.
Make sure that cell A2 is selected.
Notice that the active cell is displayed with a heavy outline. At the bottom-right corner of the outline, you'll see a small square known as the
fill handle
. Move your mouse pointer over the fill handle, click, and drag down until you've highlighted from cell A2 down to cell A13.
Release the mouse button, and Excel automatically fills in the month names.
Your worksheet should resemble the one shown in Figure 2.1.
FIGURE 2.1 Your worksheet after you've entered the column headings and month names
Entering the Sales Data
Next, you provide the sales projection numbers in column B. Assume that January's sales are projected to be $50,000 and that sales will increase by 3.5 percent in each subsequent month.
Select cell B2 and type 50000, the projected sales for January.
You could type a dollar sign and comma to make the number more legible, but you'll do the number formatting a bit later.
To enter a formula to calculate the projected sales for February, move to cell B3 and type the following:
When you press Enter, the cell displays 51750. The formula returns the contents of cell B2, multiplied by 103.5 percent. In other words, February sales are projected to be 103.5 percent of the January sales—a 3.5 percent increase.
The projected sales for subsequent months use a similar formula, but rather than retype the formula for each cell in column B, take advantage of the AutoFill feature.
Make sure that cell B3 is selected. Click the cell's fill handle, drag down to cell B13, and release the mouse button.
TIP
If you double-click the fill handle instead of dragging it, Excel will try to guess how far down to fill. If the column to the left of the active cell has data in it, Excel will fill down as far as that column's data.
At this point, your worksheet should resemble the one shown in Figure 2.2. Keep in mind that, except for cell B2, the values in column B are calculated with formulas. To demonstrate, try changing the projected sales value for the initial month, January (in cell B2). You'll find that the formulas recalculate and return different values. All these formulas depend on the initial value in cell B2.
FIGURE 2.2 Your worksheet after you've created the formulas
Formatting the Numbers
The values in the worksheet are difficult to read because they aren't formatted. In this step, you apply a number format to make the numbers easier to read and more consistent in appearance.
Select the numbers by clicking cell B2 and dragging down to cell B13.
Don't drag the fill handle this time, though, because you're selecting cells, not filling a range.
Access the Ribbon and click Home.
In the Number group, click the drop-down Number Format control (it initially displays General), and select Currency from the list. The numbers now display with a currency symbol and two decimal places. That's much better, but the decimal places aren't necessary for this type of projection.
Make sure that the range B2:B13 is selected, choose Home
➪
Number, and click the Decrease Decimal button.
One of the decimal places disappears. Click that button a second time and the values are displayed with no decimal places.
Making Your Worksheet Look a Bit Fancier
At this point, you have a functional worksheet, but it could use some help in the appearance department. Converting this range to an “official” (and attractive) Excel table is a snap.
Activate any cell within the range A1:B13.
Choose Insert
➪
Tables
➪
Table.
Excel displays the Create Table dialog box to make sure that it guessed the range properly.
Click OK to close the Create Table dialog box.
Excel applies its default table formatting and displays its Table Design contextual tab.
Your worksheet should look like Figure 2.3.
FIGURE 2.3 Your worksheet after you've converted the range to a table
If you don't like the default table style, just select another one from the Table Design ➪ Table Styles group. Notice that you can get a preview of different table styles by moving your mouse over the Ribbon. When you find one you like, click it, and the style will be applied to your table.
See
Chapter 8
, “Working with Tables,” for more information on Excel tables.
Summing the Values
The worksheet displays the monthly projected sales, but what about the total projected sales for the year? Because this range is a table, it's simple.
Activate any cell in the table.
Choose Table Design
➪
Table Style Options
➪
Total Row.
Excel automatically adds a new row to the bottom of your table, including a formula that calculates the total of the Projected Sales column.
If you'd prefer to see a different summary formula (e.g., average), click cell B14 and choose a different summary formula from the drop-down list.
Creating a Chart
How about a chart that shows the projected sales for each month?
Activate any cell in the table.
Choose Insert
➪
Charts
➪
Recommended Charts.
Excel displays some suggested chart type options.
In the Insert Chart dialog box, click the second recommended chart (a column chart), and click OK.
Excel inserts the chart in the center of the window. To move the chart to another location, click its border and drag it.
Click the chart and choose a style using the Chart Design
➪
Chart Styles options.
Figure 2.4 shows the worksheet with a column chart. Your chart may look different, depending on the chart style you selected.
FIGURE 2.4 The table and chart
This workbook is available on this book's website at
www.wiley.com/go/excelquickandeasy
. The filename is
table and chart.xlsx
.
Printing Your Worksheet
Printing your worksheet is easy (assuming that you have a printer attached and that it works properly).
Make sure that the chart isn't selected.
If a chart is selected, the chart will print on a page by itself. To deselect the chart, just press Esc or click any cell.
To make use of Excel's handy Page Layout view, click the Page Layout button on the right side of the status bar.
Excel displays the worksheet page by page so that you can easily see how your printed output will look. In Page Layout view, you can tell immediately whether the chart is too wide to fit on one page. If the chart is too wide, click and drag a corner of the chart to resize it or just move the chart below the table of numbers. Click the Normal button to return to the default view.
When you're ready to print, choose File
➪
Print.
At this point, you can change some print settings. For example, you can choose to print in landscape rather than portrait orientation. Make the change, and you see the result in the preview window.
When you're satisfied, click the large Print button in the upper-left corner.
The page is printed, and you're returned to your workbook.
Saving Your Workbook
Until now, everything that you've done has occurred in your computer's memory. If the power should fail, all may be lost—unless Excel's AutoRecover feature happened to kick in. It's time to save your work to a file on your hard drive.
Click the Save button on the Quick Access Toolbar.
„Ich bin wirklich begeistert. Auch die Möglichkeit des zusätzlichen eReaders im Abo finde ich persönlich toll.”
Happy Booktime
„Die Auswahl von Legimi ist großartig.”
Bücherleser
„Der Leser findet seine E-Books/Hörbücher sehr schnell und sie lassen sich, ob mit oder ohne Internetverbindung problemlos öffnen.”
Wurm sucht Buch
„Ich finde das Angebot von Legimi richtig toll.”
TheSarahStory
„Besonders schön finde ich die große Auswahl an möglichen Abo-Modellen und besonders die Abos mit eReader.”
Miss Foxy Reads
„Ich muss sagen, dass ich von dem E-Reader mehr als positiv überrascht bin.”
Wortmalerei
„Das ist wirklich eine großartige Idee und mal was ganz Anderes.”
Mikka liest das Leben...
Tausende von E-Books und Hörbücher
Ihre Zahl wächst ständig und Sie haben eine Fixpreisgarantie.
Sie haben über uns geschrieben:
Dabei gewährt der E-Book-Anbieter größtmögliche Freiheiten
Größter Vorteil die Möglichkeit, in der aktuellen App komfortabel zwischen E-Book und Hörbuchversion eines Titels
Spotify for E-Books
