MS EXCEL course Training with examples


Télécharger MS EXCEL course Training with examples

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


 

Table of Contents

CHAPTER ONE . 8

1.  Excel Basics 8

1.1    Getting Started with Excel .. 8

1.1.1  Introduction 8

1.1.2  Getting to Know Excel 2013 .. 8

1.1.3  Backstage View .. 13

1.1.4  Worksheet Views .. 14

Exercise 1.1 .. 15

1.2    Creating and Opening Workbooks 16

1.2.1  Using Templates 20

1.2.2  Compatibility Mode . 22

Exercise 1.2 .. 24

1.3    Saving and Sharing Workbooks . 24

1.3.1  Exporting Workbooks . 28

1.3. 2 Sharing Workbooks . 32

Exercise 1.3 .. 32

1.4    Cell Basics .. 33

1.4.1  Cell Content . 35

1.4.2  Formatting Attributes . 36

1.4. 3. Formulas and Functions . 36

1. 4. 4 Inserting Content 37

1.4. 5 Deleting Cell Content . 37

1. 4. 6 Deleting Cells 38

1. 4. 7  Copy and Paste Cell Content 38

1. 4. 8 Cut and Paste Cell Content 40

1. 4. 9 Access More Paste Options .. 41

1. 4. 10 Drag and Drop Cells 42

1. 4. 11 Using the Fill Handle .. 43

1. 4. 12 Continuing a Series with the Fill Handle .. 44

1. 4. 13 Using Flash Fill .. 44

1. 4. 14 Find and Replace .. 46

Exercise 1.4 .. 50

1.5    Modifying Columns, Rows, and Cells .. 51

1.5.1  Modifying Column Width . 51

1.5.2  AutoFit Column Width 51

1.5. 3 Modifying Row Height .. 52

1.5.4 Modifying All Rows or Columns . 53

1.5.5. Inserting, Deleting, Moving, and Hiding Rows and Columns 54

1.5.6 Wrapping Text and Merging Cells 63

Exercise 1.5 .. 66

1.6    Formatting Cells . 67

1. 6.1 Changing the Font .. 67

1. 6.2 Changing the Font Size . 68

1. 6. 3 Changing the Font Color . 69

1. 6.4 Using the Bold, Italic, and Underline Commands 70

1. 6. 5 Text Alignment . 71

1. 6.6. Cell Borders and Fill Colors 72

1. 6.7 Cell Styles . 74

1. 6.8 Formatting Text and Numbers . 75

Exercise 1.6 .. 77

1.7    Worksheet Basics . 77

1.7.1  Renaming a Worksheet . 77

1.7.2  Inserting a New Worksheet . 78

1.7.3  Deleting a Worksheet . 79

1.7. 4 Copying a Worksheet . 81

1.7. 5 Moving a Worksheet . 82

1.7.6  Changing the Worksheet Color . 82

1.7.7  Grouping and Ungrouping Worksheets . 83

Exercise 1.7 .. 85

1.8    Page Layout . 85

1.8.1  Page Layout View . 85

1.8.2  Page Orientation .. 85

1.8.4  Formatting Page Margins . 86

1.8.5  Using Custom Margins 87

1.8.6  Including Print Titles 88

1.8.7  Inserting a Page Break 90

1.8.8  Inserting Headers & Footers 92

Exercise 1.8 .. 93

1.9    Printing Workbooks . 94

1.9.1  Accessing the Print Pane .. 94

1.9.2  Printing a Workbook 95

1.9. 3 Choosing a Print Area 96

1.9. 4 To Print Active Sheets: .. 96

1.9. 5 Printing the Entire Workbook 97

1.9.6  Printing a Selection .. 97

1.9.7  Fitting and Scaling Content .. 99

1.9.8  Modifying Margins in the Preview Pane 102

Exercise 1.9 103 CHAPTER TWO 104

2.  Formulas and Functions .. 104

2.1    Simple Formulas .. 104

2.1.1  Introduction .. 104

2.1.2  Mathematical Operators 104

2.1.3  Understanding Cell References .. 104

2.1.4  Creating a Formula 105

2.1.5  Modifying Values with Cell References .. 107

2.1.6  Create a Formula Using the Point and Click Method .. 107

2.1.7  Editing a Formula 110

Exercise 2.1 112

2.2    Complex Formulas .. 112

2.2.1  Order of Operations . 112

2.2.2  Creating Complex Formulas . 113 2.2.3 Creating a Complex Formula Using the Order of Operations .114

Exercise 2.2 115

2.3    Relative and Absolute Cell References .115

2.3.1  Relative References ..115

2.3.2  Creating and Copying a Formula Using Relative References 115

2.3.3  Absolute References .119

2.3.4  Creating and Copying a Formula Using Absolute References .119

2.3.5  Using Cell References with Multiple Worksheets .123

2.3.6  Referencing Cells Across Worksheets .123

Exercise 2.3 125

2. 4 Functions 126

2. 4.1 The Parts of a Function .. 126

2.4.2  Working with Arguments .. 126

2.4.3  Creating a Function .. 127

2. 4.4 Creating a Basic Function . 127

2. 4.6 The Function Library 131

2. 4.7 Inserting a Function from the Function Library . 132

2.4.8 The Insert Function Command 136

2. 4.9 Use the Insert Function Command .. 136

Exercise 2.4 139 CHAPTER THREE 140

3.  Working with Data .. 140

3.1    Freezing Panes and View Options .. 140

3.1.1  Freezing Rows .. 140

3.1.2  Freezing Columns .. 141

3.1. 3 Other View Options . 143

Exercise 3.1 146

3.2    Sorting Data 147

3.2.1  Types of Sorting .. 147

3.2.2  Sorting Levels 157

Exercise 3.2 159

3.3    Filtering Data . 160

3.3.1  Filtering Data 160

3.3.2  Apply Multiple Filters ..163

3.3.3  Clearing a Filter 164

3.3.4  Advanced Filtering .166

Exercise 3.3 173

3. 4 Groups and Subtotals ..173

3. 4.1 Grouping Rows or Columns .173

3.4.2 Hide and Show Groups 174

3.4    3 Creating Subtotals 175

3. 4.4 Viewing Groups by Level ..178

3. 4.5 Remove Subtotals . 179

Exercise 3.4 180

3.5    Tables . 180

3.5.1  Formatting Data as a Table 181

3.5.2  Modifying Tables 183

3.5. 3 Removing a Table . 186

Exercise 3.5 188

3.6    Charts . 188

3.6.1  Understanding Charts .. 188

3.6.2  Inserting a Chart . 189

3.6.3  Chart Layout and Style 191

3.6.4  Other Chart Options . 193

Exercise 3.6 198

3.7    Sparklines 198

3.7.1  Types of Sparklines 198

3.7.2  Why Use Sparklines? 199

3.7.3  Creating Sparklines 199

3.7.4  Modifying Sparklines 202

3.7.5  Changing the Display Range . 206

Exercise 3.7 207 CHAPTER FOUR .. 208

4.  Doing More with Excel . 208

4.1    Track Changes and Comments . 208

4.1.1  Introduction .. 208

4.1.2  Understanding Track Changes .208

4.1.3  Turning on Track Changes .209

4.1. 4 Listing Changes on a Separate Worksheet ..210

4.1.5  Reviewing Changes 211

4.1.6  Comments .213

Exercise 4.1 219

4.2    Finalizing and Protecting Workbooks 219

4.2.1  Using Spell Check 219

4.2.2  Ignoring Spelling "Errors" ..220

4.2.3  Document Inspector . 221

4.2.4  Protecting Your Workbook 223

Exercise 4.2 225

4.3    Conditional Formatting 225

4.3.1  Understanding Conditional Formatting .. 225

4.3.2  Creating a Conditional Formatting Rule . 226

4.3.3  Removing Conditional Formatting 228

4.3.4  Conditional Formatting Presets .. 230

4.3. 5 Using Preset Conditional Formatting . 231

Exercise 4.3 233

4.4    Pivot Tables 233

4.4.1  Using PivotTables to Answer Questions . 233

4.4.2  Create a PivotTable .. 234

4.4. 3 Pivoting Data .. 239

4.4.4 Filters 242

4.4.4.1 Adding a Filter . 242

4.4.5  Slicers 244

4.4.6  Pivot Charts .. 246

Exercise 4.4 250

4.5    What-If Analysis 251

4.5.1  Goal Seek 251

4.5.2  Other Types of What-If Analysis . 256

Exercise 4.5 257

CHAPTER ONE

1. Excel Basics

1.1 Getting Started with Excel

1.1.1 Introduction

Excel 2013 is a spreadsheet program that allows you to store, organize, and analyze information. While you may think that Excel is only used by certain people to process complicated data, anyone can learn how to take advantage of Excel's powerful features. Whether you're keeping a budget, organizing a training log, or creating an invoice, Excel makes it easy to work with different kinds of data.

1.1.2 Getting to Know Excel 2013

Excel 2013 is very similar to Excel 2010. If you've previously used Excel 2010, Excel 2013 should feel very familiar. But if you are new to Excel, or have more experience with older versions, you should first take some time to become familiar with the Excel 2013 interface.

1.1.2.1 The Excel Interface

When you open Excel 2013 for the first time, the Excel Start Screen will appear. From here, you'll be able to create a new workbook, choose a template, or access your recently edited workbooks.

                ?    From the Excel Start Screen, locate and select Blank workbook to access the Excel interface. 

The Excel Start Screen

Click the buttons in the interactive below to become familiar with the Excel 2013 interface.

1.1.2.2 Working with the Excel Environment

If you've previously used Excel 2010 or 2007, Excel 2013 will feel very familiar. It continues to use features like the Ribbon and the Quick Access Toolbar, where you will find commands to perform common tasks in Excel, as well as Backstage view.

1.1.2.3 The Ribbon

Excel 2013 uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs, each with several groups of commands. You will use these tabs to perform the most common tasks in Excel.

Click the arrows in the slideshow below to learn more about the different commands available within each tab on the Ribbon.

                     ?                                                                                 

 

Ribbon Display options

2. Select the desired minimizing option from the drop-down menu:

o    Auto-hide Ribbon: Auto-hide displays your workbook in full-screen mode and completely hides the Ribbon. To show the Ribbon, click the Expand Ribbon command at the top of screen. 

Auto-hiding the Ribbon

o    Show Tabs: This option hides all command groups when not in use, but tabs will remain visible. To show the Ribbon, simply click a tab.

Showing only Ribbon tabs

o    Show Tabs and Commands: This option maximizes the Ribbon. All of the tabs and commands will be visible. This option is selected by default when you open Excel for the first time.

1.1.2.4 The Quick Access Toolbar

Located just above the Ribbon, the Quick Access Toolbar lets you access common commands no matter which tab is selected. By default, it includes the Save, Undo, and Repeat commands. You can add other commands depending on your preference.

To Add Commands to the Quick Access Toolbar:

1.    Click the drop-down arrow to the right of the Quick Access Toolbar.

2.    Select the command you wish to add from the drop-down menu. To choose from more commands, select More Commands.

Adding a command to the Quick Access Toolbar 3. The command will be added to the Quick Access Toolbar.

The added command

1.1.3 Backstage View

 

Clicking the File tab

Click the buttons in the interactive below to learn more about using Backstage view.

1.1.4 Worksheet Views

Excel 2013 has a variety of viewing options that change how your workbook is displayed. You can choose to view any workbook in Normal view, Page Layout view, or Page Break view. These views can be useful for various tasks, especially if you're planning to print the spreadsheet.

? To change worksheet views, locate and select the desired worksheet view command in the bottom-right corner of the Excel window.

Worksheet view options

Click the arrows in the slideshow below to review the different worksheet view options.

Exercise 1.1

1.    Open or navigate to the Excel 2013 interface.

2.    Click through all of the tabs and review the commands on the Ribbon.

3.    Try minimizing and maximizing the Ribbon.

4.    Add a command to the Quick Access Toolbar.

5.    Navigate to Backstage view and open your Account settings.

6.    Try switching worksheet views.

7.    Close Excel (you do not have to save the workbook).

1.2 Creating and Opening Workbooks

Excel files are called workbooks. Whenever you start a new project in Excel, you'll need to create a new workbook. There are several ways to start working with a workbook in Excel 2013. You can choose to create a new workbook—either with a blank workbook or a pre-designed template or open an existing workbook. Creating a New, Blank Workbook

1.    Select the File tab. Backstage view will appear.

2.    Select New and then click Blank workbook.

 

Creating a new workbook

3.    A new, blank workbook will appear.

Opening an Existing Workbook

An existing workbook is a workbook that has been previously saved..

1.    Navigate to Backstage view and then click Open.

2.    Select a location option: o   Recent Workbooks displays all of your recently edited workbooks, including those saved to Sky Drive.

 

Viewing recently edited workbooks

o    Sky Drive gives you access to your Sky Drive folders. Select Sky Drive and then click Browse if you've saved your workbook to the cloud. 

 

Opening a workbook from SkyDrive

o    Computer gives you access to the files you've saved locally on your computer. In our example, we will select this option and then click Browse.

 

Opening a locally saved workbook

3.    The Open dialog box will appear. Locate and select your workbook and then click Open.

 

                                                                                                               Opening a workbook      

Pining a Workbook

If you frequently work with the same workbook, you can pin it to Backstage view for quick access.

1.    Navigate to Backstage view.

2.    Click Open. Your recently edited workbooks will appear.

 

Viewing recently edited workbooks

3.    Hover the mouse over the workbook you wish to pin. A pushpin icon  will appear next to the workbook.

4.    Click the pushpin icon.

Pinning a workbook

5.    The workbook will appear in Recent Workbooks until it is unpinned.

The pinned workbook To unpin a workbook, simply click the pushpin icon again.

You can also pin folders to Backstage view for quick access. From Backstage view, click Open and then locate the folder you wish to pin, then click the pushpin icon.

Pinning a folder to Backstage view

1.2.1 Using Templates

 

Navigating to Backstage view 2. Select New. Several templates will appear below the Blank workbook option.

3.    Select a template to review it.

 

Selecting a template

4.    A preview of the template will appear, along with additional information about how the template can be used.

5.    Click Create to use the selected template.

 

Creating a new workbook with a template

6.    A new workbook will appear with the selected template

 

You can also browse templates by category or use the search bar to find something more specific.

 

Searching for a template

 

It's important to note that not all templates are created by Microsoft. Many are created by third-party providers and even individual users, so some templates may work better than others.

1.2.2 Compatibility Mode

Sometimes, you may need to work with workbooks that were created in earlier versions of Microsoft Excel, such as Excel 2003 or Excel 2000. When you open these kinds of workbooks, they will appear in Compatibility mode. Compatibility mode disables certain features, so you'll only be able to access commands found in the program used to create the workbook. For example, if you open a workbook created in Excel 2003, you can only use tabs and commands found in Excel 2003.

In the image below, you can see that the workbook is in Compatibility mode. This will disable some Excel 2013 features, such as sparklines and slicers.

Disabled commands in Compatibility mode

In order to exit Compatibility mode, you'll need to convert the workbook to the current version type. However, if you're collaborating with others who only have access to an earlier version of Excel, it's best to leave the workbook in Compatibility mode so that the format will not change.

To Convert a Workbook:

If you want access to all of the Excel 2013 features, you can convert the workbook to the 2013 file format.

Note that converting a file may cause some changes to the original layout of the workbook.

                1.   Click the File tab to access Backstage view.

Clicking the File tab 2. Locate and select Convert command.

Converting the workbook to the newest file type

3.    The Save As dialog box will appear. Select the location where you wish to save the workbook, enter a file name for the presentation and click Save.

 

Saving a new version of the workbook

4.    The workbook will be converted to the newest file type.

Exercise 1.2

1.    Create a new, blank workbook.

2.    Open an existing workbook from your computer.

3.    Pin a folder to Backstage view.

4.    Create a new workbook using a template.

1.3. Saving and Sharing Workbooks

Whenever you create a new workbook in Excel, you'll need to know how to save it in order to access and edit it later. As in previous versions of Excel, you can save files locally to your computer. But unlike older versions, Excel 2013 also lets you save a workbook to the cloud using SkyDrive. You can also export and share workbooks with others directly from Excel.

Save and Save As

Excel offers two ways to save a file: Save and Save As. These options work in similar ways, with a few important differences:

•    Save: When you create or edit a workbook, you'll use the Save command to save your changes. You'll use this command most of the time. When you save a file, you'll only need to choose a file name and location the first time. After that, you can just click the Save command to save it with the same name and location.

