CS041A. Using Microsoft Excel

Using Microsoft Excel CS041A © 2015 Professional Career Development Institute, LLC. All rights reserved.  Accredited by the Accrediting Commissio...
Author: Kathlyn Shields
4 downloads 1 Views 6MB Size
Using Microsoft Excel


© 2015 Professional Career Development Institute, LLC. All rights reserved.

 Accredited by the Accrediting Commission of the Distance Education and Training Council. The Accrediting Commission of the Distance Education and Training Council is listed by the U.S. Department of Education as a nationally recognized accrediting agency.

CONTENTS INTRODUCTION 1 LAUNCHING THE PROGRAM Reviewing Program Options Saving the Workbook Closing the Workbook and Exiting Excel Opening a Workbook Working with Cells Working with Formulas and Functions

COMMON EXCEL FUNCTIONS Working with the Ribbon Using Templates

WORKING WITH WORKSHEETS Working with Views Adjusting Page Layouts Inserting a Chart Printing a File

1 3 6 8 8 9 15

19 20 23

24 25 26 26 27







INTRODUCTION Welcome! This short guide offers an introduction to Microsoft Excel. This software tool allows you to work with spreadsheets, or interactive worksheets in which you organize and analyze data. You can use Excel to manipulate data to make it more useful. For instance, you can use Excel to easily create and manage a household budget, track business inventory, produce invoices, calculate payments, and more. You can even use Excel to create a fitness program or plan your next vacation! When you’ve completed this guide, you’ll be able to

Open, close, and exit Excel

Create workbooks and worksheets

Identify and describe how to use a variety of Excel features

Explain how to work with templates

Format cells and add and edit basic formulas

Save, share, and print workbooks

Explain how to work with or share files online using OneDrive

Note: This introduction describes and shows Microsoft Excel 2013. Other versions of Excel may look and operate differently. If you have any questions about your version of Excel, use the Help feature. The Help feature offers easy access to system help and how-to articles on different features.

LAUNCHING THE PROGRAM To begin working with Microsoft Excel, you must first launch the program. Do this by clicking the Microsoft Excel program name in your computer’s Start menu or by double-clicking the Excel program icon on your desktop (Figure 1).



FIGURE 1—Launch Microsoft Excel Upon startup, the Excel start screen displays (Figure 2). This screen offers a variety of options, such as creating a blank workbook, working with a template, or opening an existing file. A workbook is the type of document you’re working in; it’s the format in which Excel files are saved. Each page inside a workbook is called a worksheet. A template is a preformatted workbook designed for a particular task, such as invoicing, weight loss tracking, financial planning, or budgeting.

FIGURE 2—Excel Start Screen



Reviewing Program Options To review the options available in Microsoft Excel, we’ll first create a blank workbook. You’ll get the most out of this guide if you follow along, so, launch Microsoft Excel on your computer and click Blank workbook. A new workbook opens to a blank worksheet (Figure 3). Let’s review the program options. To maximize your understanding, refer to Figure 3 as you read this section and follow along on your computer screen. The different features are numbered for ease of review.

FIGURE 3—Blank Excel Worksheet 1 The Quick Access Toolbar at the upper left-hand of the screen allows you to access common commands at all times. The default display shows the Save, Undo, and Repeat commands. However, you can customize this toolbar by removing these commands and/or adding other commands. You’ll learn how to do this after you feel more comfortable using Excel. 2 The filename appears in the middle of the header. The default here is “Book1”; however, this name updates to whatever filename you give the workbook when you save it. 3 To the far right are additional commands, namely,

Help. Click on the question mark to access the Excel Help menu.

Ribbon Display options. Different options are available for your screen display. We’ll talk more about these in a minute.



Minimize Window. This option allows you to quickly minimize Excel to your desktop without closing the program completely. Click the icon now and watch Excel disappear! But don’t worry; you can bring it right back.

Minimize/Maximize Window. This command allows you to change the size of the Excel window. You can make the window larger or smaller.

Close Window. The X command allows you to exit Excel quickly. This icon closes the program completely. Excel will prompt you to save your work before you exit.

4 The horizontal group of commands stretching across the screen beneath the Quick Access Toolbar is called the Ribbon.