•    Save As: You'll use this command to create a copy of a workbook while keeping the original. When you use Save As, you'll need to choose a different name and/or location for the copied version.

To Save a Workbook:

It's important to save your workbook whenever you start a new project or make changes to an existing one. Saving early and often can prevent your work from being lost. You'll also need to pay close attention to where you save the workbook so it will be easy to find later.

1.    Locate and select the Save command on the Quick Access Toolbar.

Clicking the Save command

2.    If you're saving the file for the first time, the Save As pane will appear in Backstage view.

3.    You'll then need to choose where to save the file and give it a file name. To save the workbook to your computer, select Computer and then click Browse. Alternatively, you can click SkyDrive to save the file to your SkyDrive.

 

Saving a workbook locally

4.    The Save As dialog box will appear. Select the location where you wish to save the workbook.

5.    Enter a file name for the workbook and click Save.

Saving a workbook

6.    The workbook will be saved. You can click the Save command again to save your changes as you modify the workbook.

You can also access the Save command by pressing Ctrl+S on your keyboard.

Using Save As to Make a Copy

If you want to save a different version of a workbook while keeping the original, you can create a copy. For example, if you have a file named "Sales Data" you could save it as "Sales Data 2" so that you'll be able to edit the new file and still refer back to the original version.

To do this, you'll click the Save As command in Backstage View. Just like when saving a file for the first time, you'll need to choose where to save the file and give it a new file name.

Clicking Save As

To Change the Default Save Location:

If you don't want to use SkyDrive, you may be frustrated that SkyDrive is selected as the default location when saving. If you find it inconvenient to select Computer each time, you can change the default save location so that Computer is selected by default.

1. Click the File tab to access Backstage view.

Clicking the File tab 2. Click Options.

Clicking Options

3. The Excel Options dialog box will appear. Select Save, check the box next to Save to Computer by default, and then click OK. The default save location will be changed.

 

Changing the default save location

AutoRecover

Excel automatically saves your workbooks to a temporary folder while you are working on them. If you forget to save your changes, or if Excel crashes, you can restore the file using AutoRecover.

To Use AutoRecover:

1.    Open Excel 2013. If auto-saved versions of a file are found, the Document Recovery pane will appear. 

2.    Click to open an available file. The workbook will be recovered.

The Document Recovery pane

By default, Excel auto-saves every 10 minutes. If you are editing a workbook for less than 10 minutes, Excel may not create an auto-saved version.

 

If you don't see the file you need, you can browse all auto-saved files from Backstage view. Just select the File tab, click Manage Versions and then choose Recover Unsaved Presentations

Accessing all auto-saved files

1.3.1 Exporting Workbooks

By default, Excel workbooks are saved in the .xlsx file type. However, there may be times when you need to use another file type, such as a PDF or Excel 97-2003 workbook. It's easy to export your workbook from Excel in a variety of file types.

To Export a Workbook as a PDF File:

Exporting your workbook as an Adobe Acrobat Document, commonly known as a PDF file, can be especially useful if sharing a workbook with someone who does not have Excel. A PDF file will make it possible for recipients to view, but not edit, the content of your workbook.

1.    Click the File tab to access Backstage view.

2.    Click Export and then select Create PDF/XPS.

 

Exporting a PDF file

3.    The Save As dialog box will appear. Select the location where you wish to export the workbook, enter a file name, then click Publish.

   

To Export a Workbook in Other File Types:

You may also find it helpful to export your workbook in other file types, such as an Excel 97-2003 Workbook if you need to share with people using an older version of Excel, or a .CSV file if you need a plain-text version of your workbook.

1.    Click the File tab to access Backstage view.

2.    Click Export and then select Change File Type.

                          Clicking Change File Type 3. Select a common file type and then click Save As.

Choosing another file type

4. The Save As dialog box will appear. Select the location where you wish to export the workbook, enter a file name, then click Save.

 

Saving as an Excel 97-2003 workbook

 

You can also use the Save as type: drop-down menu in the Save As dialog box to save workbooks in a variety of file types.

Using the Save As type menu to choose a file type

1.3. 2 Sharing Workbooks

 

2. The Share pane will appear.

Click the buttons in the interactive below to learn more about different ways to share a workbook.

Exercise 1.3

1.    Create a new, blank workbook.

2.    Use the Save command to save the workbook to your Desktop.

3.    Save the workbook to SkyDrive and invite someone else to view it.

4.    Export the workbook as a PDF file.

1.4 Cell Basics

Whenever you work with Excel, you'll enter information, or content, into cells.  Cells are the basic building blocks of a worksheet. You'll need to learn the basics of cells and cell content to calculate, analyze, and organize data in Excel.

Understanding Cells

Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column. Columns are identified by letters (A, B, C) and rows are identified by numbers (1, 2, 3).

A cell

Every cell has its own name, or cell address, based on its column and row. In this example, the selected cell intersects column C and row 5, so the cell address is C5. The cell address will also appear in the Name box. Note that a cell's column and row headings are highlighted when the cell is selected.

Cell C5

You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4 and A5 would be written as A1:A5.

Cell range A1:A8

Cell range A1:B8

Selecting Cells

To Select a Cell:

To input or edit cell content, you'll first need to select the cell.

1.    Click a cell to select it.

2.    A border  will appear around the selected cell and the column heading and row heading will be highlighted. The cell will remain selected until you click another cell in the worksheet.

Selecting a single cell You can also select cells using the arrow keys on your keyboard

To Select a Cell Range:

Sometimes you may want to select a larger group of cells, or cell range.

1.    Click, hold and drag the mouse until all of the adjoining cells you wish to select are highlighted.

2.    Release the mouse to select the desired cell range. The cells will remain selected until you click another cell in the worksheet.

Selecting a cell range

1.4.1 Cell Content

Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain several different kinds of content, including text, formatting, formulas and functions.  

                ?    Text

Cells can contain text, such as letters, numbers, and dates.

Cell text

1.4.2 Formatting Attributes

Cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.15 or 15%. You can even change a cell's background color.

Cell formatting

1.4. 3. Formulas and Functions

Cells can contain formulas and functions that calculate cell values. In our example, SUM (B2:B8) adds the value of each cell in cell range B2:B8 and displays the total in cell C9.

Cell formulas

  1. 4. 4 Inserting Content

1.    Click a cell to select it.

Selecting cell A1

2.    Type content into the selected cell and then press Enter on your keyboard. The content will appear in the cell and the formula bar. You can also input and edit cell content in the formula bar.

Inserting cell content

1.4. 5 Deleting Cell Content

1.    Select the cell with content you wish to delete.

Selecting a cell 

2.    Press the Delete or Backspace key on your keyboard. The cell's contents will be deleted. 

Deleting cell content

You can use the Delete key on your keyboard to delete content from multiple cells at once. The Backspace key will only delete one cell at a time.

1. 4. 6 Deleting Cells

There is an important difference between deleting the content of a cell and deleting the cell itself. If you delete the entire cell, the cells below it will shift up and replace the deleted cells.

1. Select the cell(s) you wish to delete.

Selecting a cell to delete 2. Select the Delete command from the Home tab on the Ribbon

Clicking the Delete command 3. The cells below will shift up.

Cells shifted to replace the deleted cell

1. 4. 7  Copy and Paste Cell Content

Excel allows you to copy content that is already entered into your spreadsheet and paste that content to other cells, which can save you time and effort.

1.    Select the cell(s) you wish to copy.

Selecting a cell to copy

2.    Click the Copy command on the Home tab or press Ctrl+C on your keyboard.

Clicking the Copy command

3.    Select the cell(s) where you wish to paste the content. The copied cells will now have a dashed box around them.

Pasting cells

4.    Click the Paste command on the Home tab or press Ctrl+V on your keyboard.

Clicking the Paste command 5. The content will be pasted into the selected cells.

T

he pasted cell content

1. 4. 8 Cut and Paste Cell Content

Unlike copying and pasting, which duplicates cell content, cutting allows you to move content between cells.

1. Select the cell(s) you wish to cut.

Selecting a cell range to cut 2. Click the Cut command on the Home tab or press Ctrl+X on your keyboard.

Clicking the Cut command

3. Select the cells where you wish to paste the content. The cut cells will now have a dashed box around them.

4.  

Pasting cells

5.    Click the Paste command on the Home tab or press Ctrl+V on your keyboard.

Clicking the Paste command

6.    The cut content will be removed from the original cells and pasted into the selected cells.

The cut and pasted cells

1. 4. 9 Access More Paste Options

You can also access additional Paste options, which are especially convenient when working with cells that contain formulas or formatting.

                ?    To access more Paste options, click the drop-down arrow on the Paste command.

 
 

Rather than choosing commands from the Ribbon, you can also access commands quickly by right-clicking. Simply select the cell(s) you wish to format, then right-click the mouse. A drop-down menu will appear; where you'll find several commands also located on the Ribbon.

Right-clicking to access formatting options

1. 4. 10 Drag and Drop Cells

Rather than cutting, copying and pasting, you can also drag and drop cells to move their contents.

1.    Select the cell(s) that you wish to move.

2.    Hover the mouse over the border of the selected cell(s) until the cursor changes from a white cross  to a black cross with 4 arrows

Hovering over the cell border

3.    Click, hold and drag the cells to the desired location.

Dragging the selected cells

4.    Release the mouse and the cells will be dropped in the selected location.

The dropped cells

1. 4. 11 Using the Fill Handle

There may be times when you need to copy the content of one cell to several other cells in your worksheet. You could copy and paste the content into each cell, but this method would be very time consuming. Instead, you can use the fill handle to quickly copy and paste content to adjacent cells in the same row or column.

1.    Select the cell(s) containing the content you wish to use. The fill handle will appear as a small square in the bottom-right corner of the selected cell(s).

Locating the fill handle

2.    Click, hold and drag the Fill handle until all the cells you wish to fill are selected.

Dragging the fill handle 3. Release the mouse to fill the selected cells.

The filled cells

1. 4. 12 Continuing a Series with the Fill Handle

The fill handle can also be used to continue a series. Whenever the content of a row or column follows a sequential order, like numbers (1,2,3) or days (Monday, Tuesday, Wednesday), the fill handle can guess what should come next in the series. In many cases, you may need to select multiple cells before using the fill handle to help Excel determine the series order. In our example below, the Fill handle is used to extend a series of dates in a column.

Using the fill handle to extend a series

The extended series

1. 4. 13 Using Flash Fill

A new feature in Excel 2013, Flash Fill can enter data automatically into your worksheet, saving you a lot of time and effort. Just like the Fill handle, Flash Fill can guess what kind of information you're entering into your worksheet. In the example below, we'll use Flash Fill to create a list of first names using a list of existing email addresses.

1. Enter the desired information into your worksheet. A Flash Fill preview will appear below the selected cell whenever Flash Fill is available.

Previewing Flash Fill data 2. Press Enter. The Flash Fill data will be added to the worksheet.

The entered Flash Fill data

 

To modify or undo Flash Fill, click the Flash Fill button next to recently added Flash Fill data.

Clicking the Flash Fill button

1. 4. 14 Find and Replace

When working with a lot of data in Excel, it can be difficult and time consuming to locate specific information. You can easily search your workbook using the Find feature, which also allows you to modify content using the Replace feature.

1. 4. 14.1 Finding Content

In our example, we'll use the Find command to locate a specific name in a long list of employees.

1.    From the Home tab, click the Find and Select command, then select Find from the drop-down menu.

Clicking the Find command

2.    The Find and Replace dialog box will appear. Enter the content you wish to find. In our example, we'll type the employee's name.

3.    Click Find Next. If the content is found, the cell containing that content will be selected.

 

Clicking Find Next

4.    Click Find Next to find further instances or Find All to see every instance of the search term.

Clicking Find All

5.    When you are finished, click Close to exit the Find and Replace dialog box.

Closing the Find and Replace dialog box

 

You can also access the Find command by pressing Ctrl+F on your keyboard.

Click Options to see advanced search criteria in the Find and Replace dialog box.

Clicking Options

1. 4. 14.2 Replacing Cell Content

At times, you may discover that you've repeatedly made a mistake throughout your workbook (such as misspelling someone's name), or that you need to exchange a particular word or phrase for another. You can use Excel's Find and Replace feature to make quick revisions. In our example, we'll use Find and Replace to correct a list of email addresses.

1.    From the Home tab, click the Find and Select command, then select Replace from the drop-down menu.

Clicking the Replace command

2.    The Find and Replace dialog box will appear. Type the text you wish to find in the Find what: field.

3.    Type the text you wish to replace it with in the Replace with: field, then click Find Next.

Clicking Find Next

4.    If the content is found, the cell containing that content will be selected.

5.    Review the text to make sure you want to replace it.

6.    If you wish to replace it, select one of the replace options: o Replace will replace individual instances. o Replace All will replace every instance of the text throughout the workbook. In our example, we'll choose this option to save time.

Replacing the highlighted text

7.    A dialog box will appear, confirming the number of replacements made. Click OK to continue.

Clicking OK 8. The selected cell content will be replaced.

The replaced content

9. When you are finished, click Close to exit the Find and Replace dialog box.

Closing the Find and Replace dialog box

Exercise 1.4

1.    Open an existing Excel 2013 workbook. 

2.    Select cell D3. Notice how the cell address appears in the Name box and its content appears in both the cell and the Formula bar.

3.    Select a cell and try inserting text and numbers.

4.    Delete a cell and note how the cells below shift up to fill in its place.

5.    Cut cells and paste them into a different location. If you are using the example, cut cells D4:D6 and paste them to E4:E6.

6.    Try dragging and dropping some cells to other parts of the worksheet.

7.    Use the Fill handle to fill in data to adjoining cells both vertically and horizontally. If you are using the example, use the Fill handle to continue the series of dates across row 3.

8.    Use the Find feature to locate content in your workbook. If you are using the example, type the name "Lewis" into the Find what: field.

1.5. Modifying Columns, Rows, and Cells 

By default, every row and column of a new workbook is always set to the same height and width. Excel allows you to modify column width and row height in a variety of different ways, including wrapping text and merging cells.

1.5.1 Modifying Column Width

In our example below, some of the content in column A cannot be displayed. We can make all of this content visible by changing the width of column A.

1.    Position the mouse over the column line in the column heading so that the white cross  becomes a double arrow.

Hovering over the column line

2.    Click, hold and drag the mouse to increase or decrease the column width.

Increasing the column width 3. Release the mouse. The column width will be changed.

The new column width

1.5.2 AutoFit Column Width

The AutoFit feature will allow you to set a column's width to fit its content automatically.

1.    Position the mouse over the column line in the column heading so that the white cross  becomes a double arrow

Hovering the mouse over the column line

2.    Double-click the mouse. The column width will be changed automatically to fit the content.

The automatically sized column

You can also AutoFit the width for several columns at the same time. Simply select the columns you would like to AutoFit and then select the AutoFit Column Width command from the Format drop-down menu on the Hometab. This method can also be used for Row height.

AutoFitting columns width with the Format command

1.5. 3 Modifying Row Height

.

Hovering the mouse over the row line 2. Click, hold and drag the mouse to increase or decrease the row height.

Increasing the row height 3. Release the mouse. The height of the selected row will be changed.

The new row height

1.5.4 Modifying All Rows or Columns

Rather than resizing rows and columns individually, you can also modify the height and width of every row and column at the same time. This method allows you to set a uniform size for every row and column in your worksheet. In our example, we will set a uniform row height.

1.    Locate and click the Select All button

Selecting every cell in a worksheet

2.    Position the mouse over a row line so that the white cross     becomes a double arrow         .

3.    Click, hold and drag the mouse to increase or decrease the row height.

Modifying the height of all rows

4.    Release the mouse when you are satisfied with the new row height for the worksheet.

The uniform row height

1.5.5. Inserting, Deleting, Moving, and Hiding Rows and Columns

After you've been working with a workbook for a while, you may find that you want to insert new columns or rows; delete certain rows or columns, move them to a different location in the worksheet, or even hide them.

1.5.6.1 Inserting Rows

1. Select the row heading below where you want the new row to appear. For example, if you want to insert a row between rows 7 and 8, select row 8.

Selecting a row 2. Click the Insert command on the Home tab.

Clicking the Insert command 3. The new row will appear above the selected row.

T he new row

 

When inserting new rows, columns, or cells, you will see the Insert Options button  next to the inserted cells. This button allows you to choose how Excel formats these cells. By default, Excel formats inserted rows with the same formatting as the cells in the row above. To access more options, hover your mouse over the Insert Options button and then click the drop-down arrow.

The Insert Options button

1.5.5.2 Inserting Columns

1. Select the column heading to the right of where you want the new column to appear. For example, if you want to insert a column between columns D and E, select column E.

Selecting a column 2. Click the Insert command on the Home tab.

Clicking the Insert command 3. The new column will appear to the left of the selected column.

The new column

 

Clicking the Delete command

3. The selected row(s) will be deleted and the rows below will shift up. In our example, rows 9-11 are nowrows 6-8.

Rows 9-11 shifted up to replace rows 6-8

1.5.5. 4 Deleting Columns

1. Select the columns(s) you want to delete. In our example, we'll select column E.

Selecting a column to delete 2. Click the Delete command on the Home tab.

Clicking the Delete command

3. The selected columns(s) will be deleted and the columns to the right will shift left. In our example, Column Fis now Column E.

Column F shifted right to replace column E

 

It's important to understand the difference between deleting a row or column and simply clearing its contents. If you want to remove the content of a row or column without causing others to shift, right-click a heading and then select Clear Contents from the drop-down menu.

Clearing the contents from several rows

1.5.5. 5 Deleting Rows/Columns to Move a Row or Column

Sometimes you may want to move a column or row to rearrange the content of your worksheet. In our example, we will move a column, but you can move a row in the same way.

1.    Select the desired column heading for the column you wish to move, then click the Cut command on the Home tab or press Ctrl+X on your keyboard.

Cutting an entire column

2.    Select the column heading to the right of where you want to move the column. For example, if you want to move a column between columns B and C, select column C. 

 

Choosing a destination for the column

3.    Click the Insert command on the Home tab and then select Insert Cut Cells from the drop-down menu.

Inserting the column

4.    The column will be moved to the selected location and the columns to the right will shift right.

The moved column

 

You can also access the Cut and Insert commands by right-clicking the mouse and then selecting the desired commands from the drop-down menu.

Right-clicking to Insert Cut Cells

1.5.5. 6 Hiding and Un hiding a Row or Column 

At times, you may want to compare certain rows or columns without changing the organization of your worksheet. Excel allows you to hide rows and columns as needed. In our example, we'll hide columns C and D to make it easier to compare columns A, B and E.

1.    Select the column(s) you wish to hide, right-click the mouse and then select Hide from the formatting menu.

Hiding the selected columns

2.    The columns will be hidden. The green column line indicates the location of the hidden columns.

The hidden columns

3.    To unhide the columns, select the columns to the left and right of the hidden columns (in other words, the columns on both sides of the hidden columns). In our example, we'll select columns B and E.

4.    Right-click the mouse and then select Unhide from the formatting menu. The hidden columns will reappear.

Unhiding the hidden columns

1.5.6 Wrapping Text and Merging Cells

Whenever you have too much cell content to be displayed in a single cell, you may decide to wrap the text or merge the cell rather than resizing a column. Wrapping the text will automatically modify a cell's row height, allowing the cell contents to be displayed on multiple lines. Merging allows you to combine a cell with adjacent, empty cells to create one large cell.

1.5.6.1 Wrapping Text in Cells

In our example below, we'll wrap the text of the cells in column D so the entire address can be displayed.

1. Select the cells you wish to wrap. In this example, we'll select the cells in column D.

Selecting cells to wrap 2. Select the Wrap Text command on the Home tab.

Clicking the Wrap Text command 3. The text in the selected cells will be wrapped.

The wrapped text

Click the Wrap Text command again to unwrap the text.

1.5.6.2 Merging Cells Using the Merge & Center Command

In our example below, we'll merge cell A1 with cells B1:E1 to create a title heading for our worksheet.

1. Select the cell range you want to merge together.

Selecting cell range A1:E1 2. Select the Merge & Center command on the Home tab.

Clicking the Merge & Center command 3. The selected cells will be merged and the text will be centered.

Cell A1 after merging with B1:E1

1.5.6.3 Accessing More Merge Options

Click the drop-down arrow next to the Merge & Center command on the Home tab. The Merge drop-down menu will appear. From here, you can choose to:

•    Merge & Center: Merges the selected cells into one cell and centers the text.

•    Merge Across: Merges the selected cells into larger cells while keeping each row separate.

•    Merge Cells: Merges the selected cells into one cell, but does not center the text.

•    Unmerge Cells: Unmerges the selected cells.

 

Exercise 1.5

1.    Open an existing Excel 2013 workbook. 

2.    Modify the width of a column. If you are using the example, use the column that contains the players' first names.

3.    Insert a column between column A and column B, then insert a row between row 3 and row 4.

4.    Delete a column or a row.

5.    Move a column or row.

6.    Try using the Text Wrap command on a cell range. If you are using the example, wrap the text in the column that contains street addresses.

7.    Try merging some cells together. If you are using the example, merge the cells in the title row using the Merge & Center command. (cell range A1:E1)

1.6. Formatting Cells

All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections and making your content easier to view and understand. 

1. 6.1 Changing the Font

By default, the font of each new workbook is set to Calibri. However, Excel provides a wide variety of other fonts that you can use to customize your cell text. In the example below, we'll format our title cell to help distinguish it from the rest of the worksheet.

1.    Select the cell(s) you wish to modify.

Selecting a cell

2.    Click the drop-down arrow next to the Font command on the Home tab. The Font drop-down menu will appear.

3.    Select the desired font. A live preview of the new font will appear as you hover the mouse over different options. In our example, we'll choose Georgia.

Choosing a font

4.    The text will change to the selected font .

The new font

When creating a workbook in the workplace, you need to select a font that is easy to read. Along with Calibri, standard reading fonts include Cambria, Times New Roman and Arial.

1. 6.2 Changing the Font Size

1.    Select the cell(s) you wish to modify.

Selecting a cell

2.    Click the drop-down arrow next to the Font Size command on the Home tab. The Font Size drop-down menu will appear.

3.    Select the desired font size. A live preview of the new font size will appear as you hover the mouse over different options. In our example, we will choose 16 to make the text larger.

Choosing a new font size 4. The text will change to the selected font size.

The new font size

You can also use the Increase Font Size and Decrease Font Size commands or enter a custom font sizeusing your keyboard.

Modifying the font size

1. 6. 3 Changing the Font Color

1.    Select the cell(s) you wish to modify.

Selecting a cell

2.    Click the drop-down arrow next to the Font Color command on the Home tab. The Color menu will appear.

3.    Select the desired font color. A live preview of the new font color will appear as you hover the mouse over different options. In our example, we'll choose Green.

Choosing a font color 4. The text will change to the selected font color.

The new font color

Select More Colors at the bottom of the menu to access additional color options.

Selecting more colors

1. 6.4 Using the Bold, Italic, and Underline Commands

1.    Select the cell(s) you wish to modify.

Selecting a cell

2.    Click the Bold (B), Italic (I), or Underline (U) commands on the Home tab. In our example, we'll make the selected cells bold.

Clicking the Bold command 3. The selected style will be applied to the text.

The bold text

You can also press Ctrl+B on your keyboard to apply bolding, Ctrl+I to apply italics, or Ctrl+U to apply underlining.

1. 6. 5 Text Alignment

By default, any text entered into your worksheet will be aligned to the bottom-left of a cell. Any numbers will be aligned to the bottom-right of a cell. Changing the alignment of your cell content allows you to choose how the content is displayed in any cell, which can make your cell content easier to read.

Click the arrows in the slideshow below to learn more about the different text alignment options.

                 ?                                                                                    

Left align: Aligns content to the left border of the cell.

1. 6. 6.1. Changing Horizontal Text Alignment

In our examples below, we'll modify the alignment of our title cell to create a more polished look and further distinguish it from the rest of the worksheet.

1.    Select the cell(s) you wish to modify.

Selecting a cell

2.    Select one of the three horizontal Alignment commands on the Home tab. In our example, we'll choose Center Align.

Choosing Center Align 3. The text will realign.

The realigned cell text

1.   6. 5.2. Changing Vertical Text Alignment 1. Select the cell(s) you wish to modify.

Selecting a cell

2.   Select one of the three vertical Alignment commands on the Home tab. In our example, we'll choose Middle Align.

Choosing Middle Align 3. The text will realign.

The realigned cell text

You can apply both vertical and horizontal alignment settings to any cell.

1. 6.6. Cell Borders and Fill Colors

Cell borders and fill colors allow you to create clear and defined boundaries for different sections of your worksheet. In our examples below, we'll add cell borders and fill color to our header cells to help distinguish them from the rest of the worksheet.

1. 6.6.1.  Adding a Border

1.    Select the cell(s) you wish to modify.

Selecting a cell range

2.    Click the drop-down arrow next to the Borders command on the Home tab. The Borders drop-down menu will appear.

3.    Select the border style you want to use. In our example, we will choose to display All Borders.

Choosing a border style 4. The selected border style will appear.

The added cell borders

 

You can draw borders and change the line style and color of borders with the Draw Borders tools at the bottom of the Borders drop-down menu.

Drawing custom borders

1. 6.6.2. Adding a Fill Color

1.    Select the cell(s) you wish to modify.

Selecting a cell range

2.    Click the drop-down arrow next to the Fill Color command on the Home tab. The Fill Color menu will appear.

3.    Select the fill color you want to use. A live preview of the new fill color will appear as you hover the mouse over different options. In our example, we'll choose Light Green.

Choosing a cell fill color 4. The selected fill color will appear in the selected cells.

The new fill color

1. 6.7 Cell Styles

 

Rather than formatting cells manually, you can use Excel's pre-designed cell styles. Cell styles are a quick way to include professional formatting for different parts of your workbook, such as titles, headers, and more.

To Apply a Cell Style:

 

In our example, we'll apply a new cell style to our existing title and header cells.

1.    Select the cell(s) you wish to modify.

Selecting a cell range

2.    Click the Cell Styles command on the Home tab and then choose the desired style from the drop-down menu. In our example, we'll choose Accent 1.

 

Choosing a cell style 3. The selected cell style will appear. 

The new cell style

Applying a cell style will replace any existing cell formatting, except text alignment. You may not want to use cell styles if you've already added a lot of formatting to your workbook.

1. 6.8 Formatting Text and Numbers

 

One of the most powerful tools in Excel is the ability to apply specific formatting for text and numbers. Instead of displaying all cell content in exactly the same way, you can use formatting to change the appearance of dates, times, decimals, percentages (%), currency ($), and much more.

To Apply Number Formatting:

 

In our example, we will change the number format for several cells to modify the way dates are displayed.

1.    Select the cells(s) you wish to modify. 

Selecting a cell range

2.    Click the drop-down arrow next to the Number command on the Home tab. The Number Formatting dropdown menu will appear.

3.    Select the desired formatting option. In our example, we will change the formatting to Long Date.

Choosing Long Date 

4.    The selected cells will change to the new formatting style. For some number formats, you can then use the Increase Decimal and Decrease Decimal commands (below the Number Format command) to change the number of decimal places that are displayed.

                                     The applied number formatting

Click the buttons in the interactive below to learn about different text and number formatting options.

Exercise 1.6

1.    Open an existing Excel 2013 workbook. 

2.    Select a cell and change the font style, size, and color of the text. If you are using the example, change the title in cell A3 to Verdana font style, size 16 with a font color of green.

3.    Apply bolding, italics, or underlining to a cell. If you are using the example, bold the text in cell range A4:C4.

4.    Try changing the vertical and horizontal text alignment for some cells.

5.    Add a border to a cell range. If you are using the example, add a border to the header cells in in row 4.

6.    Change the fill color of a cell range. If you are using the example, add a fill color to row 4.

7.    Try changing the formatting of a number. If you are using the example, modify the date formatting in cell range D4:H4.

1.7. Worksheet Basics

Every workbook contains at least one worksheet by default. When working with a large amount of data, you can create multiple worksheets to help organize your workbook and make it easier to find content. You can also group worksheets to quickly add information to multiple worksheets at the same time.

1.7.1 Renaming a Worksheet

Whenever you create a new Excel workbook, it will contain one worksheet named Sheet1. You can rename a worksheet to better reflect its content. In our example, we will create a training log organized by month.

1. Right-click the worksheet you wish to rename, then select Rename from the worksheet menu.

Clicking Rename 2. Type the desired name for the worksheet.

Entering a new worksheet name

3. Click anywhere outside of the worksheet or press Enter on your keyboard. The worksheet will be renamed.

The renamed worksheet

1.7.2 Inserting a New Worksheet

1. Locate and select the New sheet button.

Clicking the New sheet button 2. A new, blank worksheet will appear.

The new, blank worksheet

To change the default number of worksheets, navigate to Backstage view, click Options, then choose the desired number of worksheets to include in every new workbook.

Modifying the number of default worksheets



1.7.3 Deleting a Worksheet

1. Right-click the worksheet you wish to delete, then select Delete from the worksheet menu.

Deleting a worksheet 2. The worksheet will be deleted from your workbook.

The deleted worksheet

If you wish to prevent specific worksheets from being edited or deleted, you can protect them by right-clicking the desired worksheet and then selecting Protect sheet from the worksheet menu.

Protecting a worksheet

1.7. 4 Copying a Worksheet

If you need to duplicate the content of one worksheet to another, Excel allows you to copy an existing worksheet.

1.    Right-click the worksheet you want to copy, then select Move or Copy from the worksheet menu.

Selecting Move or Copy

2.    The Move or Copy dialog box will appear. Choose where the sheet will appear in the Before sheet: field. In our example, we'll choose (move to end) to place the worksheet to the right of the existing worksheet.

3.    Check the box next to Create a copy, then click OK.

Copying a worksheet

4.    The worksheet will be copied. It will have the same title as the original worksheet, as well as a version number. In our example, we copied the January worksheet, so our new worksheet is named January (2). All content from the January worksheet has also been copied to the January (2) worksheet.

The copied worksheet

You can also copy a worksheet to an entirely different workbook. You can select any workbook that is currently open from the To book: drop-down menu.

 

1.7. 5 Moving a Worksheet

Sometimes you may want to move a worksheet to rearrange your workbook.

1.    Select the worksheet you wish to move. The cursor will become a small worksheet icon .

2.    Hold and drag the mouse until a small black arrow  appears above the desired location.

Moving a worksheet 3. Release the mouse. The worksheet will be moved.

The moved worksheet

1.7.6 Changing the Worksheet Color

You can change a worksheet's color to help organize your worksheets and make your workbook easier to navigate.

1.    Right-click the desired worksheet and hover the mouse over Tab Color. The Color menu will appear.

2.    Select the desired color. A live preview of the new worksheet color will appear as you hover the mouse over different options. In our example, we'll choose Red.

Selecting a worksheet color 3. The worksheet color will be changed.

The new worksheet color

 

The worksheet color is considerably less noticeable when the worksheet is selected. Select another worksheet to see how the color will appear when the worksheet is not selected.

Viewing an unselected worksheet color

1.7.7 Grouping and Ungrouping Worksheets

You can work with each worksheet individually, or you can work with multiple worksheets at the same time. Worksheets can be combined together into a group. Any changes made to one worksheet in a group will be made to every worksheet in the group.

1.7.7.1. Grouping Worksheets

In our example, employees need to receive training every three months, so we'll create a worksheet group for those employees. When we add the names of the employees to one worksheet, they'll be added to the other worksheets in the group, as well.

1.    Select the first worksheet you wish to include in the worksheet group.

Selecting the first worksheet of the group

2.    Press and hold the Ctrl key on your keyboard.

3.    Select the next worksheet you want in the group. Continue to select worksheets until all of the worksheets you want to group are selected.

Adding worksheets to the group

4.    Release the Ctrl key. The worksheets are now grouped

While worksheets are grouped, you can navigate to any worksheet within the group. Any changes made to one worksheet will appear on every worksheet in the group. However, if you select a worksheet that is not in the group, all of your worksheets will become ungrouped

1.7.7.2. Ungrouping All Worksheets

1.    Right-click a worksheet in the group, then select Ungroup Sheets from the worksheet menu.

 

Ungrouping a worksheet group

2.    The worksheets will be ungrouped. Alternatively, you can simply click any worksheet not included in the group to ungroup all worksheets.

The ungrouped worksheets

Exercise 1.7

1.    Open an existing Excel workbook. 

2.    Insert a new worksheet and rename it. If you are using the example, title the new worksheet April.