T he Ribbon contains these tabs: File, Home, Insert, Page Layout, Formulas, Data, Review, and View. Each tab allows you to access a group of related tools and commands. As you work through this guide, you’ll have the chance to learn more about these tabs and commands.

T he default tab is Home tab—this is the tab that’s open when you first access a file. The Home tab includes commands such as the Clipboard, Font, and Alignment. These commands may be familiar to you if you already use other Microsoft products, such as Microsoft Word.

5 On the far right, you’ll see your Microsoft username; this option you to quickly access your Microsoft account. 6 Underneath the ribbon is the Name Box. The Name Box allows you to quickly determine which cell is currently active—in other words, the cell in which your cursor is currently positioned. In Figure 3, the Name Box says A1. That means the cursor is positioned in cell A1. On your screen, try clicking your cursor in other cells; watch the Name Box update as you do so. 7 To the right of the Name Box is the Formula Bar, where you can edit the data, formula, or function in the active cell. Note: To restore Excel, simply click the Excel icon on the taskbar along the bottom of your screen (Figure 4).

FIGURE 4—The Excel Icon in the Taskbar



The largest part of the screen is taken up by the worksheet (Figure 5). A worksheet is a sheet in a workbook. It contains a grid with cells.

FIGURE 5—Blank Excel Worksheet 1. In Figure 5, the worksheet is named Sheet 1, which is Excel’s default name for new worksheets. You can see this at the bottom of the screen. You can name your sheets by right-clicking on the sheet name and entering a new name. 2. You probably already figured out that each of the rectangular shapes in the grid is a cell. Cells are identified by their point of intersection between the vertical column (A, B, C, etc.) and horizontal row (1, 2, 3, etc.). For example, A2 is the cell that’s in column A, row 2. Similarly, D10 is the cell that’s in column D, row 10. And so on.  Cells are where you enter the content of your worksheet. To enter information into cells, use your mouse or keyboard arrow buttons to navigate to the cell. Click to activate the cell and then type your information. You can enter words or numbers in Excel. You can also use Excel’s features to enter more complex information such as a formula. We’ll discuss how to format the contents of cells a little later in this guide. Microsoft Excel also offers standard navigation features, just like other Microsoft programs. 3. Scroll bars are available on the far right and along the bottom of the sheet. You can click within each scroll bar or click the up or down arrows to move around on the screen. Try it now.



4. At the very bottom of the screen is a green bar, which is called the View Bar. The View Bar allows you to select your view of the worksheet. You can select Normal, Page Layout, or Page Break views. You can also change the zoom level by moving the Zoom slider. In Figure 2, the slider is set at 100%, which means the view is 100%. To change the zoom, move the slider to the right or left. Adjusting the zoom allows you to look more closely at individual cells or more widely at the whole worksheet. Try adjusting the zoom now. Microsoft Excel also offers something called the Backstage view (Figure 6). Click the File tab. The Backstage view allows you to access several other features in Excel.

FIGURE 6—The Backstage view offers access to additional features. Click each option to display tools related to that feature. Click each feature now to see a quick overview of these features. Then click the arrow at the top of the list to return to the worksheet.

Saving the Workbook Once you’ve created a file, you should save your work. Excel offers different options for saving files, depending on where you’d like to store the file (Figure 7).



FIGURE 7—Excel Save Options To save a file in a specific location, 1. Click the File tab. Then click Save As. 2. The first time you save a file, you need to name it. Enter a logical name for your file. 3. Navigate to the location and folder where you’d like to save the file, as follows: a. OneDrive refers to Microsoft’s online storage space (“the cloud”). If you use this storage option, you can access your file from any computer or device, as long as you have an Internet connection and sign into your OneDrive account. You can also use OneDrive to share files and collaborate with others. Learn more about OneDrive at https://onedrive.live.com. b. Computer refers to your computer’s hard drive or any attached storage devices. 4. Once you decide where to save your files, click Browse if you don’t see the specific folder location listed. 5. Navigate to the file, enter a filename, and click Save. You’re going to work on a short exercise in this guide, so save your currently open file as Sales. You can save this file in a directory related to your studies, on your hard drive.



Closing the Workbook and Exiting Excel When you’re finished with a workbook, you can close it by following these instructions:

Click File, then click Close.