3.    Delete a worksheet. If you are using the example, delete the blank worksheet named Sheet 4.

4.    Move a worksheet.

5.    Copy a worksheet.

6.    Try grouping and ungrouping worksheets. If you are using the example, group the January and March worksheets together. Try entering new content in the January worksheet and then notice how it appears in the March worksheet.

1.8. Page Layout

Many of the commands you'll use to prepare your workbook for printing and PDF export can be found on the Page Layout tab. These commands let you control the way your content will appear on a printed page, including the page orientation, margin size, and more. Other page layout options, such as print titles and page breaks, can help make your workbook easier to read.

1.8.1 Page Layout View

Before you start modifying a workbook's page layout, you may want to view the workbook in Page Layout view, which can help you visualize your changes.

? To access Page Layout view, locate and select the Page Layout view command in the lower-right corner of your workbook.

Selecting Page Layout view

1.8.2 Page Orientation

Excel offers two page orientation options: landscape and portrait. Landscape orients the page horizontally, while Portrait orients the page vertically. Portrait is especially helpful for worksheets with a lot of rows, while Landscape is best for worksheets with a lot of columns. In the example below, Portrait orientation works best because the worksheet includes more rows than columns.

Portrait and Landscape orientation

 1.8.3 Changing Page Orientation

1.    Click the Page Layout tab on the Ribbon.

2.    Select the Orientation command and then choose either Portrait or Landscape from the drop-down menu.

Choosing a page orientation

3.    The page orientation of the workbook will be changed.

1.8.4 Formatting Page Margins

A margin is the space between your content and the edge of the page. By default, every workbook's margins are set to Normal, a one-inch space between the content and each edge of the page. Sometimes, you may need to adjust the margins to make your data fit more comfortably on the page. Excel includes a wide variety of pre-defined margin sizes.

1.    Click the Page Layout tab on the Ribbon and then select the Margins command.

2.    Select the desired margin size from the drop-down menu. In our example, we'll select Narrow to fit more of our content on the page.

Choosing a pre-defined margin size

3.    The margins will be changed to the selected size.

1.8.5 Using Custom Margins

Excel also allows you to customize the size of your margins in the Page Setup dialog box.

1.    From the Page Layout tab, click Margins. Select Custom Margins from the drop-down menu.

Selecting Custom Margins

2.    The Page Setup dialog box will appear.

3.    Adjust the values for each margin and click OK.

Setting custom page margins

4.    The margins of the workbook will be changed.

1.8.6 Including Print Titles

If your worksheet uses title headings, it's important to include those headings on every page of your printed worksheet. It would be extremely difficult to read a printed workbook if the title headings appeared only on the first page. The Print Titles command allows you to select specific rows and columns to appear on each page.

1.    Click the Page Layout tab on the Ribbon, then select the Print Titles command.

Clicking the Print Titles command

2.    The Page Setup dialog box will appear. From here, you can choose rows or columns to repeat on each page. In our example, we'll repeat a row.

3.    Click the Collapse Dialog button next to the Rows to repeat at top: field.

Clicking the Collapse Dialog Button

4.    The cursor will become a small selection arrow  and the Page Setup dialog box will be collapsed. Select the row(s) you want to repeat at the top of each printed page. In our example, we'll select row 1.

Selecting row 1

5.    Row 1 will be added to the Rows to repeat at top: field. Click the Collapse Dialog button again.

Clicking the Collapse Dialog button

6.    The Page Setup dialog box will expand. Click OK. Row 1 will be printed at the top of every page.

Clicking OK

1.8.7 Inserting a Page Break

If you need to print different parts of your workbook across separate pages, you can insert a page break. There are two types of page breaks: vertical and horizontal. Vertical page breaks separate columns, while horizontal page breaks separate rows. In our example, we'll insert a horizontal page break.

1.    Locate and select the Page Break view command. The worksheet will appear in Page Break view.

Selecting Page Break View

2.    Select the row above where you want the page break to appear. For example, if you want to insert a page break between rows 28 and 29, select row 29.

 

Selecting a row

3.    Click the Page Layout tab on the Ribbon, select the Breaks command and then click Insert Page Break.

Inserting a page break 4. The page break will be inserted, represented by a dark blue line.

The inserted page break

 

1.8.8 Inserting Headers & Footers

You can make your workbook easier to read and look more professional by including Headers & Footers. The header is a section of the workbook that appears in the top margin, while the footer appears in the bottom margin.

Headers and footers generally contain information such as page number, date, and workbook name.

1.    Locate and select the Page Layout view command. The worksheet will appear in Page Layout view.

Selecting Page Layout View

2.    Select the desired header or footer you wish to modify. In our example, we'll modify the footer at the bottom of the page.

 

Selecting a footer to modify

3.    The Header & Footer Tools tab will appear on the Ribbon. From here, you can access commands that will automatically include page numbers, date, workbook name, and more. In our example, we'll add page numbers.

 

Adding page numbers from the Header & Footer Tools tab 4. The footer will change to include page numbers automatically.

The newly added footer

Excel uses the same tools as Microsoft Word to modify headers and footers. Check out our lesson onHeaders,Footers and Page Numbersfrom ourtraining manual to learn more. 

Exercise 1.8

1.    Open an existing Excel workbook. 

2.    Change the page orientation to Landscape.

3.    Try modifying the margins of a worksheet.

4.    Try using the Print Titles command to include a row or column on every page of your workbook. If you are using the example, use the Print Titles command to make row 1 of the Schedule worksheet appear at the top of every page

5.    Insert a page break. If you are using the example, insert a page break between rows 19 and 20 on the Schedule worksheet.

6.    Navigate to Page Layout view and insert a header or footer.

1.9. Printing Workbooks

There may be times when you want to print a workbook to view and share your data offline. Once you've chosen your page layout settings, it's easy to preview and print a workbook from Excel using the Print pane.

1.9.1 Accessing the Print Pane

1. Select the File tab. Backstage view will appear.

Clicking the File tab 2. Select Print. The Print pane will appear.

Clicking Print

Click the buttons in the interactive below to learn more about using the Print pane.

1.9.2 Printing a Workbook

1.    Navigate to the Print pane and select the desired printer.

2.    Enter the number of copies you wish to print.

3.    Select any additional settings, if needed (see above interactive).

4.    Click Print.

Printing a workbook

1.9. 3 Choosing a Print Area

Before you print an Excel workbook, it's important to decide exactly what information you want to print. For example, if you have multiple worksheets in your workbook, you will need to decide if you want to print the entire workbook or only active worksheets. There may also be times when you want to print only a selection of content from your workbook.

1.9. 4 To Print Active Sheets:

Worksheets are considered active when selected.

1.    Select the worksheet you want to print. To print multiple worksheets, click the first worksheet, hold the Ctrl key on your keyboard, then click any other worksheets you want to select.

2.    Navigate to the Print pane.

3.    Select Print Active Sheets from the Print Range drop-down menu.

Printing active worksheets

4.    Click the Print button.

Clicking the Print button

1.9. 5 Printing the Entire Workbook

1.    Navigate to the Print pane.

2.    Select Print Entire Workbook from the Print Range drop-down menu.

Printing the entire workbook 3. Click the Print button.

Clicking the Print button

1.9.6 Printing a Selection

In our example, we'll print a selection of content related to upcoming softball games in July. 

1.    Select the cells you wish to print.

 

Selecting a print area

2.    Navigate to the Print pane.

3.    Select Print Selection from the Print Range drop-down menu.

Printing only the selected cells 4. A preview of your selection will appear in the Preview pane.

 

Viewing the selection in the Preview pane 5. Click the Print button to print the selection. 

Clicking the Print button

If you prefer, you can also set the print area in advance so you'll be able to visualize which cells will be printed as you work in Excel. Simply select the cells you want to print, click the Page Layout tab, select the Print Area command, then choose Set Print Area.

 

On occasion, you may need to make small adjustments from the Print pane to fit your workbook content neatly onto a printed page. The Print pane includes several tools to help fit and scale your content, such as scaling and page margins.

To Fit Content Before Printing:

If some of your content is being cut off by the printer, you can use scaling to fit your workbook to the page automatically.

1.    Navigate to the Print pane. In our example, we can see in the Preview pane that our content will be cut off when printed.

Viewing a cut off worksheet in the Preview pane

2.    Select the desired option from the from the Scaling drop-down menu. In our example, we'll select Fit Sheet on One Page.

Fitting a worksheet onto one page 3. The worksheet will be condensed to fit onto a single page.

The scaled worksheet 4. When you're satisfied with the scaling, click Print.

Clicking the Print button

Keep in mind that worksheets will become more difficult to read as they are scaled down, so you may not want to use this option when printing a worksheet with a lot of information.

1.9.8 Modifying Margins in the Preview Pane

Sometimes, you may only need to adjust a single margin to make your data fit more comfortably. You can modify individual page margins from the Preview pane.

1.    Navigate to the Print pane, then click the Show Margins button in the lower-right corner.

                 Showing the margins

2.    The page margins will appear in the Preview pane. Hover the mouse over one of the margin markers  until the cursor becomes a double arrow . In our example, we'll modify the left margin to fit an additional column on the page.

 

Hovering the mouse over a margin

3.    Click, hold and drag the mouse to increase or decrease the margin width.

 

Decreasing the margin width

4.    Release the mouse. The margin will be modified. In our example, we were able to fit an additional column on the page.

The new margin width

Exercise 1.9

1.    Open an existing Excel workbook. 

2.    Try printing two active worksheets. If you are using the example, try printing the Player Info and Schedule worksheets.

3.    Try printing only a selection of cells. If you are using the example, try printing the upcoming games for the Bulls (cell range A12:E19).

4.    Try the scaling feature to condense your workbook content. If you are using the example, use scaling to make the worksheet fit onto a single page.

5.    Adjust the margins from the Preview pane.

CHAPTER TWO

2. Formulas and Functions

2.1 Simple Formulas

2.1.1 Introduction

One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we'll show you how to use cell references to create simple formulas.

2.1.2 Mathematical Operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents. 

Standard operators

All formulas in Excel must begin with an equal sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

2.1.3 Understanding Cell References

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5) most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate, because you can change the value of referenced cells without having to rewrite the formula.

Using cell references to recalculate a formula

By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:

Examples of simple formulas

2.1.4 Creating a Formula

In our example below, we'll use a simple formula and cell references to calculate a budget.

1.    Select the cell that will contain the formula. In our example, we'll select cell B3.

Selecting cell B3

2.    Type the equal sign (=). Notice how it appears in both the cell and the formula bar.

Entering the = sign

3.    Type the cell address of the cell that you wish to reference first in the formula, cell B1 in our example. A blue border will appear around the referenced cell.

Referencing cell B1

4.    Type the mathematical operator you wish to use. In our example, we'll type the addition sign (+).

5.    Type the cell address of the cell that you wish to reference second in the formula, cell B2 in our example. Ared border will appear around the referenced cell. 

Referencing cell B2

6.    Press Enter on your keyboard. The formula will be calculated and the value will be displayed in the cell.

The complete formula and calculated value

2.1.5 Modifying Values with Cell References

 

The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we've modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.

The recalculated cell value

2.1.6 Create a Formula Using the Point and Click Method

Rather than typing cell addresses manually, you can also point and click on the cells you wish to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we'll create a formula to calculate the cost of ordering several boxes of plastic silverware.

1.    Select the cell that will contain the formula. In our example, we'll select cell D3.

 

Selecting cell D3

2.    Type the equal sign (=).

3.    Select the cell that you wish to reference first in the formula, cell B3 in our example. The cell address will appear in the formula and a dashed blue line will appear around the referenced cell.

Referencing cell B3

4.    Type the mathematical operator you wish to use. In our example, we'll type the multiplication sign (*).

5.    Select the cell that you wish to reference second in the formula, cell C3 in our example. The cell address will appear in the formula and a dashed red line will appear around the referenced cell.

 

Referencing cell C3

6.    Press Enter on your keyboard. The formula will be calculated and the value will be displayed in the cell.

The completed formula and calculated value

Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. Review our lesson onRelative and Absolute CellReferencesto learn more.

 

2.1.7 Editing a Formula

Sometimes, you may want to modify an existing formula. In the example below, we've entered an incorrect cell address in our formula, so we'll need to correct it.

1.                                                               Select the cell containing the formula you wish to edit. In our example, we'll select cell B3.

Selecting cell B3

2.                                                               Click the formula bar to edit the formula. You can also double-click the cell to view and edit the formula directly within the cell.

Selecting a formula to edit

3.                                                               A border will appear around any referenced cells. In our example, we'll change the second part of the formula to reference cell B2 instead of cell C2

The misplaced cell reference

4.                                                               in the formula bar. 

Editing a formula

5.                                                               The formula will be updated and the new value will be displayed in the cell.

The newly calculated value

If you change your mind, you can press the Esc key or your keyboard or click the Cancel command  in the formula bar to avoid accidentally making changes to your formula.

Exercise 2.1

1.    Open an existing Excel workbook.

2.    Create a simple addition formula using cell references. If you are using the example, create the formula in cell B4 to calculate the "Total Budget."

3.    Try modifying the value of a cell referenced in a formula. If you are using the example, change the value of cell B2 to $2,000. Notice how the formula in cell B4 recalculates the total.

4.    Try using the point and click method to create a formula. If you are using the example, create a formula in cell G5 that multiplies the cost of napkins by the quantity needed to calculate the total cost.

5.    Edit a formula using the formula bar. If you are using the example, edit the formula in cell B9 to change the division sign (/) to a minus sign (-).

2.2 Complex Formulas

A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations.

2.2.1 Order of Operations

Excel calculates formulas based on the following order of operations:

1.    Operations enclosed in parentheses

2.    Exponential calculations (3^2, for example)

3.    Multiplication and division, whichever comes first

4.    Addition and subtraction, whichever comes first

A mnemonic that can help you remember the order is PEMDAS or Please Excuse My Dear Aunt Sally.

Click the arrows in the slideshow below to learn more about how the order of operations is used to calculate formulas in Excel.

                      ?                                                                              

While this formula may look really complicated, we can use the order of operations step-by-step to find the right answer.

2.2.2 Creating Complex Formulas

In the example below, we will demonstrate how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of sales tax for a catering invoice. To do this, we'll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.

Creating a complex formula

Excel follows the order of operations and first adds the values inside the parentheses: (44.85+39.90) = $84.75. Then, it multiplies that value by the tax rate: $84.75*0.075. The result will show that the sales tax is $6.36.

The completed formula and calculated value

It is especially important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the result is incorrect. Parentheses are the best way to define what calculations will be performed first in Excel.

Result of an incorrect formula

2.2.3 Creating a Complex Formula Using the Order of Operations

In our example below, we will use cell references along with numerical values to create a complex formula that will calculate the total cost for a catering invoice. The formula will calculate the cost for each menu item and then add those values together.

1.    Select the cell that will contain the formula. In our example, we'll select cell C4.

Selecting cell C4

2.    Enter your formula. In our example, we'll type =B2*C2+B3*C3. This formula will follow the order of operations, first performing the multiplication: 2.29*20 = 45.80 and 3.49*35 = 112.15. Then, it will add those values together to calculate the total: 45.80+112.15.

Creating a complex formula

3.    Double-check your formula for accuracy, then press Enter on your keyboard. The formula will calculate and display the result. In our example, the result shows that the total cost for the order is $167.95.

The completed formula and calculated value

You can add parentheses to any equation to make it easier to read. While it won't change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.

 

Exercise 2.2

1.    Open an existing Excel workbook. 

2.    Try creating a complex formula that uses addition and subtraction. If you are using the example, create a formula in cell D6 that first adds the values of cells D3, D4 and D5 and then multiples their total by 0.075.

3.    Try creating a complex formula that uses multiplication and division. If you are using the example, create a

formula in cell D7 to calculate the total cost of the invoice, including sales tax.

2.3 Relative and Absolute Cell References

There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant, no matter where they are copied.

2.3.1 Relative References

By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.

2.3.2 Creating and Copying a Formula Using Relative References

In the following example, we want to create a formula that will multiply each item's price by the quantity. Rather than creating a new formula for each row, we can create a single formula in cell D2 and then copy it to the other rows.

We'll use relative references so that the formula correctly calculates the total for each item.

1.    Select the cell that will contain the formula. In our example, we'll select cell D2.

 

Selecting cell D2

2.    Enter the formula to calculate the desired value. In our example, we'll type =B2*C2.

 

Entering the formula

3.    Press Enter on your keyboard. The formula will be calculated and the result will be displayed in the cell.

4.    Locate the fill handle in the lower-right corner of the desired cell. In our example, we'll locate the fill handle for cell D2.

Locating the fill handle

5.    Click, hold and drag the fill handle over the cells you wish to fill. In our example, we'll select cells D3:D12.

 

Dragging the fill handle over cells D3:D12

6.    Release the mouse. The formula will be copied to the selected cells with relative references and the values will be calculated in each cell.

 

The copied formulas and calculated values

You can double-click the filled cells to check their formulas for accuracy. The relative cell references should be different for each cell, depending on its row.

Checking the copied formulas for accuracy

2.3.3 Absolute References

 

2.3.4 Creating and Copying a Formula Using Absolute References

In our example, we'll use the 7.5% sales tax rate in cell E1 to calculate the sales tax for all items in column D. We'll need to use the absolute cell reference $E$1 in our formula. Since each formula is using the same tax rate, we want that reference to remain constant when the formula is copied and filled to other cells in column D.

1.    Select the cell that will contain the formula. In our example, we'll select cell D3.

 

Selecting cell D3

2.    Enter the formula to calculate the desired value. In our example, we'll type =(B3*C3)*$E$1.

 

Entering the formula

3.    Press Enter on your keyboard. The formula will calculate and the result will display in the cell.

4.    Locate the fill handle in the lower-right corner of the desired cell. In our example, we'll locate the fill handle for cell D3.

 

Locating the fill handle

5.    Click, hold and drag the fill handle over the cells you wish to fill, cells D4:D13 in our example.

 

Dragging the fill handle

6.    Release the mouse. The formula will be copied to the selected cells with an absolute reference and the values will be calculated in each cell.

The copied formulas and calculated values

You can double-click the filled cells to check their formulas for accuracy. The absolute reference should be the same for each cell, while the other references are relative to the cell's row.

 

Checking the formulas for accuracy

 

Be sure to include the dollar sign ($) whenever you're making an absolute reference across multiple cells. The dollar signs were omitted in the example below. This caused Excel to interpret it as a relative reference, producing an incorrect result when copied to other cells.

The result of an incorrect absolute reference

2.3.5 Using Cell References with Multiple Worksheets

Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another. To do this, you'll simply need to begin the cell reference with the worksheet name followed by an exclamation point (!). For example, if you wanted to reference cell A1 onSheet1, its cell reference would be Sheet1!A1.

Note that if a worksheet name contains a space, you will need to include single quotation marks (' ') around the name. For example, if you wanted to reference cell A1 on a worksheet named July Budget, its cell reference would be 'July Budget'!A1.

2.3.6 Referencing Cells Across Worksheets

In our example below, we'll refer to a cell with a calculated value between two worksheets. This will allow us to use the exact same value on two different worksheets without rewriting the formula or copying data between worksheets.

1.    Locate the cell you wish to reference and note its worksheet. In our example, we want to reference cell E14 on the Menu Order worksheet.

Cell E14

2.    Navigate to the desired worksheet. In our example, we'll select the Catering Invoice worksheet.

 

Navigating to Sheet2

3.    The selected worksheet will appear.

4.    Locate and select the cell where you want the value to appear. In our example, we'll select cell B2.

Selecting cell B2

5.    Type the equal sign (=), the sheet name followed by an exclamation point (!), and the cell address. In our example, we'll type ='Menu Order'!E14.

Referencing a cell on Sheet1

6.    Press Enter on your keyboard. The value of the referenced cell will appear. If the value of cell E14 changes on the Menu Order worksheet, it will be updated automatically on the Catering Invoice worksheet.

The referenced cell

 

If you rename your worksheet at a later point, the cell reference will be updated automatically to reflect the new worksheet name.

If you enter a worksheet name incorrectly, the #REF! error will appear in the cell. In our example below, we've mistyped the name of the worksheet. Click the Error button  and then select the desired option from the dropdown menu to edit or ignore the error.

Correcting a cell reference error

Exercise 2.3

1.    Open an existing Excel workbook.

2.    Create a formula that uses a relative reference. If you are using the example, use the fill handle to fill in the formula in cell E4 through E14. Double-click a cell to see the copied formula and the relative cell references.

3.    Create a formula that uses an absolute reference. If you are using the example, correct the formula in cell D4 to refer only to the tax rate in cell E2 as an absolute reference, then use the fill handle to fill the formula from cell D4 to D14.

4.    Try referencing a cell across worksheets. If you are using the example, create a cell reference in cell B3 on the Catering Invoice worksheet for cell E15 on the Menu Order worksheet.

2. 4 Functions

A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be useful for quickly finding the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.

2. 4.1 The Parts of a Function

In order to work correctly, a function must be written a specific way, which is called the syntax. The basic syntax for a function is an equal sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20.

Syntax of a basic function

2.4.2 Working with Arguments

Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.

For example, the function =AVERAGE (B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.

A function with a single argument

Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E2) will add the values of all the cells in the three arguments.

A function with multiple arguments

2.4.3 Creating a Function

Excel has a wide variety of functions available. Here are some of the most common functions you'll use:

•    SUM: This function adds all the values of the cells in the argument.

•    AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument.

•    COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range.

•    MAX: This function determines the highest cell value included in the argument.

•    MIN: This function determines the lowest cell value included in the argument.

2. 4.4 Creating a Basic Function

In our example below, we'll create a basic function to calculate the average price per unit for a list of recently ordered items using the AVERAGE function.

1.    Select the cell that will contain the function. In our example, we'll select cell C11.

 

Selecting cell C11

2.    Type the equal sign (=) and enter the desired function name. You can also select the desired function from the list of suggested functions that will appear below the cell as you type. In our example, we'll type=AVERAGE.

 

Entering the AVERAGE function

3.    Enter the cell range for the argument inside parentheses. In our example, we'll type (C3:C10). This formula will add the values of cells C3:C10 and then divide that value by the total number of cells in the range to determine the average. 

Creating an argument

4.    Press Enter on your keyboard. The function will be calculated and the result will appear in the cell. In our example, the average price per unit of items ordered was $15.93.

The completed function and calculated value

Excel will not always tell you if your function contains an error, so it's up to you to check all of your functions.

 2. 4. 6 Creating a Function Using the AutoSum Command

The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX. In our example below, we'll create a function to calculate the total cost for a list of recently ordered items using the SUM function.

1.    Select the cell that will contain the function. In our example, we'll select cell D12.

Selecting

cell D12

2.    In the Editing group on the Home tab, locate and select the arrow next to the AutoSum command and then choose the desired function from the drop-down menu. In our example, we'll select Sum.

Selecting Sum from the AutoSum command drop-down menu

3.    The selected function will appear in the cell. If logically placed, the AutoSum command will automatically select a cell range for the argument. In our example, cells D3:D11 were selected automatically and their values will be added together to calculate the total cost. You can also manually enter the desired cell range into the argument.

The inserted function and automatically selected cell range

4.    Press Enter on your keyboard. The function will be calculated and the result will appear in the cell. In our example, the sum of D3:D11 is $606.05.

The completed function and calculated value

 

The AutoSum command can also be accessed from the Formulas tab on the Ribbon.

Accessing the AutoSum command from the Formulas tab

2. 4.6 The Function Library

While there are hundreds of different functions in Excel, the functions you use most frequently will depend on the kind of data your workbooks contains. There is no need to learn every single function, but exploring some of the different types of functions will be helpful as you create new projects. You can search for functions by category, such as Financial, Logical, Text, Date & Time, and much more from the Function Library on the Formulas tab.

                ? To access the Function Library, select the Formulas tab on the Ribbon. The Function Library will appear.

Clicking the Formulas tab

Click the buttons in the interactive below to learn more about the different types of functions in Excel.

2. 4.7 Inserting a Function from the Function Library

In our example below, we'll use a function to calculate the number of business days it took to receive the items after they were ordered. In our example, we'll use the dates in columns B and C to calculate the delivery time in column D.

1.    Select the cell that will contain the function. In our example, we'll select cell D3.

   

Selecting cell D3

2.    Click the Formulas tab on the Ribbon to access the Function Library.

3.    From the Function Library group, select the desired function category. In our example, we'll choose Date & Time.

4.    Select the desired function from the drop-down menu. In our example, we'll select the NETWORKDAYS function to count the number of business days between the ordered date and received date.

 

Selecting the NETWORKDAYS function

5.    The Function Arguments dialog box will appear. From here, you'll be able to enter or select the cells that will make up the arguments in the function. In our example, we'll enter B3 in the Start_date: field and C3 in the End date: field.

6.    When you're satisfied with the arguments, click OK.

Clicking OK

7.    The function will be calculated and the result will appear in the cell. In our example, the result shows that it took four business days to receive the order.

The completed function and calculated value

 

Like formulas, functions can be copied to adjacent cells. Hover the mouse over the cell that contains the function, then click, hold and drag the fill handle over the cells you wish to fill. The function will be copied and values for those cells will be calculated relative to their row or column.

 

Copying a function to adjacent cells using the fill handle

2.4.8 The Insert Function Command

If you're having trouble finding the right function, the Insert Function command allows you to search for functions using keywords. While it can be extremely useful, this command is sometimes a little difficult to use. If you don't have much experience with functions, you may have more success browsing the Function Library instead. For more advanced users, however, the Insert Function command can be a powerful way to find a function quickly.

2. 4.9 Use the Insert Function Command

In our example below, we want to find a function that will count the total number of items ordered. We want to count the cells in the Item column, which uses text. We cannot use the basic COUNT function because it will only count cells with numerical information. Therefore, we will need to find a function that counts the total number of cells within a cell range.

1.    Select the cell that will contain the function. In our example, we'll select cell B16.

 

Selecting cell B16

2.    Click the Formulas tab on the Ribbon and then select the Insert Function command.

Selecting the Insert Function command

3.    The Insert Function dialog box will appear.

4.    Type a few keywords describing the calculation you want the function to perform and click Go. In our example, we'll type Count cells, but you can also search by selecting a category from the drop-down list.

Searching for a function with keywords

5.    Review the results to find the desired function, then click OK. In our example, we'll choose COUNTA because it will count the number of cells in a cell range.

Selecting a function and clicking OK

6.    The Function Arguments dialog box will appear. Select the Value1: field and then enter or select the desired cells. In our example, we'll enter the cell range A3:A10. You may continue to add arguments in theValue2:

field, but in this case we only want to count the number of cells in the cell range A3:A10.

7.    When you're satisfied, click OK.

 

Entering an argument and clicking OK

8.    The function will be calculated and the result will appear in the cell. In our example, the result shows that a total of eight items were ordered.

 

The completed function and calculated value

Exercise 2.4

1.    Open an existing Excel workbook. If you want, you can use theLesson16PracticeWorkbook.

2.    Create a function that contains one argument. If you're using the example, use the SUM function in cell B16 to calculate the total quantity of items ordered.

3.    Use the AutoSum command to insert a function. If you are using the example, insert the MAX function in cell B23 and use the cell range D3:D15 for the argument to find the most expensive item that was ordered.

4.    Explore the Function Library and try using the Insert Function command to search for different types of functions.

CHAPTER THREE

3. Working with Data

3.1 Freezing Panes and View Options

Whenever you're working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, such as the ability to freeze panes and split your worksheet.

3.1.1 Freezing Rows

You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you'll be able to scroll through your content while continuing to view the frozen cells.

1.    Select the row below the row(s) you wish to freeze. In our example, we want to freeze rows 1 and 2, so we'll select row 3.

 

Selecting row 3

2.    Click the View tab on the Ribbon.

3.    Select the Freeze Panes command and then choose Freeze Panes from the drop-down menu.

Clicking Freeze Panes

4.    The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. In our example, we've scrolled down to row 18.

The frozen rows

    3.1.2 Freezing Columns 

1.    Select the column to the right of the column(s) you wish to freeze. In our example, we want to freeze column A, so we'll select column B.

 

Selecting column B

2.    Click the View tab on the Ribbon.

3.    Select the Freeze Panes command and then choose Freeze Panes from the drop-down menu.

Clicking Freeze Panes

4.    The column will be frozen in place, as indicated by the gray line. You can scroll across the worksheet while continuing to view the frozen column on the left. In our example, we've scrolled across to column E.

The frozen column

To unfreeze rows or columns, click the Freeze Panes command and then select Unfreeze Panes from the drop-down menu.

Unfreezing a row

If you only need to freeze the top row (row 1) or first column (column A) in the worksheet, you can simply select Freeze Top Row or Freeze First Column from the drop-down menu.

Freezing only the top row of a workbook

   3.1. 3 Other View Options

If your workbook contains a lot of content, it can sometimes be difficult to compare different sections. Excel includes many additional options to make your workbooks easier to view and compare. For example, you can choose to open a new window for your workbook or split a worksheet into separate panes.

 3.1. 3.1 Open a New Window for the Current Workbook

Excel allows you to open multiple windows for a single workbook at the same time. In our example, we'll use this feature to compare two different worksheets from the same workbook.

1. Click the View tab on the Ribbon and then select the New Window command.

Clicking the New Window command 2. A new window for the workbook will appear.

The

same workbook open in two separate windows

3. You can now compare different worksheets from the same workbook across windows. In our example, we'll select the 2013 Sales Detailed View worksheet to compare the 2012 and 2013 sales.

 

Selecting a worksheet in a new window

 

If you have several windows open at the same time, you can use the Arrange All command to rearrange them quickly.

Clicking Arrange All

   3.1. 3.2 Split a Worksheet

Sometimes, you may want to compare different sections of the same workbook without creating a new window. The Split command allows you to divide the worksheet into multiple panes that scroll separately.

1. Select the cell where you wish to split the worksheet. In our example, we'll select cell C7.

1

Selecting cell C7 2. Click the View tab on the Ribbon and then select the Split command.

Clicking the Split command

3. The workbook will be split into different panes. You can scroll through each pane separately using the scroll bars, allowing you to compare different sections of the workbook.

 

The split worksheet

Exercise 3.1

1.    Open an existing Excel workbook. 

2.    Try freezing a row or column in place. If you are using the example, freeze the top two rows (rows 1 and 2).

3.    Try opening a new window for your workbook.

4.    Use the Split command to split your worksheet into multiple panes.

3.2 Sorting Data

As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways.

3.2.1 Types of Sorting

When sorting data, it's important to first decide if you would like the sort to apply to the entire worksheet or just a cell range.

•    Sort sheet organizes all of the data in your worksheet by one column. Related information across each row is kept together when the sort is applied. In the example below, the Contact Name column (column A) has been sorted to display the names in alphabetical order.

 

Sorting a sheet 

•    Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content on the worksheet.

 

Sorting a cell range

  3.2.1.1 Sorting a Sheet

In our example, we'll sort a t-shirt order form alphabetically by Last Name (column C).

1.    Select a cell in the column you wish to sort by. In our example, we'll select cell C2.

 

Selecting cell C2

2.    Select the Data tab on the Ribbon and then click the Ascending command  to Sort A to Z, or the  to Sort Z to A. In our example, we'll click the Ascending command.

Clicking the Ascending command

3.    The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name.

 

The sorted worksheet

  3.2.1.2 Sorting a Range

In our example, we'll select a separate table in our t-shirt order form to sort the number of shirts that were ordered on different dates.

1.    Select the cell range you wish to sort. In our example, we'll select cell range A13:B17.

Selecting cell range A13:B17

2.    Select the Data tab on the Ribbon and then click the Sort command.

Clicking the Sort command

3.    The Sort dialog box will appear. Choose the column you wish to sort by. In our example, we want to sort the data by the number of t-shirt orders, so we'll select Orders.

Selecting

a column to sort by

4.    Decide the sorting order (either ascending or descending). In our example, we'll use Smallest to Largest.

5.    Once you're satisfied with your selection, click OK.

Clicking OK

6.    The cell range will be sorted by the selected column. In our example, the Orders column will be sorted from lowest to highest. Notice that the other content in the worksheet was not affected by the sort. 

The sorted cell range

If your data isn't sorting properly, double-check your cell values to make sure they are entered into the worksheet correctly. Even a small typo could cause problems when sorting a large worksheet. In the example below, we forgot to include a hyphen in cell A18, causing our sort to be slightly inaccurate.

A small typo in cell A18 causing an incorrect sort

3.2.1.3 Custom Sorting