If you’ve worked on your file since you last saved it, Excel will ask if you’d like to save changes.

Click Save to save your changes, Don’t Save to save without keeping your changes, or Cancel to cancel the exit from the program. If you click Save or Don’t Save, the file will close.

To exit Excel, click the Close option (the X) in the upper right-hand corner of the Excel window.

Opening a Workbook Once you have saved a workbook, Excel gives you several different options to open it.

In Backstage view, click Open.

 ou’ll be presented with a list of locations. Since you recently worked on Y this workbook, click Recent Workbooks.

Note: You can also find your workbook by clicking OneDrive if you saved it to your OneDrive account, or by clicking Computer if you saved it to your computer’s hard drive. Then you’ll navigate to the folder where the file is located (click Browse if the folder isn’t displayed). To work on a short exercise for this guide, you’ll need to open your Sales file.

Click the Sales filename to open the workbook.

Tip: If you want to quickly access a workbook that you use often, you can “pin” it to Backstage view. To do so, click Open from Backstage view. Then hover your mouse over the filename. When the Pushpin icon appears next to the workbook, click on it (Figure 8). To unpin a workbook, follow the same process: hover over the filename and click the Pushpin icon again.



FIGURE 8—Use the Pin option to easily access frequently used files.

Working with Cells For a quick introduction to work with cells in Excel, we’re going to add data to the Sales file. This will be a monthly summary of sales for a retail store. 1. In cell A1, type Sales. Then press the Enter key. You’ll now see the data in cell A1; your cursor will move to cell A2 (Figure 9).

FIGURE 9—Beginning of Sales Sheet



2. Use your mouse or the arrow keys on your keyboard to return to cell A1. 3. Click the Home tab in the Ribbon, if it’s not already selected. The second command group, Font, gives different options for changing the appearance of text (Figure 10). Since the text you just entered is meant to be the title of your worksheet, you’ll want to make it stand out from the rest of the text.

FIGURE 10—Font Options in the Home Tab 4. Click the drop-down arrow to the right of the Font option and choose Arial (Figure 11).

FIGURE 11—Changing the Font



5. The Font Size option is to the right. Click the drop-down arrow and select 14 (Figure 12) to increase the size of the text.

FIGURE 12—Changing the Font Size 6. Underneath the font name are emphasis options; we want to make this title boldface, so click the Bold option (Figure 13).

FIGURE 13—Select bold emphasis to update your text. 7. Now, let’s add data to our Sales by month. Navigate to cell A2. Type January and press the Enter key. 8. Repeat this process from cells A3 through A13; type in the months in sequential order, from February to December (Figure 14).



FIGURE 14—Sales Sheet after Adding Additional Data 9. You may see that the text for some months extends over the right border of the cell. You can increase the cell size to adjust for this. Hover over the horizontal line cells A and B in the column row, click and drag the border toward the right to increase the size of the cell (Figure 15).

FIGURE 15—You can adjust the size of cells by clicking and dragging the cell border.



10. Now we’ll undo the change we just made, so you have the chance to use a tool created just for this purpose. In the Quick Access Toolbar, click the Undo option (Figure 16). This will “undo” your last edit. (Optionally, you can use the keyboard shortcut by clicking Ctrl and Z together.)

FIGURE 16—Clicking the Undo option allows you to undo your last edit. 11. Excel includes a useful feature to adjust cell size based on the cell’s contents. Let’s try that feature. In the Home tab, look for the Cell group. Click Format, then click AutoFit Column Width (Figure 17).

FIGURE 17—You can adjust cell size with by clicking on AutoFit Column Width.



Enter more text for our sales sheet. In cells B2 through B13, enter the following sales figures. Check the sheet shown in Figure 18 to make sure you entered the data correctly. Cell Number B2 B3 B4 B5 B6 B7 B8 B9 B10 B11 B12 B13

Text to Enter 11,312 12,398 14,218 14,516 15,917 16,309 14,210 13,887 15,913 12,464 15,310 23,595

FIGURE 18—The Updated Sales Worksheet 13. Since these figures are meant to represent dollars, we’ll use the Numbers command to change the format. Using your mouse, click and select cells B2 through B14 (make sure you select the blank cell in B14 for the next steps). In the Numbers group on the Home tab, click the Accounting Number Format to add dollar signs to the numbers (Figure 19).