Sometimes, you may find that the default sorting options can't sort data in the order you need. Fortunately, Excel allows you to create a custom list to define your own sorting order.

To Create a Custom Sort:

In our example below, we want to sort the worksheet by T-Shirt Size (column D). A regular sort would organize the sizes alphabetically, which would be incorrect. Instead, we'll create a custom list to sort from smallest to largest.

1. Select a cell in the column you wish to sort by. In our example, we'll select cell D2

 

Selecting cell D2 2. Select the Data tab, then click the Sort command.

Clicking the Sort command

3.    The Sort dialog box will appear. Select the column you want to sort by, then choose Custom List from the Order field. In our example, we will choose to sort by T-Shirt Size.

Selecting Custom List from the Order: field

4.    The Custom Lists dialog box will appear. Select NEW LIST from the Custom Lists: box.

5.    Type the items in the desired custom order in the List entries: box. In our example, we want to sort our data by t-shirt size from smallest to largest, so we'll type Small, Medium, Large, and X-Large, pressing Enter on the keyboard after each item.

Creating a Custom list

6.    Click Add to save the new sort order. The new list will be added to the Custom lists: box. Make sure the new list is selected, then click OK.

Clicking OK to select the custom list

7.    The Custom Lists dialog box will close. Click OK in the Sort dialog box to perform the custom sort.

Clicking OK to sort the worksheet

8.    The worksheet will be sorted by the custom order. In our example, the worksheet is now organized by t-shirt size from smallest to largest.

The worksheet sorted by t-shirt size

  3.2.1. 4 Sorting by Cell Formatting

You can also choose to sort your worksheet by formatting rather than cell content. This can be especially helpful if you add color coding to certain cells. In our example below, we'll sort by cell color to quickly see which t-shirt orders have outstanding payments.

1.    Select a cell in the column you wish to sort by. In our example, we'll select cell E2.

 

Selecting cell E2

2.    Select the Data tab, then click the Sort command.

Clicking the Sort command

3.    The Sort dialog box will appear. Select the column you wish to sort by and then decide whether you'll sort by Cell Color, Font Color, or Cell Icon from the Sort On field. In our example, we'll sort by Payment Method (column E) and Cell Color.

Choosing to sort by cell color

4.    Choose a color to sort by from the Order field. In our example, we'll choose light red.

Choosing a cell color to sort by

5.    Click OK. In our example, the worksheet is now sorted by cell color, with the light red cells on top. This allows us to see which orders still have outstanding payments.

 

The worksheet sorted by cell color

   3.2.2 Sorting Levels

If you need more control over how your data is sorted, you can add multiple levels to any sort. This allows you to sort your data by more than one column.

To Add a Level:

In our example below, we'll sort the worksheet by Homeroom Number (column A) and then by Last Name (column C).

1.    Select a cell in the column you wish to sort by. In our example, we'll select cell A2.

Selecting cell A2

2.    Click the Data tab, then select the Sort command.

Clicking the Sort command

3.    The Sort dialog box will appear. Select the first column you wish to sort by. In this example, we will sort by Homeroom # (column A).

4.    Click Add Level to add another column to sort by.

Clicking Add Level

5.    Select the next column you wish to sort by, then click OK. In our example, we'll sort by Last Name (column C).

Sorting by Homeroom # and Last Name

6.    The worksheet will be sorted according to the selected order. In our example, the homeroom numbers are sorted numerically. Within each homeroom, students are sorted alphabetically by last name.

The worksheet sorted by homeroom number and last name

 

If you need to change the order of a multi-level sort, it's easy to control which column is sorted first. Simply select the desired column and then click the Move Up or Move Down arrow to adjust its priority.

Changing the sorting priority for a column

Exercise 3.2

1.    Open an existing Excel workbook. 

2.    Sort a worksheet in ascending  or descending  order. If you are using the example, sort by Homeroom # (column A).

3.    Sort a cell range. If you are using the example, sort the cell range in the cell rangeG3:H7 from highest to lowest by Orders (column H).

4.    Add a level to the sort and sort it by cell color, font color, or cell icon. If you are using the example, add a second level to sort by cell color in column E.

5.    Add another level and sort it using a custom list. If you are using the example, create a custom list to sort by TShirt Size (column D) in the order of Small, Medium, Large, and X-Large.

6.    Change the sorting priority. If you are using the example, re-order the list to sort  by T-Shirt Size (column D), Homeroom # (column A), and Last Name (column C).

3.3 Filtering Data

If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information that you need.

3.3.1 Filtering Data

In our example, we'll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for check-out.

1.    In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row 1: ID#, Type, Equipment Detail and so on.

A worksheet with a header row

2.    Select the Data tab, then click the Filter command.

Clicking the Filter command

3.    A drop-down arrow  will appear in the header cell for each column.

4.    Click the drop-down arrow for the column you wish to filter. In our example, we will filter column B to view only certain types of equipment.

 

Clicking the drop-down arrow for column B

5.    The Filter menu will appear.

6.    Uncheck the box next to Select All to quickly deselect all data. 

Unchecking Select All

7.    Check the boxes next to the data you wish to filter, then click OK. In this example, we will check Laptop and Tablet to view only those types of equipment.

Choosing data to filter and clicking OK

8.    The data will be filtered, temporarily hiding any content that doesn't match the criteria. In our example, only laptops and tablets are visible.

The filtered data

Filtering options can also be accessed from the Sort & Filter command on the Home tab.

Accessing Filter options from the Home tab

3.3.2 Apply Multiple Filters

Filters are cumulative, which means that you can apply multiple filters to help narrow down your results. In this example, we've already filtered our worksheet to show laptops and projectors, and we'd like to narrow it further to only show laptops and projectors that were checked out in August.

1.    Click the drop-down arrow for the column you wish to filter. In this example, we will add a filter to column D to view information by date.

 

Clicking the drop-down arrow for column D

2.    The Filter menu will appear.

3.    Check or uncheck the boxes depending on the data you wish to filter, then click OK. In our example, we'll uncheck everything except August.

 

Choosing data to filter and clicking OK

4.    The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and tablets that were checked out in August.

 

The filtered data

  3.3.3 Clearing a Filter

After applying a filter, you may want to remove, or clear, it from your worksheet so you'll be able to filter content in different ways.

1.    Click the drop-down arrow for the filter you wish to clear. In our example, we'll clear the filter in column D.

 

Clicking the drop-down arrow for column D

2.    The Filter menu will appear.

3.    Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we'll select Clear Filter From "Checked Out".

 

Clearing a filter

4.    The filter will be cleared from the column. The previously hidden data will be displayed.

The cleared filter

To remove all filters from your worksheet, click the Filter command on the Data tab.

Clicking the Filter command to remove filters

  3.3.4 Advanced Filtering

If you need to filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes many advanced filtering tools, including search, text, date, and number filtering, which can narrow your results to help find exactly what you need.

3.3.4.1 Filtering with Search

Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we'll use this feature to show only Saris brand products in our equipment log.

1.    Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.



2.    Click the drop-down arrow for the column you wish to filter. In our example, we'll filter column C.

 

Clicking the drop-down arrow for column C

3.    The Filter menu will appear. Enter a search term into the search box. Search results will appear automatically below the Text Filters field as you type. In our example, we'll type saris to find all Saris brand equipment.

4.    When you're done, click OK

 

Entering a search term and clicking OK

5.    The worksheet will be filtered according to your search term. In our example, the worksheet is now filtered to show only Saris brand equipment. 

 

The worksheet filtered by the search term

3.3.4.2 Using Advanced Text Filters

Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters, or data that excludes a specific word or number. In our example, we've already filtered our worksheet to only show items with "Other" in the Type column, but we'd like to exclude any item containing the word case.

1.    Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.

2.    Click the drop-down arrow for the column you wish to filter. In our example, we'll filter column C.

 

Clicking the drop-down arrow for column C

3.    The Filter menu will appear. Hover the mouse over Text Filters and then select the desired text filter from the drop-down menu. In our example, we'll choose Does Not Contain to view data that does not contain specific text.

 

Selecting a text filter

4.    The Custom AutoFilter dialog box will appear. Enter the desired text to the right of the filter, then click OK. In our example, we'll type case to exclude any items containing that word.

Applying a text filter

5.    The data will be filtered by the selected text filter. In our example, our worksheet now displays items in the "Other" category that do not contain the word "case".

 

The applied text filter

 3.3.4.3 Using Advanced Date Filters

Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, between two dates, and more. In this example, we will use advanced date filters to view only equipment that has been checked out today.

1.    Select the Data tab, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.

2.    Click the drop-down arrow for the column you wish to filter. In our example, we will filter column D to view only a certain range of dates.

 

Clicking the drop-down arrow for column D

3.    The Filter menu will appear. Hover the mouse over Date Filters and then select the desired date filter from the drop-down menu. In our example, we'll select Today to view equipment that has been checked out on today's date.

 

Selecting a date filter

4.    The worksheet will be filtered by the selected date filter. In our example, we can now see which items have been checked out today.

 

The applied date filter

  3.3.4 4 Use Advanced Number Filters

Advanced number filters allow you to manipulate numbered data in many different ways. In this example, we will display only certain kinds of equipment based on the range of ID numbers.

1.    Select the Data tab on the Ribbon, then click the Filter command. A drop-down arrow will appear in the header cell for each column. Note: If you've already added filters to your worksheet, you can skip this step.

2.    Click the drop-down arrow for the column you wish to filter. In our example, we'll filter column A to view only a certain range of ID numbers.

 

Clicking the drop-down arrow for column A

3.    The Filter menu will appear. Hover the mouse over Number Filters and then select the desired number filter from the drop-down menu. In our example we will choose Between to view ID numbers between a specific number range.

 

Selecting a number filter

4.    The Custom AutoFilter dialog box will appear. Enter the desired number(s) to the right of each filter, then click OK. In our example, we want to filter for ID numbers greater than or equal to 3000, but less than or equal to 4000, which will display ID numbers in the 3000-4000 range.

Applying a number filter and clicking OK

5.    The data will be filtered by the selected number filter. In our example, only items with an ID number between3000 and 4000 are visible. 

 

The applied number filter

Exercise 3.3

1.    Open an existing Excel workbook. 

2.    Apply a filter to a column. If you are using the example, filter the Type column (column B) so it displays only laptops and cameras.

3.    Add another filter by searching. If you are using the example, search for EDI brand equipment in the Equipment Detail column (column C).

4.    Clear both filters.

5.    Use an advanced text filter to view data that does not contain a certain word or phrase. If you are using the example, display data that does not contain the word saris (this should exclude all Saris brand equipment).

6.    Use an advanced date filter to view data from a certain time period. If you are using the example, display only the equipment that was checked out in September 2013.

7.    Use an advanced number filter to view numbers less than a certain amount. If you are using the example, display all items with an ID# below 3000.

3. 4 Groups and Subtotals

Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.

3. 4.1 Grouping Rows or Columns

1.    Select the rows or columns that you wish to group. In this example, we'll select columns A, B and C.

Selecting

columns to group

2.    Select the Data tab on the Ribbon, then click the Group command.

 

Clicking the Group command

3.    The selected rows or columns will be grouped. In our example, columns A, B and C are grouped together.

The grouped columns

To ungroup data, select the grouped rows or columns and then click the Ungroup command.

 

Clicking the Ungroup command

3.4.2 Hide and Show Groups

1. To hide a group, click the Hide Detail button.

Hiding a group

Clicking the Show Detail button to show the hidden group

  3.4. 3 Creating Subtotals

The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. The Subtotal command will create a hierarchy of groups, known as an outline, to help organize your worksheet.

Your data must be correctly sorted before using the Subtotal command,  To Create a Subtotal:

In our example, we will use the Subtotal command with a t-shirt order form to determine how many t-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each t-shirt size and then count the total number of shirts in each group.

1. First, sort your worksheet by the data you wish to subtotal. In this example, we will create a subtotal for each tshirt size, so our worksheet has been sorted by t-shirt size from smallest to largest.

The worksheet sorted by t-shirt size 2. Select the Data tab and then click the Subtotal command.

 

Clicking the Subtotal command

3.    The Subtotal dialog box will appear. Click the drop-down arrow for the At each change in: field to select the column you wish to subtotal. In our example, we'll select T-Shirt Size.

4.    Click the drop-down arrow for the Use function: field to select the function you wish to use. In our example, we'll select COUNT to count the number of shirts ordered in each size.

5.    In the Add subtotal to: field, select the column where you want the calculated subtotal to appear. In our example, we'll select T-Shirt Size.

6.    When you're satisfied with your selections, click OK.

Creating a subtotal

7.    The worksheet will be outlined into groups and the subtotal will be listed below each group. In our example, the data is now grouped by t-shirt size and the number of shirts ordered in that size appears below each group.

The outlined and subtotaled data

3. 4.4 Viewing Groups by Level

When you create subtotals, your worksheet is divided into different levels. You can switch between these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons  to the left of the worksheet. In our example, we'll switch between all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight.

1.    Click the lowest level to display the least detail. In our example, we'll select level 1, which contains only the grand count, or total number of t-shirts ordered.

 

Viewing data at the lowest level

2.    Click the next level to expand the detail. In our example, we'll select level 2, which contains each subtotal row but hides all other data from the worksheet.

 

Viewing data at the next level

3.    Click the highest level to view and expand all of your worksheet data. In our example, we'll select level 3.

 

Viewing data at the highest level

You can also use the Show and Hide Detail buttons to show and hide the groups within the outline.

 

3. 4.5 Remove Subtotals

Sometimes, you may not want to keep subtotals in your worksheet, especially if you want to reorganize the data in different ways. If you no longer wish to use subtotaling, you'll need remove it from your worksheet.

1.    Select the Data tab and then click the Subtotal command.

 

Clicking the Subtotal command 

2.    The Subtotal dialog box will appear. Click Remove All.

Removing subtotaling

3.    All worksheet data will be ungrouped and the subtotals will be removed.

 

To remove all groups without deleting the subtotals, click the Ungroup command drop-down arrow and then choose Clear Outline.

Removing all groups

Exercise 3.4

1.    Open an existing Excel workbook. 

2.    Try grouping a range of rows or columns together. If you are using the example, group columns D and E.

3.    Use the Show and Hide Detail buttons to hide and unhide the group.

4.    Try ungrouping the group. If you are using the example, ungroup columns D and E.

5.    Outline your worksheet using the Subtotal command. If you are using the example, outline by t-shirt size.

6.    Remove subtotaling from your worksheet.

3.5 Tables

Once you've entered information into a worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, but they'll also help to organize your content and make your data easier to use. Excel includes several tools and pre-defined table styles, allowing you to create tables quickly and easily.

3.5.1 Formatting Data as a Table

1.    Select the cells you want to format as a table. In our example, we'll select the cell range A4:D10.

 

Selecting a cell range to format as a table

2.    From the Home tab, click the Format as Table command in the Styles group 

Clicking the Format as Table command

3.    Select a table style from the drop-down menu.

Choosing a table style

4.    A dialog box will appear, confirming the selected cell range for the table.

5.    If your table has headers, check the box next to My table has headers, then click OK.

 

Clicking OK

6.    The cell range will be formatted in the selected table style.

The cell range formatted as a table

 3.5.2 Modifying Tables

It's easy to modify the look and feel of any table after adding it to a worksheet. Excel includes many different options for customizing a table, including adding rows or columns, changing the table style, and more.

3.5.2.1 Add Rows or Columns to a Table

If you need to fit more content in your table, Excel allows you to modify the table size by including additional rows and columns. There are two simple ways to change the table size:

•    Begin typing new content after the last row or column in the table. The row or column will be included in the table automatically.

 

Typing a new row below an existing table

•    Click, hold and drag the bottom-right corner of the table to create additional rows or columns.

 

Dragging the table border to create more rows

 3.5.2.2 Changing the Table Style

1.    Select any cell in your table, then click the Design tab.

 

Clicking the Design tab

2.    Locate the Table Styles group and click the More drop-down arrow to see all of the table styles.

Clicking the More drop-down arrow 3. Select the desired style.

Choosing a new table style 4. The selected table style will appear.

The new table style

 3.5.2.3 Modify the Table Style Options

You can turn various options on or off to change the appearance of any table. There are six options: Header Row, Total Row, Banded Rows, First Column, Last Column, and Banded Columns.

1.    Select any cell in your table.

2.    From the Design tab, check or uncheck the desired options in the Table Style Options group. In our example, we'll check Total Row to automatically include a total for our table.

Checking the Total Row option

3.    The table style will be modified. In our example, a new row has been added to the table with a formula that will automatically calculate the total value of the cells in column D.

The table with a total row

These options can affect your table style in various ways, depending on the type of content in your table. You may need to experiment with a few different options to find the exact style you want.

3.5. 3 Removing a Table

Sometimes, you may not want to use the additional features included with tables, such as the Sort and Filter dropdown arrows. You can remove a table from the workbook while still preserving the table's formatting elements, like font and cell color. 

1.    Select any cell in your table. The Design tab will appear.

2.    Click the Convert to Range command in the Tools group.

 

Clicking Convert to Range 3. A dialog box will appear. Click Yes.

 

Removing a table

4. The range will no longer be a table, but the cells will retain their data and formatting.

The cell range formatted as a normal range

Exercise 3.5

1.    Open an existing Excel workbook. 

2.    Format a range of cells as a table. If you are using the example, format the cell range A1:E13.

3.    Add a row or column to the table.

4.    Choose a new table style.

5.    Change the table style options. If you are using the example, add a total row.

6.    Remove the table.

3.6 Charts

It can often be difficult to interpret Excel workbooks that contain a lot of data. Charts allow you to illustrate your workbook data graphically, which make it easy to visualize comparisons and trends.

3.6.1 Understanding Charts

Excel has many different types of charts, allowing you to choose the one that best fits your data. In order to use charts effectively, you'll need to understand how different charts are used.

Click the arrows in the slideshow below to learn more about the types of charts in Excel.

?

Excel has a wide variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.

In addition to chart types, you'll need to understand how to read a chart. Charts contain several different elements, or parts that can help you interpret the data.

Click the buttons in the interactive below to learn about the different parts of a chart.

3.6.2 Inserting a Chart

1.    Select the cells you want to chart, including the column titles and row labels. These cells will be the source data for the chart. In our example, we'll select cells A1:F6.

 

Selecting cells A1:F6

2.    From the Insert tab, click the desired Chart command. In our example, we'll select Column.

Clicking the Column chart command

3.    Choose the desired chart type from the drop-down menu.

Choosing a chart type 4. The selected chart will be inserted in the worksheet.

 

The inserted chart

If you're not sure which type of chart to use, the Recommended Charts command will suggest several different charts based on the source data. 

 

 3.6.3 Chart Layout and Style

After inserting a chart, there are several things you might want to change about the way your data is displayed. It's easy to edit a chart's layout and style from the Design tab.

•    Excel allows you to add chart elements—such as chart titles, legends, and data labels—to make your chart easier to read. To add a chart element, click the Add Chart Element command on the Design tab and then choose the desired element from the drop-down menu.

Adding a chart title

•    To edit a chart element, like a chart title, simply double-click the placeholder and begin typing.

Editing the chart title placeholder text

•    If you don't want to add chart elements individually, you can use one of Excel's pre-defined layouts. Simply click the Quick Layout command and then choose the desired layout from the drop-down menu.

Choosing a chart layout

•    Excel also includes several different chart styles, which allow you to quickly modify the look and feel of your chart. To change the chart style, select the desired style from the Chart styles group.

 

Choosing a new chart style

 

You can also use the chart formatting shortcut buttons to quickly add chart elements, change the chart style, and filter the chart data.

 

Chart formatting shortcuts

3.6.4 Other Chart Options

There are lots of other ways to customize and organize your charts. For example, Excel allows you to rearrange a chart's data, change the chart type, and even move the chart to a different location in the workbook.

 3.6.4.1 Switching Row and Column Data
 

Sometimes, you may want to change the way charts group your data. For example, in the chart below, the Book Sales data are grouped by year, with columns for each genre. However, we could switch the rows and columns so that the chart will group the data by genre, with columns for each year. In both cases, the chart contains the same data—it's just organized differently.

The data grouped by year, with columns for each genre

1.    Select the chart you wish to modify.

2.    From the Design tab, select the Switch Row/Column command.

 

Clicking the Switch Rows/Columns command

3.    The rows and columns will be switched. In our example, the data is now grouped by genre, with columns for each year.

The switched row and column data

 3.6.4.2 Changing the Chart Type

If you find that your data isn't well suited to a certain chart, it's easy to switch to a new chart type. In our example, we'll change our chart from a Column chart to a Line chart.

1.    From the Design tab, click the Change Chart Type command.

 

Clicking the Change Chart Type command

2.    The Change Chart Type dialog box will appear. Select a new chart type and layout, then click OK. In our example, we'll choose a Line chart.

 

Choosing a new chart type

3.    The selected chart type will appear. In our example, the Line chart makes it easier to see trends in the sales data over time.

The new chart type

 3.6.4.3 Moving a Chart

Whenever you insert a new chart, it will appear as an object on the same worksheet that contains its source data.

Alternatively, you can move the chart to a new worksheet to help keep your data organized.

1.    Select the chart you wish to move.

2.    Click the Design tab and then select the Move Chart command.

Clicking the Move Chart command

3.    The Move Chart dialog box will appear. Select the desired location for the chart. In our example, we'll choose to move it to a New sheet, which will create a new worksheet.

4.    Click OK.

Moving the chart to a new worksheet

5.    The chart will appear in the selected location. In our example, the chart now appears on a new worksheet.

The chart on its own worksheet

Exercise 3.6

1.    Open an existing Excel workbook

2.    Use worksheet data to create a chart. If you are using the example, use the cell range A1:F6 as the source data for the chart.

3.    Change the chart layout. If you are using the example, select Layout 8.

4.    Apply a chart style.

5.    Move the chart. If you are using the example, move the chart to a new worksheet named Book Sales Data: 2008-2012.

3.7 Sparklines

Sometimes, you might want to analyze and view trends in your data without creating an entire chart. Sparklines are miniature charts that fit into a single cell. Since they're so compact, it's easy to include lots of sparklines in a workbook.

 3.7.1 Types of Sparklines

There are three different types of sparklines: Line, Column, and Win/Loss. Line and Column work the same as line and column charts. Win/Loss is similar to Column, except it only shows whether each value is positive ornegative, instead of how high or low the values are. All three types can display markers at important points, such as the highest and lowest points, to make them easier to read.

Line

Column

Win/Loss

3.7.2 Why Use Sparklines?

Sparklines have certain advantages over charts. For example, imagine you have 1,000 rows of data. A traditional chart would have 1,000 data series to represent all of the rows, making relevant data hard to find. But if you placed a sparkline on each row, it will be right next to its source data, making it easy to see  relationships and trends for multiple data series at the same time.

In the image below, the chart is extremely cluttered and hard to follow, but the sparklines allow you to clearly follow each salesperson's data.

 

The same data visualized in a chart and in sparklines

Sparklines are ideal for situations where you need a clear overview of the data at a glance and where you don't need all the features of a full chart. On the other hand, charts are ideal for situations where you want to represent the data in greater detail, and they are often better for comparing different data series.

 3.7.3 Creating Sparklines

Generally, you will have one sparkline for each row, but you can create as many as you want in any location. Just like formulas, it's usually easiest to create a single sparkline and then use the fill handle to create sparklines for the adjacent rows. In our example, we'll create sparklines to help visualize trends in sales over time for each salesperson.

1.    Select the cells that will serve as the source data for the first sparkline. In our example, we'll select the cell range B2:G2.

 

Selecting cells B2:G2

2.    Select the Insert tab, then choose the desired Sparkline from the Sparklines group. In our example, we'll choose Line.

Clicking the Line command

3.    The Create Sparklines dialog box will appear. Use the mouse to select the cell where the sparkline will appear, then click OK. In our example, we'll select cell H2 and the cell reference will appear in the Location Range: field.

 

Selecting a location for the sparkline and clicking OK

4.    The sparkline will appear in the specified cell.

A sparkline

5.    Click, hold and drag the fill handle to create sparklines in adjacent cells.

 

Dragging the fill handle to create sparklines in adjacent cells

6.    Sparklines will be created for the selected cells. In our example, the sparklines show clear trends in sales over time for each salesperson in our worksheet. 

 

Sparklines filled to multiple rows

 3.7.4 Modifying Sparklines

It's easy to change the way sparklines appear in your worksheet. Excel allows you to customize a sparkline'smarkers, style, type, and more.

 3.7.4.1 To Display Markers

Certain points on a sparkline can be emphasized with markers, or dots, making the sparkline more readable. For example, in a line with a lot of ups and downs, it might be difficult to tell which values are the highest and lowest points. Showing the High Point and Low Point will make them easier to identify.

1.    Select the sparkline(s) that you want to change. If they are grouped in adjacent cells, you'll only need to click on one sparkline to select them all.

Selecting a group of sparklines

2.    From the Design tab, select the desired option(s) from the Show group. In our example, we'll select HighPoint and Low Point.

Showing the High and Low points on the sparklines

3.    The sparkline(s) will update to show the selected markers.

The sparklines with high and low markers

    3.7.4.2 Changing the Sparkline Style

1.    Select the sparkline(s) that you want to change.

2.    From the Design tab, click the More drop-down arrow.

 

Clicking the More drop-down arrow 3. Choose the desired style from the drop-down menu.

Choosing a sparkline style

4. The sparkline(s) will update to show the selected style.

The new sparkline style

   3.7.4.3 Changing the Sparkline Type

1.    Select the Sparkline(s) that you want to change.

2.    From the Design tab, select the desired Sparkline type. In our example, we'll select Column.

 

Choosing a new sparkline type 3. The sparkline(s) will update to reflect the new type.

The new sparkline type

Some sparkline types will be better suited for certain types of data. For example, Win/Loss is best suited for data where there could be positive and negative values (such as net earnings).

   3.7.5 Changing the Display Range

 

Selecting a group of sparklines

2.    From the Design tab, click the Axis command. A drop-down menu will appear.

3.    Below Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options, select Same for All Sparklines.

Modifying the sparklines' display range

4.    The sparklines will update to reflect the new display range. In our example, we can now use the sparklines to compare trends for each salesperson.

The updated display range

Exercise 3.7

1.    Open an existing Excel workbook.

2.    Create a Sparkline on the first row of data. If you are using the example, create a Sparkline for the first salesperson on row 3.

3.    Use the fill handle to create spark lines for the remaining rows.

4.    Create markers for the High Point and Low Point.

5.    Change the Sparkline type.

6.    Change the Display Range to make the spark lines easier to compare.

CHAPTER FOUR

4. Doing More with Excel

 4.1 Track Changes and Comments

 4.1.1 Introduction

Suppose someone asked you to proofread or collaborate on a workbook. If you had a printed copy, you might use a red pen to edit cell data, mark spelling errors, or add comments in the margins. Excel allows you to do all of these things electronically using the Track Changes and Comments features.

4.1.2 Understanding Track Changes

When you turn on the Track Changes feature, every cell you edit will be highlighted with a unique border and indicator. Selecting a marked cell will show the details of the change. This allows you and other reviewers to see what's been changed before accepting the revisions permanently.

A worksheet with tracked changes

There are some changes that Excel cannot track. Before using this feature, you may want to review Microsoft's list ofchangesthatExceldoesnottrackorhighlight.

You cannot use Track Changes if your workbook includes tables. To remove a table, select it, click the Design tab, and then click Convert to Range.

4.1.3 Turning on Track Changes

1.    From the Review tab, click the Track Changes command and then select Highlight Changes from the dropdown menu.

 

Selecting Highlight Changes

2.    The Highlight Changes dialog box will appear. Check the box next to Track changes while editing. Verify the box is checked for Highlight changes on screen, then click OK.

Turning on Track Changes 3. If prompted, click OK to allow Excel to save your workbook.

Clicking OK to save the workbook

4.    Track Changes will be turned on. A triangle and border color will appear in any cell you edit. If there are multiple reviewers, each person will be assigned a different color.

5.    Select the edited cell to see a summary of the tracked changes. In our example below, we've changed the content of cell E11 from "?" to "Tyler".

4

 

Using the Track Changes feature

When you turn on Track Changes, your workbook will be "shared" automatically. Shared workbooks are designed to be stored where other users can access and edit the workbook at the same time, such as a network. However, you can also track changes in a local or personal copy, as seen throughout this lesson.

4.1. 4 Listing Changes on a Separate Worksheet

You can also view changes on a new worksheet, sometimes called the Tracked Changes history. The history lists everything in your worksheet that has been changed, including the "old value" (previous cell content) and the "new value" (current cell content).

1.    Save your workbook.

2.    From the Review tab, click the Track Changes command and then select Highlight Changes from the dropdown menu.

 

Selecting Highlight Changes

3.    The Highlight Changes dialog box will appear. Check the box next to List changes on a new sheet, then click OK.

Listing changes on a new worksheet and clicking OK

4.    The tracked changes will be listed on their own worksheet, called History.

 

A summary of all changes on their own worksheet

To remove the History worksheet from your workbook, you can either save your workbook again or uncheck the box next to List changes on a new sheet in the Highlight Changes dialog box.

  4.1.5 Reviewing Changes

Tracked changes are really just "suggested" changes. To become permanent, the changes must be accepted. On the other hand, the original author may disagree with some of the tracked changes and choose to reject them. To Review Tracked Changes

1.    From the Review tab, click Track Changes and then select Accept/Reject Changes from the drop-down menu.

 

Selecting Accept/Reject Changes 

2.    If prompted, click OK to save your workbook.

3.    A dialog box will appear. Make sure the box next to the When: field is checked and set to Not yet reviewed, then click OK

Clicking OK

4.    A dialog box will appear. Click Accept or Reject for each change in the workbook. Excel will move through each change automatically until you have reviewed them all.

 

Accepting a change

5.    Even after accepting or rejecting changes, the tracked changes will still appear in your workbook. To remove them completely, you'll need to turn off Track Changes. From the Review tab, click Track Changes and then select Highlight Changes from the drop-down menu.

 

Clicking Highlight Changes

6.    A dialog box will appear. Uncheck the box next to Track changes while editing, then click OK.

Turning off Track Changes

7.    Click Yes to confirm that you want to turn off Track Changes and stop sharing your workbook.

 

Confirming that Track Changes will be turned off

 

To accept or reject all the changes at once, click Accept All or Reject All in the Accept or Reject Changes dialog box.

Turning off Track Changes will remove any tracked changes in your workbook. You will not be able to view, accept, or reject changes; instead, all changes will all be accepted automatically. Always review the changes in your worksheet before turning off Track Changes.

   4.1.6 Comments

 

Sometimes, you may want to add a comment to provide feedback instead of editing the contents of a cell. While often used in combination with Track Changes, you don't necessarily need to have Track Changes turned on to use comments.

 4.1.6.1 Adding a Comment

1. Select the cell where you want the comment to appear. In our example, we'll select cell E8.

 

Selecting cell E8 2. From the Review tab, click the New Comment command.

Clicking the New Comment command

3.    A comment box will appear. Type your comment, then click anywhere outside the box to close the comment.

 

Adding a comment

4.    The comment will be added to the cell, represented by the red triangle in the top-right corner.

 

The added comment 5. Select the cell again to view the comment.

 

Selecting a cell to view a comment

   4.1.6.2 Editing a Comment

1.    Select the cell containing the comment you wish to edit.

2.    From the Review tab, click the Edit Comment command.

Clicking the Edit Comment command

3.    The comment box will appear. Edit the comment as desired, then click anywhere outside the box to close the comment.

 

Editing a comment

4.1.6.3 Showing or Hiding Comments

1.    From the Review tab, click the Show All Comments command to view every comment in your worksheet at the same time.

Clicking the Show All Comments command

2.    All comments in the worksheet will appear. Click the Show All Comments command again to hide them.

Viewing all comments at the same time

 

You can also choose to show and hide individual comments by selecting the desired cell and then clicking the Show/Hide Comment command.

Showing and hiding individual comments

4.1.6.4 Deleting a Comment

1. Select the cell containing the comment you wish to delete. In our example, we'll select cell E8.

 

Selecting cell E8 2. From the Review tab, click the Delete command in the Comments group.

Clicking the Delete command 3. The comment will be deleted.

After deleting the comment

Exercise 4.1

1.    Open an existing Excel workbook. 

2.    Turn on Track Changes.

3.    Delete, add, or edit the text in several cells. Notice how the edited cells are highlighted.

4.    Accept all of the tracked changes and then turn off Track Changes.

5.    Add a few comments to different cells in your worksheet.

6.    Show all of the comments, then hide them.

        4.2 Finalizing and Protecting Workbooks            