FIGURE 19—Select your cells, then click the Accounting Number Format icon to change the format to dollars.



14. Since we’re working only in dollars (and not cents), we’ll reduce the number of decimal places. In the Numbers group, click the Decrease Decimal icon two times (Figure 20).

FIGURE 20—Click the Decrease Decimal icon to reduce the number of decimal places shown in the cells. Now, compare your worksheet to Figure 21.

THE CLIPBOARD To cut or copy the contents of a cell (or multiple cells) from one cell to another, you can use the Clipboard. The Clipboard is a Microsoft Office feature that allows you to store up to 24 copied entries, such as blocks of text, which you can then insert in other locations. This process is often called “copy and paste.” Select the cells with the information you want to move. Click Cut to remove the contents from the cell(s) or Copy to copy them. Then move your mouse to another cell (or similar grouping of cells), and click Paste. The contents of the cells will appear in the new cell. The Clipboard also contains a useful formatting feature called Format Painter. If you want to copy formatting from one cell to the other, select the cell with the formatting you want to reuse, click Format Painter, and then click in the new cell(s) where you want to apply the formatting.

FIGURE 21—The Sales Worksheet

Working with Formulas and Functions Excel has many useful features that allow you to work with data. Two of these features are formulas and functions.

Formulas allow you to perform mathematical calculations on data. For example, you can add, subtract, multiply, or divide your numbers.



Functions are formulas that have been predefined to perform certain calculations. In other words, they’re shortcuts to commonly used formulas. This saves you time, since you don’t have to create them from scratch. Examples of functions include Sum, which allows you to add the contents of a group of cells, and Average, which calculates the average of a group of cells.

To understand the difference between formulas and functions, let’s work with the data in our worksheet using both a formula and a function. First we’ll work on a simple subtraction formula. 1. Position your cursor in an empty cell, such as D13. 2. Let’s say you want to calculate the difference in sales in the months of January and December. Since December’s value is larger, we’ll subtract the January value from the December value. Enter the formula exactly as shown: =B13-B2 Every formula begins with the equal sign (=). Formulas can also include standard mathematical operators, such as: addition (+), subtraction (–), multiplication (*), and division (/). You can also use the caret (^) for exponents.  Note: As you type the formula in the cell, note that it also appears in the Formula Bar (Figure 22). If it’s easier, you can enter formulas directly into the Formula Bar. Just make sure you’ve selected the correct cell number in the Name Box.

FIGURE 22—The Formula Bar 3. Press the Enter key. The value of the formula appears in the cell. In this case, it’s the additional amount of sales in December over January. 4. Now we’ll remove this formula from the cell using the Formula Bar. Click the formula in the Formula Bar. You’ll notice the cells that are used in this formula display. 5. Select the formula, then press the Delete key. Finally, click the Enter icon in the Formula bar (Figure 23). The value and the formula are cleared from the cell.



FIGURE 23—The Enter Icon in the Formula Bar

Types of Cell References Excel has two main types of cell references: relative references and absolute references.

Relative references (the default) update to reflect a new location when a formula is copied to another cell.

Absolute references remain fixed when formulas are copied to a new cell.

If a series of pound signs appear when you’re viewing a cell, they usually mean the column isn’t wide enough to display the value of the formula. Increase the column width to see the value.

To change from one to the other, select the cell and press the F4 key. For more on cell references, visit the Microsoft Excel official website: http://office.microsoft.com/en-us/excel-help/switch-between-relativeabsolute-and-mixed-references-HP010342940.aspx

Now, let’s try adding the contents of multiple cells using a function. 1. Navigate to cell B14. 2. In the Home tab, click the AutoSum drop-down arrow in the Editing group and then click Sum (Figure 24). AutoSum gives you access to commonly used functions; Sum is for addition.

FIGURE 24—The AutoSum icon gives you access to common functions.



Excel will highlight a range of nearby cells with values and insert a function into both cell B14 and the Formula Bar. You can modify this by resizing the selected cells or changing the cell references. 3. Since we have no changes, press the Enter key. A value calculating the sum of all the monthly sales appears in cell B14. 4. Move to cell A14 and type Total, since the value in cell B14 represents the total of all sales for the year. Then press the Enter key. 5. Format the word “Total” and the value using bold text. Do you remember how to do so? That’s right; select the cells and click the Bold icon in the Font group (Figure 25).

FIGURE 25—Formatting tools such as bold appear in the font group. You can also enter functions into the Formula Bar or cell using the following format: =FunctionName(arguments) “Arguments” may refer to a cell, multiple cells, or a range of cells. Select a range of cells using a colon and separate multiple arguments using commas. For example, in the following: =SUM(B2:B13) Excel will sum together the values in the cell range B2 to B13.



Tips •

AutoSum is also found in the Ribbon’s Formula tab.

F ormulas and functions follow the mathematical order of operations.

 ou can use the cursor to select cells to use in a formula. This is Y handy when you need to select multiple cells.

 formula refers to the cell reference—or the address of the cell— A and not to the actual values that have been entered into the cell. Thus, if you must update the sales figure for December, the formula will automatically recalculate with the new value.

 ou can edit a formula either in the Formula Bar or by doubleY clicking the cell to edit the formula in the cell. Click the checkmark in the Formula Bar or press the Enter key to complete the change.

 ou can copy formulas from one cell to another. One way is to copy Y the formula from the Formula Bar, navigate to the new cell, paste in the formula, and click the green checkmark. Another way is to use the Fill option. Select the cell or range of cells that include the formula. Then position your mouse over the square in the lower right of the cell until you see crosshairs. Drag the formula to the new cell(s).

COMMON EXCEL FUNCTIONS Here’s a description of several of the most common functions available in Excel:

SUM adds values in cells.

AVERAGE calculates the average for a group of selected cells.

COUNT counts the number of cells.

MAX determines the highest cell value in the selected cells.

MIN determines the lowest cell value in the selected cells.

You can use these and other functions from the Functions Library (Figure 26). To access this library, click the Formulas tab on the Ribbon. You’ll see several types of Functions listed. Note that some of these are complicated and directed toward more advanced Excel users. However, you should try out different functions to get a feel for them. Access the Excel Help if you need assistance. We recommend you start with the basic functions first, such as SUM, AVERAGE, and COUNT.



FIGURE 26—The Functions Library contains a variety of functions that can be applied to arguments.

Tip: You can also insert functions by clicking the Insert Function icon in the Formulas tab or the icon labeled fx in the Formula Bar.

Working with the Ribbon Let’s review each tab in Ribbon. Remember, each of these tabs displays groups of related commands. On your screen, you can hover your mouse over each icon to view Tool Tips that describe each tool and tell you how to use it. Tip: The Ribbon will automatically update with additional tabs when you’re using certain tools. For instance, if you’re inserting a table, an additional Table Tools Design tab appears in the Ribbon while you work on the table.


Home—This tab (Figure 27) includes the tools you’ll use the most often, such as
















FIGURE 27—Home Tab

Insert—This tab (Figure 28) is used to insert a variety of objects, such as




Illustrations (pictures, online pictures, shapes, SmartArt, screenshots, etc.)















FIGURE 28—Insert Tab

Page Layout—This tab (Figure 29) is used to set margins, page sizes, print areas, and more, through these command groups:




Page Setup


Scale to Fit


Sheet Options



FIGURE 29—Page Layout Tab



Formulas—This tab (Figure 30) is used to add formulas using these command groups:


Insert Function


Function Library


Defined Names


Formula Auditing



FIGURE 30—Formulas Tab

Data—This tab (Figure 31) is used to connect to other data sources and work with data using these command groups:


Get External Data




Sort & Filter


Data Tools



FIGURE 31—Data Tab


Review—This tab (Figure 32) is used to access tools such as proofing, adding comments, and tracking changes, through these command groups:










FIGURE 32—Review Tab

 iew—This tab (Figure 33) is used to access different view options through V these command groups:


Workbook Views









FIGURE 33—View Tab

Using Templates Templates are one of the most useful features in Excel. A template is a predefined worksheet. It may include predesigned formatting, styles, formulas, and text—all of which save you time and frustration when working with workbooks (Figure 34).

FIGURE 34—Excel comes with a number of templates, such as some seen here.



Let’s begin by looking at the available templates. 1. Click New to create a new Excel workbook. 2. Browse the templates available and choose the one that suits your needs (Figure 35). 3. Add values and make adjustments to the template. You can also change the formatting and styles using the tools in the Ribbon.