Before sharing a workbook, you'll want to make sure that it doesn't include any spelling errors or information that you wish to keep private. Fortunately, Excel includes several tools to help finalize and protect your workbook, such as Spell Check and the Document Inspector.

4.2.1 Using Spell Check

1.    From the Review tab, click the Spelling command.

 Clicking the Spelling command

2.    The Spelling dialog box will appear. For each spelling error in your worksheet, Spell Check will try to offer suggestions for the correct spelling. Choose a suggestion and then click Change to correct the error.

 

Using Spell Check to correct spelling errors

3.    A dialog box will appear after reviewing all spelling errors. Click OK to close Spell Check.

Closing Spell Check

If there are no appropriate suggestions, you can also enter the correct spelling manually.

 4.2.2 Ignoring Spelling "Errors"

Spell Check isn't always correct. It will sometimes mark certain words as incorrect, even if they're spelled correctly. This often happens with names, which may not be in the dictionary. You can choose not to change a spelling "error" using one of three options:

•    Ignore Once: This will skip the word without changing it.

•    Ignore All: This will skip the word without changing it and also skip all other instances of the word in your worksheet.

•    Add: This adds the word to the dictionary so it will never appear as an error again. Make sure the word is spelled correctly before choosing this option.

4.2.3 Document Inspector

Whenever you create or edit a workbook, certain personal information may be added to the file automatically. You can use the Document Inspector to remove this kind of information before sharing a workbook with others.

Because some changes may be permanent, it's a good idea to save an additional copy of your workbook before using the Document Inspector to remove information. To Use the Document Inspector:

1.    Click the File tab to access Backstage view.

2.    From the Info pane, click Check for Issues and then select Inspect Document from the drop-down menu.

 

Clicking Inspect Document

3.    The Document Inspector will appear. Check or uncheck the boxes, depending on the content you wish to review, then click Inspect. In our example, we'll leave everything selected.

Inspecting the workbook

4.    The inspection results will appear. In our example, we can see our workbook contains some personal information, so we'll click Remove All to remove that information from the workbook.

Removing personal information from the workbook

5.    When you're done, click Close.

Closing the Document Inspector

  4.2.4 Protecting Your Workbook

By default, anyone with access to your workbook will be able to open, copy, and edit its content unless you protect it. There are many different ways to protect a workbook, depending on your needs.

To Protect Your Workbook:

1.    Click the File tab to access Backstage view.

2.    From the Info pane, click the Protect Workbook command.

3.    In the drop-down menu, choose the option that best suits your needs. In our example, we'll select Mark as Final. Marking your workbook as final is a good way to discourage others from editing the workbook, while the other options give you even more control, if needed.

 

Selecting Mark as Final 4. A dialog box will appear prompting you to save. Click OK.

Clicking OK to save the workbook

5. Another dialog box will appear. Click OK.

 

Clicking OK 6. The workbook will be marked as final.

A workbook marked as final

Marking a workbook as final will not prevent someone from editing it. If you want to prevent people from editing it, you can use the Restrict Access option instead.

Exercise 4.2

1.    Open an existing Excel workbook. 

2.    Run the Spell Check to correct any spelling errors in the workbook.

3.    Use the Document Inspector to check the workbook. If you are using the example, remove all personal information from the workbook.

4.    Protect the workbook by marking it as final.

   4.3 Conditional Formatting

Imagine that you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides another way to visualize data and make worksheets easier to understand.

 4.3.1 Understanding Conditional Formatting

Conditional formatting allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the cell value. To do this, you'll need to create a conditional formatting rule. For example, a conditional formatting rule might be: "If the value is less than $2,000, color the cell red." By applying this rule, you'd be able to quickly see which cells contain values under $2,000.

Conditional formatting marking values less than $2000

 4.3.2 Creating a Conditional Formatting Rule

In our example, we have a worksheet containing sales data and we'd like to see which salespeople are meeting their monthly sales goals. The sales goal is $4,000 per month, so we'll create a conditional formatting rule for any cells containing a value higher than 4000.

1.    Select the desired cells for the conditional formatting rule.

Selecting the desired cells

2.    From the Home tab, click the Conditional Formatting command. A drop-down menu will appear.

3.    Hover the mouse over the desired conditional formatting type and then select the desired rule from the menu that appears. In our example, we want to highlight cells that are greater than $4,000.

Selecting a conditional formatting rule

4.    A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll enter 4000 as our value.

5.    Select a formatting style from the drop-down menu. In our example, we'll choose Green Fill with Dark Green Text, then click OK.

Creating a conditional formatting rule

6.    The conditional formatting will be applied to the selected cells. In our example, it's easy to see which salespeople reached the $4,000 sales goal for each month. 

 

Conditional formatting applied to the data

You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data.

A worksheet with multiple conditional formatting rules

   4.3.3 Removing Conditional Formatting

1.    Click the Conditional Formatting command. A drop-down menu will appear.

2.    Hover the mouse over Clear Rules and choose which rules you wish to clear. In our example, we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.

Removing conditional formatting rules 3. The conditional formatting will be removed.

The conditional formatting removed from the worksheet

Click Manage Rules to edit or delete individual rules. This is especially useful if you have applied multiple rules to a worksheet.

Deleting an individual rule

   4.3.4 Conditional Formatting Presets

Excel has a number of pre-defined styles, or presets, that you can use to quickly apply conditional formatting to your data. They are grouped into three categories:

•    Data Bars are horizontal bars added to each cell, much like a bar graph.

Data Bars

•    Color Scales change the color of each cell based on its value. Each color scale uses a two or three color gradient. For example, in the Green - Yellow - Red color scale, the highest values are green, average values are yellow, and the lowest values are red.

Color Scales ?            Icon Sets add a specific icon to each cell based on its value.

Icon Sets

     4.3. 5 Using Preset Conditional Formatting

1.    Select the desired cells for the conditional formatting rule.

Selecting the desired cells

2.    Click the Conditional Formatting command. A drop-down menu will appear.

3.    Hover the mouse over the desired preset and then choose a preset style from the menu that appears.

Applying a preset conditional formatting rule 4. The conditional formatting will be applied to the selected cells.

The applied conditional formatting preset

 Exercise 4.3

1.    Open an existing Excel workbook

2.    Apply conditional formatting to a range of cells with numerical values. If you are using the example, apply a rule for the sales data (cells B3:G23) that will fill cells with green if their values are over $9,000.

3.    Apply a second conditional formatting rule to the same set of cells. If you are using the example, apply a preset conditional formatting rule.

4.    Clear all conditional formatting rules from the worksheet.

 4.4 Pivot Tables

When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways.

  4.4.1 Using PivotTables to Answer Questions

Suppose we wanted to answer the question: "What is the amount sold by each salesperson?" for the sales data in the example below. Answering this question could be very time-consuming and difficult—each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with.

A worksheet containing sales data

Fortunately, a PivotTable can instantly calculate and summarize the data in a way that's both easy to read and manipulate. When we're done, the PivotTable will look something like this:

A completed PivotTable

Once you've created a PivotTable, you can use it to answer different questions by rearranging, or pivoting, the data. For example, if we wanted to answer the question: "What is the total amount sold in each month?" we could modify our PivotTable to look like this:

Pivoting data to answer different questions

    4.4.2 Create a PivotTable

1.    Select the table or cells (including column headers) containing the data you want to use.

Selecting cells for a PivotTable

2.    From the Insert tab, click the PivotTable command.

Clicking the PivotTable command

3.    The Create PivotTable dialog box will appear. Choose your settings and then click OK. In our example, we'll use Table1 as our source data and place the PivotTable on a new worksheet.

Creating a PivotTable

4.    A blank PivotTable and Field List will appear on a new worksheet.

 

A blank PivotTable on its own worksheet

5.    Once you create a PivotTable, you'll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Field List, check the box for each field you wish to add. In our example, we want to know the total amount sold by each salesperson, so we'll check the Salesperson and Order Amount fields.

Checking the desired fields

6.    The selected fields will be added to one of the four areas below the Field List. In our example, the Salesperson field has been added to the Rows area, while the Order Amount has been added to the Values area. Alternatively, you can click, hold and drag a field to the desired area.

Adding fields to the PivotTable

7.    The PivotTable will calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson.

 

The PivotTable calculating the selecting fields

 

Just like with normal spreadsheet data, you can sort the data in a PivotTable using the Sort & Filter command in the Home tab. You can also apply any type of number formatting that you want. For example, you may want to change the Number Format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable.

A sorted and formatted PivotTable

4.4. 3 Pivoting Data

One of the best things about PivotTables is that they can quickly pivot, or reorganize, data, allowing you to look at your worksheet data in different ways. Pivoting data can help you answer different questions and even experiment with the data to discover new trends and patterns.

In our example, we used the PivotTable to answer the question "What is the total amount sold by each salesperson?" But now we'd like to answer a new question: "What is the total amount sold in each month?" We can do this by simply changing the field in the Rows area.

 4.4. 3.1 Changing Rows

1.    Click, hold and drag any existing fields out of the Rows area. The field will disappear.

Removing a field

2.    Drag a new field from the Field List into the Rows area. In our example, we'll use the Month field.

Adding a field

3.    The PivotTable will adjust, or pivot, to show the new data. In our example, it now shows the total Order Amount for each month.

The updated PivotTable

4.4. 3.2 Adding Columns

So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you'll need to add a field to the Columns area.

1.    Drag a field from the Field List into the Columns area. In our example, we'll use the Region field.

Adding a field to the Column area

2.    The PivotTable will include multiple columns. In our example, there is now a column for each region.

The PivotTable with columns

   4.4.4 Filters

Sometimes, you may want focus on just a certain section of your data. Filters can be used to narrow down the data in your PivotTable, allowing you to view only the information that you need.

 4.4.4.1 Adding a Filter

In our example, we'll filter out certain salespeople to determine how they affect the total sales.

1.    Drag a field from the Field List to the Filters area. In this example, we'll use the Salesperson field.

Adding a field to the Filters area

2.    The filter will appear above the PivotTable. Click the drop-down arrow, then check the box next to Select Multiple Items.

 

Checking the box for Select Multiple Items

3.    Uncheck the box for any items you don't want to include in the PivotTable. In our example, we'll uncheck the boxes for a few different salespeople, then click OK.

Choosing data to filter and clicking OK 4. The PivotTable will adjust to reflect the changes.

The updated PivotTable

   4.4.5 Slicers

Slicers make filtering data in PivotTables even easier. Slicers are basically just filters, but they're easier and faster to use, allowing you to instantly pivot your data. If you frequently filter your PivotTables, you may want to consider using slicers instead of filters.

 4.4.5.1 Adding a Slicer

1.    Select any cell in the PivotTable.

2.    From the Analyze tab, click the Insert Slicer command.

Clicking the Insert Slicer command

3.    A dialog box will appear. Select the desired field. In our example, we'll select Salesperson, then click OK.

Choosing a field and clicking OK

4.    The slicer will appear next to the PivotTable. Each selected item will be highlighted in blue. In the example below, the slicer contains a list of all of the different salespeople, and six of them are currently selected.

The inserted slicer

5.    Just like filters, only selected items are used in the PivotTable. When you select or deselect items, the PivotTable will instantly reflect the changes. Try selecting different items to see how they affect the PivotTable. Press and hold the Ctrl key on your keyboard to select multiple items from a slicer.

Selecting items from the slicer

You can also click the Filter icon in the upper-right corner to select all items from the slicer at once.

  4.4.6 Pivot Charts

Pivot Charts are like regular charts, except they display data from a PivotTable. Just like regular charts, you'll be able to select a chart type, layout and style that will best represent the data. 

4.4.6.1 Creating a PivotChart

In this example, our PivotTable is showing each person's total sales per month. We'll use a PivotChart so that we can see the information more clearly.

1. Select any cell in your PivotTable.

 

Clicking a cell in the PivotTable 2. From the Insert tab, click the PivotChart command.

 

Clicking the PivotChart command

3. The Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK.

 

Choosing a chart type and clicking OK 4. The PivotChart will appear.

 

The inserted PivotChart

Try using slicers or filters to change the data that is displayed. The PivotChart will automatically adjust to show the new data.

 

Exercise 4.4

1.    Open an existing Excel workbook. 

2.    Create a PivotTable using the data in the workbook.

3.    Experiment by placing different fields in the Rows and Columns areas.

4.    Filter the report with a slicer.

5.    Create a PivotChart.

6.    If you are using the example, use the PivotTable to answer the question, "Which salesperson sold the lowest amount in January?" Hint: First decide which fields you need in order to answer the question

 4.5 What-If Analysis

Excel includes many powerful tools to perform complex mathematical calculations, such as what-if analysis. This feature can help you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you will learn how to use a what-if analysis tool called Goal Seek.

 4.5.1 Goal Seek

Whenever you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way: It lets you start with the desired result, and it calculates the input value that will give you that result. We'll use a couple of examples to show how to use Goal Seek.

To Use Goal Seek (Example 1):

Imagine that you're enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class.

In the image below, you can see that the grades on the first four assignments are 58, 70, 72, and 60. Even though we don't know what the fifth grade will be, we can go ahead and write a formula or function that calculates the final grade. In this case, each assignment is weighted equally, so all we have to do is average all five grades by typing=AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade that we'll need to make on that assignment.

Using a function to calculate the final grade for the class

1.    Select the cell whose value you wish to change. Whenever you use Goal Seek, you'll need to select a cell that already contains a formula or function. In our example, we'll select cell B7 because it contains the formula=AVERAGE (B2:B6).

Selecting cell B7

2.    From the Data tab, click the What-If Analysis command and then select Goal Seek from the drop-down menu.

 

Selecting Goal Seek from the drop-down menu

3.    A dialog box will appear with three fields:

o    Set cell: The cell that will contain the desired result. In our example, cell B7 is already selected. 

o    To value: The desired result. In our example, we'll enter 70 because we need to earn at least that to pass the class.

o    By changing cell: The cell where Goal Seek will place its answer. In our example, we'll select cell B6, because we want to determine the grade we need to earn on the final assignment.

4.    When you're done, click OK.

Entering the desired values into the dialog box and clicking OK 5. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.

Clicking OK

6. The result will appear in the specified cell. In our example, Goal Seek calculated that we will need to score at least a 90 on the final assignment to earn a passing grade.

The completed Goal Seek and calculated value

 

Selecting cell B4

2.    From the Data tab, click the What-If Analysis command and then select Goal Seek from the drop-down menu.

 

Selecting Goal Seek from the drop-down menu

3.    A dialog box will appear with three fields:

o Set cell: The cell that will contain the desired result. In our example, cell B4 is already selected.  o To value: The desired result. In our example, we'll enter 500 because we only want to spend $500. o By changing cell: The cell where Goal Seek will place its answer. In our example, we'll select cell B3, because we want to know how many guests we can invite without spending more than $500.

4.    When you're done, click OK.

Entering the desired values into the dialog box and clicking OK 5. The dialog box will tell you if Goal Seek was able to find a solution. Click OK.

Clicking OK

6. The result will appear in the specified cell. In our example, Goal Seek calculated the answer to be approximately 18.62. In this case, our final answer needs to be a whole number, so we'll need to round the answer up or down. Since rounding up would cause us to exceed our budget, we'll round down to 18 guests.

The completed Goal Seek and the calculated value

As you can see in the example above, some situations will require the answer to be a whole number. If Goal Seek gives you a decimal, you'll need to round up or down, depending on the situation.

 4.5.2 Other Types of What-If Analysis

For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. Rather than starting from the desired result and working backward, like Goal Seek, these options allow you to test multiple values and see how the results change.

•    Scenarios let you substitute values for multiple cells (up to 32) at the same time. You can create as many scenarios as you want and then compare them without changing the values manually. In the example below, we're using scenarios to compare different venues for an upcoming event. 

 

Using the Scenario Manager to compare different options

For more information about scenarios, check outthis articlefrom Microsoft.

•    Data tables allow you to take one or two variables in a formula and replace them with as many different values as you want, and then view the results in a table. This option is especially powerful because it shows multiple results at the same time, unlike scenarios or Goal Seek. In the example below, we can view 24 possible results for a car loan.

Data tables

For more information about data tables, check outthis articlefrom Microsoft.

Exercise 4.5

1.    Open an existing Excel workbook

2.    Use Goal Seek to determine an unknown value. If you're using the example, go to the History Class worksheet

and use Goal Seek to determine what grade you would need on Test 3 to earn a final grade average of 90



88