FIGURE 35—Templates aren’t just for spreadsheets and budgets. This template is designed to help the user track diet and exercise. Note: You can search for templates based on your needs. Enter terms in the Search bar at the top of the Templates screen. You can also go to the following website to find additional templates: http://www.office.com

WORKING WITH WORKSHEETS At the bottom left of your worksheet, you’ll see the name of the sheet as well as arrows and a plus (+) sign. Add more sheets to your workbook by clicking the + sign. If your workbook already has more than one sheet, you can navigate through the different sheets using the left and right arrows. You can find a variety of options for working with worksheets by right-clicking the sheet name (Figure 36).



FIGURE 36—Right-clicking a sheet name displays additional options for working with worksheets.

Working with Views Excel also allows you to view your workbook in different ways (Figure 37). Open a workbook to see how this works.

FIGURE 37—The View tab displays different options to view your workbook. 1. Click the View tab in the Ribbon. The first icon is Normal view, which is the default. 2. Click the Page Layout Preview icon to the right to change the view to show multiple pages.



3. Click the Page Break view to see an overview of your workbook. If you don’t have multiple pages in a worksheet, this won’t change much. However, if you have multiple pages, you’ll be able to see where the pages will break when the worksheet is printed. 4. Click on Normal view again. Note: The Custom Views option allows you to create new views based on parameters you create yourself, such as print settings.

Adjusting Page Layouts The Page Layout tab offers many features for adjusting workbooks (Figure 38). Adding a theme allows you to add a predesigned formatting scheme to your workbook. Click on the Themes icon to view a list of theme types. Experiment if you wish; click Undo to remove the changes. You can also manipulate formatting here by changing colors or fonts or adding effects.

FIGURE 38—The Page Layout Tab You can also adjust many page settings in the Page Setup group. You can play with the margins, page size, and page orientation (portrait or landscape). Click on the icon for the feature you’d like to change and adjust the settings, if necessary.

Inserting a Chart Viewing rows and columns of numbers can become overwhelming. One way to make information more understandable is to format your data as a chart. Excel makes it easy to add a chart to your workbooks. Let’s try it now. 1. Open the Sales workbook if it’s not already open. 2. Click the Insert tab. 3. Select the data you want to include in the chart. For this demonstration, we’ll select all the information in our Sales workbook, except for the Total row. 4. Click Recommended Charts (not available in all versions of Excel) or click a chart type. For this demonstration, click the Column Chart icon, followed by the first 2-D Column option.



Your sales information is displayed in a chart (Figure 39). Chart Tools also appears in the Ribbon, to give you additional tools for working with the chart. You can adjust the design or formatting of your chart. One change you want to make is to select the text that reads “Chart Title,” and give it a more appropriate title, such as “Sales.”

FIGURE 39—A Sample Column Chart in Excel

Printing a File Let’s review how to print a file. 1. Click the File tab and click Print. The left pane displays the available printers and the options. The right pane displays a preview of how your file will print (Figure 40). 2. Select the printer and options you want. 3. Adjust the contents of your file if you need to. When you’re ready, click the Print icon.



FIGURE 40—Print Options

GETTING HELP It’s easy to access Help at any time while using Excel. 1. Click the Help icon (the question mark) in the upper right-hand corner on the Excel screen. Alternately, press the F1 key (Figure 41). 2. Enter search terms or navigate through the suggestions displayed to find more information on the topic of your choice.

FIGURE 41—Excel’s Help Screen 28


ADDITIONAL RESOURCES Now that you’ve learned about the basics of Excel, get some practice! This is the best way to become more comfortable in using this tool. Try using different templates. Insert various functions and see how they work. Insert illustrations or charts. Experiment with formatting. Use commands like Undo to remove anything you don’t like. Use your own interests and needs to continue learning about this incredible tool. Here are a few online resources to learn more about Microsoft Excel: http://office.microsoft.com/en-us/support/getting-started-with-excel-2013FX102827997.aspx http://spreadsheets.about.com/od/tipsandfaqs/f/excel_use.htm http://www.excelfornoobs.com/ Go to http://www.teachertube.com; search for “Microsoft Excel.”