Identify the features of the Microsoft Excel interface
Please follow the instructions step-by-step; if you just do the project without following the instructed steps, that will be a failing grade. The professor will track back the steps to ensure good comprehension. All the materials are included; it is a matter of following the steps.
Requirements: materials are already included for this graded project
Microsoft Excel
Overview
Microsoft Excel is a spreadsheet application and one of the Microsoft Office products. Excel is used to organize, analyze, and interpret data.
In this lesson, you’ll learn about the features of Excel and how they’re used to produce spreadsheets that include formulas, functions, charts, and tables. You’ll also learn about spreadsheet planning and how to format and present data for the greatest impact. Whether you use Excel professionally, academically, or personally, the techniques you’ll learn in this lesson will help you use spreadsheets effectively.
Objectives
Identify the features of the Microsoft Excel interface
Describe how to create a spreadsheet
Explain how to apply formatting to make worksheet data easier to understand
Identify the steps needed to produce calculations using formulas and functions
Describe how to interpret data through charts
Analyze data organized in tables
Identify the Features of the Microsoft Excel Interface
Getting Started
Read this assignment.
Microsoft Excel is a spreadsheet application that’s used to store, analyze, and present data. It can be used for budgets, payroll, inventory, sales figures, and much more.
The instructions to start Excel will vary depending on your installation. However, you’ll most likely need to click the Excel icon in the Taskbar at the bottom of the screen or double-click the icon on the Desktop.
Excel is a popular program in many workplace settings. Because of its versatility, it can be used to organize data in a number of ways. Here are two professions where Excel might be used daily.
Veterinary Assistant
Record client appointments, admissions, information (such as breed, coat color, gender), treatments, medications, and payments.
Organize data to create charts and graphs from this information. For example, the veterinary assistant could see which type of heartworm medication is the most popular with their clients or whether the number of spayed or neutered cats changes over time.
Guest Services Agent
Record room or restaurant reservations made online or over the phone to see changing trends over time.
Create a graph showing which month the hotel had the most reservations, to be prepared for the next year. Data can also be organized showing when families booked rooms versus individuals or couples; when and how often clients used the concierge, restaurant, or room service options; or which amenities clients found satisfactory or unsatisfactory.
The Excel Window
After starting Excel, the first thing you’ll see is a screen with links to recently opened documents on the left. On the right are links to create a new blank workbook or one based on a template.
You’ll see this screen after starting Excel.
Click Blank workbook to display the Excel window with a new spreadsheet. Workbook refers to the entire Excel file, which contains one or more worksheets. The Excel window displays a single worksheet, also called a sheet, which is the Excel term for a spreadsheet.
An Excel Window with a Blank Workbook
Other features of the Excel window include the following:
The Quick Access Toolbar, which is located on the upper left of the Excel window, contains shortcuts to commonly used commands. The default shortcuts are Save, Undo, and Redo. You can click the last icon, Customize Quick Access Toolbar, to add or remove shortcuts.
The filename (the workbook name) is displayed in the top center of the window. When you save a new workbook, the name you provide will be displayed here.
Window controls in the upper-right corner are the Minimize button, which hides a window; the Restore button, which reduces or expands the window size; and the Close button, which closes the application window.
The Ribbon contains Excel commands. Commands are organized into tabs that run along the top of the Ribbon. Clicking a tab displays a different set of commands. Within a tab, commands are grouped. Some groups include a dialog box launcher—a small arrow icon—in the lower-right corner that you can click to display a dialog box or task pane with additional options related to the group. Some commands on the Ribbon have an arrow you click to display a menu of additional options.
The File tab is different from other Ribbon tabs. You click File to display the Backstage view with options for opening, saving, printing, sharing, and closing a file. To get back to the spreadsheet window without executing a command on the File screen, click the Back Arrow in the upper left of the screen or press the Esc key.
The Tell Me box is a search tool and help feature. When you can’t locate a command, or when you want to learn how to perform an action, click Tell Me or press Alt+Q and type any word or phrase in the box to display a menu of related search results. The results will include related commands and an option to display a help dialog box with more information.
Columns run vertically and are identified with letters.
Rows run horizontally and are identified with numbers.
A cell is the intersection of a row and column and has a cell name, which is made up of the cell’s location in a column and row, such as A1. The name of the selected cell is displayed above the sheet in the Name Box. The cell name is also called the cell reference.
The active cell is also called the selected cell. It has a bold border and is where the data you type will appear.
The formula bar is displayed above the column letters and displays the contents of a cell.
Scroll bars are located along the right side of the window and below the worksheet. You use them to bring unseen cells into view.
The Sheet tab is displayed at the bottom of a worksheet. Clicking the New Sheet button adds an additional sheet to the workbook.
The status bar runs along the bottom of the window. On the right side of the status bar is a zoom slider you can use to change the magnification of your worksheet. Icons for changing the worksheet view are also near the zoom slider.
ScreenTips are small boxes that pop up when pointing to a command on the Ribbon or other features on the Excel document window. ScreenTips display information about a feature, including keyboard shortcuts for executing the feature without having to remove your hands from the keyboard.
Working with Input Devices
The input devices you’ll most likely use with Excel are the keyboard and the mouse or touchpad. You use the mouse or touchpad to point to commands and cells, to click commands and cells, and to drag cells.
The keyboard is used to enter data into your worksheet. The data you type appears in the active cell. Along with keys for typing data, the keyboard has keys for selecting a cell and communicating with Excel:
The Tab key moves to the next cell in a row.
The Enter key moves to the next cell in a column.
The arrow keys select the next cell in the direction of the arrow. Ctrl+arrow selects the last cell with data.
The Home key selects the cell at the beginning of a row. Ctrl+Home and Ctrl+End move the insertion point to the first or last cell of data.
The Delete key is used to remove the active cell contents.
The Page Up and Page Down keys are used to scroll a sheet within the window.
The Esc key’s function varies depending on the action, but it’s commonly used to cancel the current operation.
Freeze Worksheet Rows and Columns
Freezing panes allows you to keep one area of a worksheet static while letting you scroll to a different area within that worksheet. To freeze panes, go to the View tab and lock your selected rows and columns so the data is stationary on the sheet. The split panes view will create separate windows within the same worksheet.
Freeze the first column by selecting Freeze First Column from the Freeze Panes group in the View tab. A line will appear after the first column, between A and B, to show that the first column is frozen.
To freeze the first two columns, select the third column and click Freeze Panes from the Freeze Panes group in the View tab.
To freeze columns and rows, select the cell below the rows and to the right of the column or columns you want to keep visible when you scroll. Then select Freeze Panes from the Freeze Panes group in the View tab.
Change Window Views
When working in Excel, especially with multiple sheets, it may become necessary to view data from multiple sheets together at one time to compare and contrast data more easily. To change the windows view of workbooks and spreadsheets from the View and Window group, use the options Arrange All, Split, and Side by Side.
Arrange All allows for all open windows to be arranged in an easy-to-see format, cascading the sheets so they’re individually accessible.
Split view splits the section of the current worksheet into four sections, allowing you to scroll to different areas in the spreadsheet while freezing other areas.
The Side by Side view arranges the window so two worksheets are arranged side by side for easy comparison and viewing.
The toolbar contains various functions.
Modify Workbook Properties
In Excel, Workbook properties can identify key pieces of information like who created a file, when it was created, when it was last modified, and what the current status is. Workbook properties include a function that allows you to add tags to a workbook file. Tags are short descriptions, or keywords, that help identify the kind of content contained within a file.
Tell Me How
The Microsoft Excel application has many features. You can use the Tell Me box to help you understand these features. Explore the features of Excel by typing questions or phrases related to document creation into the Tell Me box. Click the Get Help option at the bottom of the menu to learn more.
Key Points and Links
Key Points
Excel has many features to help you organize data.
When you start Excel, you’ll see the Start screen with links to spreadsheets and a link to create a blank workbook.
To help you perform an action quickly, the Quick Access Toolbar is located in the upper left of the Excel window.
You can use the Tell Me box to learn more about the commands and features of Excel.
A workbook consists of columns that run vertically and rows that run horizontally.
Columns are labeled with letters, and rows are labeled with numbers. You can enter data into the active cell, also called the selected cell, which has a bold border.
ScreenTips display information about a command or feature.
The Tab and Enter keys are used to move from one cell to another. When you type data, it appears in the selected cell.
Describe How to Create a Spreadsheet
Entering and Editing Data
Read this assignment.
Microsoft Excel makes entering and editing data easy and efficient. It has features such as AutoCorrect, AutoComplete, and the Spelling command to help keep data accurate. Other features such as Cut, Copy, Paste, and the ability to move cells help organize data without having to retype it.
Planning Your Worksheet
A Work Schedule
A worksheet is rows and columns of data; therefore, it’s important to develop a plan based on this structure. To avoid needing to make changes later, you should decide beforehand which data belongs in rows and which will be better in columns. In most cases, columns are for groups and rows are for singular items. For example, a work schedule will probably be best organized with employees in rows and those working on a given day grouped into columns.
Watch this video to learn about rows and columns.
Entering Data into a Worksheet
When you’ve gathered the information you want to organize, it’s time to enter data into your spreadsheet. In addition to the actual data, you should enter labels to identify the purpose of the worksheet, columns, and rows. Labels are text used for descriptions and aren’t used in calculations. Data can also be text, but more often data are in the form of values or dates and times. A value is numeric and can be used in calculations. Date and time can also be used in calculations and is a code that represents a date, a time, or both.
It’s important to use labels to define rows, columns, and cells in your worksheets. The information you enter might make sense to you when you enter it, but you might forget which values were which over time or as more information is entered. Additionally, you’ll rarely be the only person to use the worksheet, so labeling rows and columns will help others make sense of your data.
To enter information into a cell, select a cell to make it the active cell, and then type your label or data. When typing, the contents appear in the Formula bar and the Cancel and Enter buttons are active. You can find the Cancel and Enter buttons above the cells right underneath the ribbon. The Formula bar can be found in the same location.
The Cancel and Enter buttons and the Formula bar appear above the cells.
You can select Enter to change the cell contents to the typed data or choose Cancel to leave the cell as it was.
The most efficient way to enter data is to keep your hands on the keyboard. As you type, press Tab to move to the next cell in the row or press Enter to move to the next cell in the column. If you’ve entered data into a row of cells, pressing Enter selects the first cell in the next row.
Differentiate Between Cell Name and Cell Contents
A cell name refers to the column and row of the cell’s location. For example, cell A9 is the cell at the intersection of column A and row 9. This is also called a cell reference. The cell contents are what a cell contains, such as data or a label. You can compare a cell to a mailbox with an address and a storage area.
Change Cell Width and Height
Truncated cells appear as pound signs.
Sometimes the information you include won’t fit within the cell width because it’s longer than the given space. If the next cell doesn’t contain data, your information will extend into that empty cell. However, if the next cell contains data, then long data are truncated, or shortened. When you type a numeric value that’s too large for a cell, the cell displays #####, as in the example figure.
Change the column width by dragging the column boundary.
You can display data entirely by changing the column width for the cell. You do this by dragging a column boundary at the top of the sheet. A boundary is simply the line between columns or rows. When pointing to a column boundary, your pointer changes to a double-headed arrow.
A long title in column A causes the data to display across several cells.
You can size a column to best fit the data it contains by double-clicking a column boundary. This formatted sheet has best-fit column widths for columns B, C, and D. Note that if you try to best-fit column A, the long title will cause a very wide column A.
Similarly, you can change a row height by double-clicking a boundary or by dragging.
Use AutoComplete and AutoCorrect
When you type data, Excel makes a best guess at what you’re trying to enter. If the cells in the same column have entries similar to what you’re typing, Excel tries to complete the entry for you. For example, if the words “any town” already exist in the Excel sheet and you start typing the words “any town” again in a different cell, Excel will autocomplete these words in that cell.
An Example of AutoComplete
You can choose Tab or Enter to accept the suggested text or continue typing for a different entry.
When you type labels and other text, Excel uses the AutoCorrect feature to automatically change obvious misspellings such as “thier” for “their.” Always double-check data entry for unwanted AutoComplete and AutoCorrect changes.
Excel doesn’t automatically flag misspelled words as you type. For professional results, you should always check the spelling in a worksheet before sending it off or printing it. You can check the whole sheet at once with the Spelling command on the Review tab, which displays a dialog box allowing you to correct unrecognized words.
Editing Cell Contents
Corrections and updates are common with spreadsheets. To change the data in a cell, simply select the cell and type a new entry to replace the current one. If you want to edit an entry, select the cell and press F2 to place the insertion point in the cell. You can also select a cell and then click in the Formula bar to display the insertion point there. When the insertion point is displayed in your data, you can use the Backspace key and arrow keys to correct just a portion of the entry.
To remove data from a cell, click the cell to make it active and then press Delete. To remove a recent entry, click Undo on the Quick Access Toolbar or press Ctrl+Z to reverse the last action.
Insert and Delete Rows and Columns
When you need to expand a worksheet, right-click the column letter that’s to the right of where the new column should appear, and then click Insert. You can also click a column header to select the entire column and then click Insert on the Home tab. You can also select a column and press both Ctrl and the plus sign key (+) on the number pad or Ctrl, Shift, and the equal sign key (=) on the number pad.
Rows are inserted similarly. Right-clicking on a row number and selecting Insert will create a row above the selected row. You can also use the Insert command on the Home tab to add a row. You can also select a row and press both Ctrl and the plus sign key (+) on the number pad or Ctrl, Shift, and the equal sign key (=) on the number pad.
Delete a column or row by right-clicking the column letter or row number and clicking Delete or by pressing both Ctrl and the minus sign key (-) on the number pad. Be careful that this is what you want to do—the entire column or row and all of its data will be removed. To reverse this action, either press Ctrl+Z or click Undo on the Quick Access Toolbar.
Move and Copy Cells
You can make edits to a spreadsheet by copying and moving cells.
Select the cell to be moved or duplicated.
On the Home tab, in the Clipboard group, click Cut (Ctrl+X) to move data or Copy (Ctrl+C) to duplicate it. A moving, dashed border will appear.
Select the cell to receive the data.
On the Home tab, in the Clipboard group, click Paste (Ctrl+V). If data was cut, then the previously selected cell will be blank. Copying a cell keeps the data in the original cell.
When you move or duplicate data with Cut and Copy, it’s placed in an area of memory called the Clipboard. You can view Clipboard items from the Home tab in the Clipboard group where you can click the dialog box launcher to access the Clipboard task pane. Select any item in the task pane to place it into the selected cell. You can delete items from the Clipboard by pointing to the item, clicking the drop-down arrow, and then clicking Delete.
Excel warns you if you’re about to move a cell onto one that already has data in it.
Cells can also be moved by clicking and dragging them to a new location. First, point to the cell border until the cursor changes to a four-headed arrow, then drag the cell to a new location. If you try to place the cell at a location that already has data, a warning pops up. Click OK if you want to replace the contents or Cancel to leave the cell in the previous location.
Use Special Paste Options
Paste Special has features to allow special paste options for mathematical operations, values, formats, validations, and many other options. For example, text copied from the internet or a PDF file often retains much or all its original formatting, which may not match the colors, fonts, or other text formatting being used in a document or spreadsheet. When using Paste Special, you can paste the text into a document without its original formatting. Unformatted text may have a different font theme, bolding, or hyperlinks.
Access the Paste Special dialog box by selecting the dropdown menu arrow next to Paste in the Clipboard group in the Home tab and choosing Paste Special.
Use Autofill
The Autofill feature attempts to see an existing pattern in the data entered and then apply that pattern to filling in additional cells. The following steps show how to complete an Autofill.
Select the sequence of cells that the pattern will be based on. When selected, the cell or range will display within a green border.
At the bottom-right corner of the cell or range, a Fill Handle will display. Drag the Fill Handle until the border surrounds the desired range of cells and then release it.
The Autofill feature captures duplicate text entries in the same column. If text is typed into a cell that matches the beginning of another cell’s content, Excel will automatically complete the entry to match the existing content.
You can insert cells in an Excel sheet by using the Insert options.You can delete cells in an Excel sheet by using the Delete options.
Insert and Delete Cells
In your data entry process, the amount of text you need to display in a cell may be more than the cell can hold. For example, your finance team might create a new metric to which certain commission dollar amounts are tracked.
To include information on the new metric, you may have to add a row or a column right in the middle of the existing data. Or maybe you want to view your data in a different way to focus more on one element of a system. When situations like this arise, you’ll need to be able to modify the cells, columns, and rows in your worksheet to best fit your needs. The Insert Cells/Delete Cells commands and features allow you to make these kinds of changes.
The Insert and Delete options are for inserting and deleting cells. Insert or delete a cell or group of cells by using either the Insert Cells or the Delete Cells command from the drop-down menus or with the Insert or Delete dialog boxes.
Page Layout Features
You have many options on the Page Layout tab for controlling how your worksheet is printed:
Select Margins to change the space around the worksheet when printed. By making margins smaller, you can get more rows and columns on a page.
You can change the Orientation to Landscape to print more columns on a page (but fewer rows).
Select Breaks and then Insert Page Break to insert a break above the selected cell. When you control where one page ends and the next begins, you can provide more useful printouts.
In the Page Setup group, click the dialog box launcher and then click the Header/Footer tab for options to add information to be printed at the top or bottom of each printed page. Headers and footers are especially helpful with worksheets that print on multiple pages.
Scale to Fit options are used to format your worksheet to fit on a designated number of pages.
Select the Gridlines Print option to make data more readable on a printout. Without this option selected, a printout doesn’t include the cell borders. The Headings Print option works similarly to add column letters and row numbers to a printout.
Save as an Alternative File Format
The default file for Excel workbook files is the .xlsx format. There are other file formats for Excel to share with other users, to upload to websites, or for other uses. These alternative file formats will save the current Excel file per the Save As feature command.
Watch this video to learn how to save workbooks in other formats.
Inspect Workbooks
The Excel Compatibility Checker is an option to check the compatibility of objects and data in a workbook when the file may have been saved in an earlier file format. Selecting the Check Compatibility command opens the Microsoft Excel – Compatibility Checker dialog box. To access this, select File > Info > Check for Issues. Excel will automatically run this check when a current file is saved in a previous file format.
Import Data into Workbooks
Importing is the process of bringing information or data into an application or database from an outside source. Excel has functionality for importing several types of data sources such as Access databases, text files, or files from the web.
Import Data from .txt and .cvs Files
Importing text files is an option that allows you to import data from simple text files that are delimited in a variety of ways.
Paste Text file (.txt) Data—Text files contain plain text and are commonly created with NotePad. If you paste content into a text file, all the styles and formatting are stripped out, leaving you with plain text. This makes it easy to copy and paste data without formatting issues. If you paste spreadsheet data into a .txt file, it’s separated by tabbed spaces.
Paste Comma Separated Value (CSV) Data—A CSV file also contains plain text that can be opened in Excel. If you paste spreadsheet data into a CSV file, it’s separated by commas.
Import Delimited text files (.txt)—A delimited text file is a plain text file, typically created with NotePad, with data separated by tabbed spaces. It can be imported into Excel.
Import Comma separated values text files (.csv)—A CSV file, with the data separated by commas, can be imported into Excel.
There are two ways that you can import data from text files into Excel. You can either open the text file within Excel or you can import the data from the text file as a data range. If the file is opened directly in Excel, you don’t need to create a link to the text file. If you import the data, you’ll need to link the workbook to the text file for changes to the text file to reflect in the Excel workbook.
Import Data Using the Text Import Wizard
There’s an easy Text Import Wizard process that will walk you through the process of identifying the type of data a file contains and where the import should start, the type of characters that separate entries in delimited files, and the selected cell formatting options for the columns that will contain the data after importing. You can access the Text Import Wizard in two ways:
Open a text file using File > Open.
Select Data > Get External Data > From Text, and then select the desired text document from the Import Text File dialog box.
Search for Data within a Workbook
There are various options to find and search for data, values, formulas, or formatting in an Excel worksheet or workbook. One option is to use the Find command to locate specific content within worksheets and workbooks. When the Find command is selected, the Find and Replace dialog box opens with the Find tab automatically selected. To access the Find command, select Home > Editing > Find & Select > Find, or press Ctrl+F.
Reflect and Respond
Spreadsheets often have large amounts of data. What mistakes might you make
Key Points and Links
Key Points
Develop a plan for your data based on the rows-and-columns structure of a spreadsheet.
A worksheet typically contains labels as well as data.
Labels describe cell data, while data can be text, numbers, dates, or times.
The typed data appear in the active cell, also called the selected cell. When you type data, press Tab to move to the next cell in a row or Enter to move to the next cell in a column.
A cell name refers to the column and row of the cell’s location.
If a cell doesn’t display its contents entirely, you can drag the cell boundary to increase the width of the entire column.
To change the data in a cell, click the cell and then type a new entry. If you want to edit existing data in a cell, click the cell and press F2 or click on the Formula bar to place the insertion point.
Links
Guided Practice: Organizing a Worksheet (Semester Grades)
Complete this guided practice activity to learn the steps for organizing a worksheet.
You’ve decided to keep track of your semester grades with a spreadsheet. Follow the directions below to organize your grades in a worksheet.
Create a new Excel workbook.
Select cell A1 in the upper-left corner of the worksheet if it’s not already selected. (Note the cell name in the Name box.)
Type “Semester Grades” in cell A1 and select Enter. (Notice how the cell A1 entry extends into the next cell.)
Type “Class” in cell A3 and select Tab.
Type “Instructor” in cell B3 and select Tab.
Type “Grade 1” in cell C3 and select Enter. (Notice that the next cell selected is the first cell in row 4.)
Type “English 1” in cell A4 and select Tab.
Type “Cohen” in cell B4 and select Enter.
Type “Sociology” in cell A5 and select Tab.
Type “Foster-Smith” in cell B5 and press Enter.
Type “Biology” in cell A6 and select Tab.
Type “Lopez” in cell B6 and select Enter.
Type “College Math,” in cell A7 and select Tab.
Type “Kim” in cell B7 and select Enter.
Point to the boundary between columns A and B until the pointer changes to the double-headed arrow shape.
Drag the column A boundary with the double-headed arrow pointer until the cell boundary is approximately halfway through the “d” in Grades in cell A1.
Release the mouse. (Note that column A should now be wide enough to entirely display “College Math” in cell A7. )
Point to the boundary between columns B and C until the pointer changes to the double-headed arrow shape.
Double-click the column B boundary with the double-headed arrow pointer. (Note that column B widens to a best fit for the data it contains.)
Right-click cell A7 and select Insert to add a blank row between Biology and College Math.
Type “Biology Lab” in cell A7 and select Tab.
Type “Douglas” in cell B7.
Select cell B8.
Type “Soto” in cell B8 and select Enter to replace the original data.
Select cell B4 and use the Formula bar to display the insertion point.
Use the arrow keys to move the insertion point to the end of Cohen, if necessary.
Type an “s” at the end of Cohen and select Enter. “Cohens” is now displayed in cell B4.
Select the entire row 6.
Insert a blank row on the Home tab.
Select cell A9 (“Biology”).
Select Cut (Ctrl+X) on the Home tab. (A moving, dashed border appears.)
Select cell A6.
Select Paste (Ctrl+V) on the Home tab to move the data. (College Math is now right above Biology.)
Select cell B9 (“Soto”).
Point to cell B9’s border until the cursor changes to a four-headed arrow.
Drag the cell to cell B6 (next to College Math).
Save your workbook, naming it “Semester Grades.”
Your worksheet should look similar to the table below.
Explain How to Apply Formatting to Make Worksheet Data Easier to Understand
Formatting Data
Read this assignment.
Spreadsheets must convey organized thinking in an understandable way to be useful. Making sure the numbers are correct is a priority, but how those numbers are presented determines if a reader can make sense of your data.
Formatting Cell Contents
Your worksheet isn’t the place to experiment with numerous fonts and styles, but you should make titles larger, and bold or colored labels might be more eye-catching. Visually, your spreadsheet will be easier to read if data is aligned properly. For example, numbers should be right-aligned, headings should align with their data or at least be centered above the data, and labels that are considerably longer than their data should be rotated or wrapped within a cell.
Selecting Cells
The first step to change the look of a cell is to select the cell to be formatted. You can select several cells at once to apply formats faster.
When cells are selected together, they form a range. To select cells, drag from one to another.
When you select more than one cell at a time, Excel highlights the range of cells in your selection.
For ranges that extend beyond the window, click the first cell in the range, scroll until the last cell is visible, and hold the Shift key while clicking the last cell in the range. If you want to use the keyboard to select a range, select the first cell of the range, hold the Shift key, and press the arrow keys until the last cell in the range is reached.
To select all the cells in a row or column, select the row number or column letter.
Fonts and Alignment
Changing the font in a worksheet should be based on readability or company standards. Aside from the worksheet title, which may need to be in a font that reflects the company logo, other labels will be easier to read in a sans serif font, such as Calibri or Arial.
Bold and italic words are useful for drawing the reader’s eye to the information. Some styles, such as underline, are usually not a good choice because the style competes with the cell border and is also associated with hyperlinks. Color can also be used for cell data but should be used with thought to how it will print.
The Font and Alignment groups on the Home tab allow customization of how your cells look.
Cell alignment can be left, center, or right. Since numbers are right-aligned in a cell by default, your column labels for the numbers will be best right-aligned or centered.
To apply font or alignment formats, first select the cells to format, and then use commands in the Font group and the Alignment group on the Home tab.
Hyperlinks
A hyperlink is a link within a document that, when clicked, navigates to a certain point in a document, opens another document, navigates to a webpage, or opens to start an email.
Hyperlinks can be added or modified within a worksheet using the Insert Hyperlink dialog box or the Edit Hyperlink dialog box. You can also press Ctrl + K to access the Insert Hyperlink dialog box. Access the Insert Hyperlink and Edit Hyperlink dialog boxes by selecting Insert > Links > Hyperlink.
A hyperlink can be removed by using the Remove Link button. Another option for removing hyperlinks is the Clear command, which is in the Editing group on the Home tab.
Clear Hyperlinks clears hyperlinks from cell contents. Cell formatting remains, including formatting applied to text when the hyperlink was created.
Remove Hyperlinks removes hyperlinks and all formatting.
Format Cells Using Format Painter
Use Format Painter when you want to copy formatting from one item to another. The Format Painter copies and pastes formatting (font type, color, and size) from one cell or range to another cell or range.
If you have a large number of cells in a worksheet to which you would like to apply formatting changes, the Format Painter feature allows for a broad paintbrush effect on a large number of cells at one time. By using the Format Painter, you can reapply existing formatting to other cells on the same worksheet, to other cells in the same workbook, and in other open workbook files.
Access the Format Painter from the Clipboard group on the Home tab.
Clear Cell Formatting
In addition to using the Delete or Backspace key, to delete cell contents, you can use the Clear feature. This removes formulas and data, formats such as number formats, conditional formats, and borders, and any attached comments. The remaining cells are blank or unformatted once the Clear feature has been applied.
To clear the cells of content or formatting, choose the desired cell, row, or column of data that you want to remove. On the Home tab, under the Editing group, an arrow next to the Clear button reveals a list of options:
Clear All removes the contents in the selected cells, rows, or columns, along with any formatting and comments.
Clear Formats removes the formatting that’s associated with the selected cells, rows, or columns.
Clear Content removes the contents within the selected cell, row, or column.
Clear Comments and Notes removes the comments or notes associated with the selected cell, row, or column.
Clear Hyperlinks removes any formatted hyperlinks that are associated with the selected cell, row, or column.
Format and Modify Text
As you work with Excel, you’ll either enter data yourself or be provided data or text from a database or other systems and applications. With the Text function in Excel, you can manipulate text in cells to extract sections of text to other cells or combine them with various formatting options to create a list or report.
The more common text formatting functions are RIGHT, LEFT, MID, UPPER, LOWER, CONCAT, and TEXTJOIN.
The RIGHT function returns the first character(s) in a text string based on the number of characters specified. For example, for the name Huckleberry Finn in cell A2, you can use the RIGHT function in cell B2 to extract five characters of that text from the right, resulting in a cell with the last name Finn. For the name Luke Skywalker, use the RIGHT function for cell A3 to extract ten characters from the right for the next result in the Last Name column, cell B2, of Skywalker.
The LEFT function returns the first characters in a text string based on the number characters selected. For the full name Huckleberry Finn in cell A1, use the LEFT function to extract the first ten characters of the text string, resulting in the first name Huckleberry.
The MID function returns the characters from the middle of a text string, starting at an identified position, based on the number of characters chosen. For example, an inventory list can be used to extract information from one column. In this instance, the Description column is used to extract the fourth character and return the next four characters. The description column C3 for XCA9883619CBA with the MID function applied results in 9883.
The UPPER function converts a text string to uppercase or capitalizes all the text in the string. The LOWER function coverts a text string to lowercase or capitalizes all the text in a string. Note that numbers don’t change when the LOWER function is used.
Format Text Using the CONCAT() and TEXTJOIN() Functions
One of the most powerful text functions in Excel is the concatenate, or CONCAT function. This function allows you to join together text strings from multiple cells on a worksheet into a single cell. This function can save time when you need to pull data from multiple cells that already exist within your worksheets.
For example, imagine that you’re the coordinator for a large event with hundreds of attendees, and the attendee list you received from the vendor is an Excel worksheet with attendee first and last names in separate columns, but you would prefer the names to be in a single column. The CONCAT function is a great option for this task.
Change a cell border using the Border command on the Home tab.
The TEXTJOIN function combines the text from multiple ranges or strings. It includes a specified delimiter between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.
Applying Borders
Spreadsheets are about presenting data in cells, and Excel has many cell-border options. Applying solid borders to all sides of cells containing data makes printouts easier to read. Other border formats, such as Bottom Double Border, are useful for specific purposes like totals. To change a cell border, select the cells to format and then select the arrow in the Border command on the Home tab to display choices.
Wrapping and Rotating Text
When you have long labels, you can end up with columns that are much too wide and affect the readability of your worksheet. Before you consider formatting options, carefully think about the label. Can it be just as descriptive with fewer words? Shorter labels are easier to read. Should the data be divided into more columns? For example, a First and Last Name column may not be a good idea. A cell should contain a single data item.
You can rotate words that are too long for your cells.
But if a long label is needed, you can either wrap the text in a cell or rotate it with Wrap Text and Orientation commands in the Alignment group on the Home tab. The cells below display both formats.
Merging Cells
Your worksheet is more descriptive with a title centered at the top. Centering data across many cells is accomplished by using merged cells, which are cells that have been combined into one larger cell. To combine two or more cells, you first select the cells to merge and then select Merge & Center on the Home tab. To reverse the merge, select the merged cell, select the arrow in the Merge & Center command, and then select Unmerge Cells.
Themes and Cell Styles
You can change the overall look of your worksheet in one step with options in the Themes group on the Page Layout tab. A theme is a named set of colors, fonts, and effects. You may even be asked to use a specific theme for your company.
A theme has several cell styles associated with it. A cell style is used to apply several formats at once to a selected cell or cell range. Cell styles are applied with options in the Styles group on the Home tab. You can use the More button at the end of the group to expand the list to see the Themed Cell Styles.
Themed Cell Styles
If you use a themed cell style, it will be applied to the selected cell or cell range. Later, if you change the theme, color, font, or effect from the Page Layout tab, any cells with applied styles will be updated to match automatically.
Adding Graphics
A company logo or other related graphic can be used on your worksheet to give it a better overall presentation. You can do this with commands on the Insert tab. It doesn’t matter which cell is selected when you insert a graphic because images are by default freely moving objects.
A handle allows you to rotate the image.
To insert an image from your computer, select Pictures on the Insert tab, and then navigate to the image you want. For a general-purpose image, you can select Online Pictures on the Insert tab to display a dialog box where you can search for a clip art image online. Clip art from the internet is protected by copyright through a Creative Commons license. A link to this license is available in the Insert Pictures dialog box.
After you insert an image, you can select it to change its size or rotate it by dragging a handle.
To move the image, drag it to a new location. When a picture is selected, the Picture Tools Format tab is added to the Ribbon. You can use this tab to change picture styles, borders, and effects.
Formatting Numeric Data
Apply a format to an empty cell using the Number Format list on the Home tab.
Your worksheets will be easier to understand if you format numeric data appropriately. For example, a number for the items in stock can’t have a decimal portion, and dollar amounts should display a dollar sign. Taking care to make sure data are displayed correctly gives your worksheet credibility.
You can apply numeric formats after data are entered, but formatting empty cells before you type the data can help improve accuracy. To apply a format, select a cell or range of cells and use commands in the Number Format list on the Home tab.
The Number group also contains Increase Decimal and Decrease Decimal options for easily changing the decimal places for a value. You can see all available number formats by launching the Number Format dialog box.
Cell Values vs. Cell Formats
Formats applied to a cell determine how a value is displayed, but they don’t change the stored value. For example, if you enter 123.456 into a cell and then format it to display two decimal places, you’ll see 123.46.
If you want to know the value stored in a cell, select the cell and look at the Formula bar. This is important because when using formulas, which you’ll learn about later in this lesson, the calculations will use the nonrounded numbers, and might show different results than if the rounded numbers were used.
Conditional Formatting
Highlight Cells Rules provides simple formatting options for cells.
Effective use of color can make it possible to evaluate data quickly. When a cell displays a color format based on a value, it has a conditional format.
To apply conditional formatting, select the cell or cell range to format and then select Conditional Formatting on the Home tab to display a menu. Conditional formatting is based on rules that range from simple to complex. The Highlight Cells Rules provide some simple but effective formatting.
When you click one of the Highlight Cells Rules, a dialog box is displayed.
Clicking on Less Than displays this dialog box.
You type a value, select a color format from the list, and then select OK. When the cell contains a value less than the one you specified, the cell color appears.
Using themed cells and conditional formatting can be a great way to represent data visually. Two of the default Themed Cell Styles are “Bad,” which had red text on a light red fill, and “Good,” which has green text on a light green fill. These styles are commonly used in business when displaying money values such as those from sales, purchases, expenditures, and other transfers. Good, as the name suggests, represents a positive total—money earned for the company—while Bad represents a negative total—money lost. Before even reading the data on a spreadsheet, someone could tell how the company is performing simply by seeing the number of Good and Bad cells.
Using Quick Analysis
You can also quickly add conditional formatting with the Quick Analysis button, which appears when you select a range of data. Use the Quick Analysis button in the lower-right corner of the range to display options.
You can point to an option to preview the results. Click any option to apply it to the selected range. When the Quick Analysis formats aren’t what you’re looking for, use the Conditional Formatting command on the Home tab.
Watch this video to learn how to use the conditional formatting command.
Define a Name Range
By using a defined Name Range, formulas will be much easier to understand and maintain. This command can define a name for a cell range, function, constant, or table. Using Names in the Excel workbook allows for easy updates, auditing, and management of the names.
This is how you define names from a selected range.
Choose the range and include any row or column names.
On the Formulas tab, select Create from Selection from the Defined Names group.
In the Create Names from Selection dialog box, identify the labels by selecting the appropriate check box where the data is located. The options include Top row, Left column, Bottom row, and Right column. You can make multiple selections.
Select OK.
Name a Table
When a new table is created, a default name will be provided, such as Table1, Table2, Table3, and so on. However, these names can be changed or updated.
To rename a table, do the following:
Select the table.
Go to Table Tools > Design > Properties > Table Name.
Highlight the table name and enter a new name
Reflect and Respond
What kind of problems could you run into if you format a cell with the wrong format for the data type?
Key Points and Links
Key Points
You can use commands on the Home tab to change the font, style, alignment, wrap, and orientation of your cell data.
More than one cell can be selected at a time by dragging and highlighting a range of cells.
Cell borders can be used to distinguish one cell from another for printouts that are easier to read.
You can use Merge & Center to combine cells to create a larger cell for titles and labels.
Themes on the Page Layout tab are used to change several formats at once on your worksheet.
If you want to add a company logo or graphic, click Pictures or Online Pictures on the Insert tab.
You can size or rotate an image using its handles.
Numbers can be formatted appropriately using commands in the Number Format list on the Home tab.
Conditional Formatting on the Home tab makes use of color to help you evaluate data.
The Quick Analysis button can also be used to apply conditional formatting.
Apply Your Knowledge: Creating a Worksheet (Soccer League)
Complete this Apply Your Knowledge activity to learn the steps for creating a worksheet.
As secretary of the youth soccer league, you need to keep a record of the team coaches. Follow the steps below to create a worksheet with coach information.
Create a new Excel workbook.
Type “Any Town Youth Soccer League” in cell A1.
Point to cell A1 and then drag from A1 to cell I1.
Point and drag to select the indicated cells.
Select Merge & Center in the Home tab to merge the cells and center the title.
Ensure cell A1 is selected.
Use commands on the Home tab to change the text in cell A1 to Arial, size 24, bold, and dark orange.
Select Online Pictures in the Insert tab to open a dialog box.
Type “soccer ball” in the search box and select the magnifying glass to display search results.
The Inserted Soccer Ball Image
Select a soccer ball image.
Select Insert to place the graphic on the worksheet.
Drag the image to center it below the title.
Size the image appropriately.
Enter the following data, starting at row 10.
Select the entire row 10.
Bold the contents of row 10.
Select Wrap Text from the Home tab.
Widen column B so that “First Name” is on the top line and “Coach” is on the second line in cell B10.
Widen columns C, D, and E so that they match those in the image titled The Formatted Worksheet Header.
The Formatted Worksheet Header
Select cells B10 through C14.
Select the 20%–Accent1 style from the More Styles group in the Home tab.
Select cells D10 through E14.
Select the 20%–Accent4 style from the More Styles group in the Home tab.
The Table with Borders
Bold the contents of row 10 again.
Select cells A1 through I14.
Select All Borders from the arrow beside Borders in the Home tab.
Select Themes from the Page Layout tab.
Select the theme titled Ion.
Note that while your worksheet fonts and colors have changed to match the theme, the title that you previously formatted remains in the font you selected. Only Themed Cell Styles and default fonts are changed when a new theme is applied.
Adjust the columns to display the labels appropriately, if necessary.
Save the workbook, naming it “Any Town Soccer Coaches.”
Your finished workbook should look similar to the figure below.
The Finished Soccer Coaches Worksheet
Apply Your Knowledge: Creating an Inventory Worksheet (Clever Acme Corp.)
Complete this Apply Your Knowledge activity to learn the steps for creating an inventory worksheet.
As the owner of Clever Acme Corp., you want to keep better track of inventory. Your employees aren’t reordering early enough, so you want to have your worksheet alert you to low stock numbers. Follow the directions below to create an inventory worksheet.
Create a new Excel workbook.
Enter the data as shown below.
Format the title as Arial, size 22, bold, and purple.
Format the other labels as Calibri, size 14, bold.
Widen columns as needed.
Select cell B3 (“Stock #”).
Select Center from the Alignment group in the Home tab.
Select cell D3 (“Cost”).
Select Align Right from the Alignment group in the Home tab.
, the Cost label. This column will display dollar amounts, which are right-aligned in a cell. On the Home tab, in the Alignment group, select Align Right.
Right-align cells E3 and F3 (“Last Shipment” and “Current Stock”).
Select cells D4 through D9.
Select Currency from the Number Format list in the Home tab.
Select cells E4 through E9.
Select Long Date from the Number Format list in the Home tab.
Type “Doodad” in cell A4 and select Tab.
Type “Green” in cell B4 and select Tab.
Type “.24” in cell C4 and select Tab. (Note that .24 was automatically converted to $0.24 because the cell is formatted to display values as dollar amounts.)
Type “6/10/20” in cell E4 and select Tab. (Note that the date is converted to the long format.)
Widen column E if ###### shows in cell E4 instead of the date.
Type “122” in cell F4 and select Enter.
Ensure cell A5 is selected.
Type “D” in cell A5.
Select Tab to accept AutoComplete’s suggestion of “Doodad” for the entry.
Complete the data entry as shown below. (Remember that you need only type the numerical value for the dollar amounts because the cell is already formatted. Also, you need only enter the shortened date because the formatting will determine the day of the week to display.)
Widen any columns if necessary.
Select cells F4 through F9.
Select Home > Conditional Formatting > Highlight Cells Rules > Less Than to open a dialog box.
Type “20.”
Select Light Red Fill from the list.
Select OK. (The spreadsheet will now alert you when stock is low. You can see that two items are low on stock.)
Select cells F4 through F9. (Notice the Quick Analysis button that appears in the lower right of the selected range.)
Select Quick Analysis.
Select the Format tab in the gallery, if necessary, to reveal several conditional formatting options.
Select Data Bars. (Now all of the values reflect their relative stock.)
Select the cells A1 through F9.
Apply All Borders to cells A1 through F9.
Save the workbook, naming it “Clever Acme Corp. Inventory.”
Identify the Steps Needed to Produce Calculations Using Formulas and Functions
Formulas and Functions
Read this assignment.
The power of a spreadsheet is generating data using formulas and functions that calculate values. Formulas become even more meaningful when the results are based on data from your worksheet. For example, when you want to know the total of the expenses in your budget worksheet, you can use a formula to add the expenses and display the total.
Using Formulas
A formula is a mathematical statement that calculates a value. To create a formula in your worksheet, you must begin with an equal sign (=). For example, if you type “=25+2” in a cell, it will display “27”.
Use the following symbols to represent operators:
Exponentiation: ^
Multiplication: *
Division: /
Addition: +
Subtraction: –
The exponentiation symbol raises a number to a power, as in 32. To perform this same calculation in Excel, you use the formula =3^2 to display 9.
Formulas are evaluated using the mathematical order of operations, which states that exponentiation is calculated first, followed by multiplication and division, and then addition and subtraction. The following formulas demonstrate the order of operations:
To change the order of operations, you can put parentheses around calculations to be performed first.
Display Formulas
When you enter a formula into a cell, the result of the calculation is displayed. To review or edit the actual formula, select the cell and use the Formula bar to access the actual cell contents. This can be inconvenient when reviewing an entire worksheet. To see all the formulas in a spreadsheet at once, select Show Formulas on the Formulas tab, or press both Ctrl and the backtick key (`) located next to the number 1 key on your keyboard.
When formulas are displayed, column widths adjust automatically to display the entire formula. Selecting Show Formulas again reverts back to the original formatting.
Worksheet FormulaWorksheet Formula Bar
Check for Errors
Dividing by 0 causes this error to appear in the cell.
Excel checks formulas when you enter them. When a formula can’t be calculated, an error is displayed and a green triangle is added to the upper-left corner of the cell. For example, the formula “=4/0” displays “#DIV/0!”. This displays because numbers can’t be divided by 0. You can select the cell with the error and use Error Checking to display a description of the error and a list of options.
You can click Show Calculation Steps to display a dialog box that examines the formula.
If errors are displayed in your worksheet but the Error Checking menu isn’t available, you can click Error Checking on the Formulas tab to check all the cells in your worksheet.
Using Cell References
When you want to make calculations based on the data in your spreadsheet, you can include a cell reference in your formula rather than the actual number. For example, in the figure, cell C1 displays a value (total of $540) based on multiplying the numbers in cells A1 (Quantity of 12) and B1 (Cost is $45).
Cell C2 shows a value based on cells A2 and B2.
When you use cell references in formulas, your spreadsheet becomes easier to update. If you change a value in a cell referenced in a formula, the formula automatically recalculates.
Although you can type the cell name in a formula to create the cell reference, you can avoid typing errors by clicking the cell instead. To do this, type a formula up to the point where the cell name appears, and then click on the cell to be used in the calculation. You can also use the arrow key to select the cell.
You’ll almost always want to use cell references in worksheet formulas rather than actual numbers. It’s much easier to have the calculation results change automatically when values are entered than to update each formula manually with the correct numbers. Whether your data are sales figures, age ranges, horsepower, voltage, or grades, your data will likely change over time, and formulas with cell references make sure your spreadsheet stays up to date and accurate.
You can’t create a formula in a cell that’s referenced by that formula because this will cause a circular reference error—the formula can’t be calculated until the cell has a value, but the cell can’t have a value until the formula is entered.
Display Formulas with Cell References
Double-clicking a cell displays the formula and highlights the referenced cells in that formula.
When you select Show Formulas on the Formulas tab, selecting a cell with a formula outlines the cells referenced in that formula. If you want to examine just one formula, double-click the cell to display the formula and outline referenced cells.
Copy Formulas
Often you want to copy the formula you’ve created to the next cell in a row or column. For this, you can use Copy and Paste, but when copying to adjacent cells, you can also use the Fill Handle, a point in the lower-right corner of the active cell that you can drag to other cells. Select the cell with the formula you want to copy, and then drag its Fill Handle to copy the formula to a range of cells.
Cell C2 shows a value based on cells A2 and B2.
When a formula is copied, cell references automatically change relative to the new row or column. For example, in the spreadsheet above, the formula in cell C2 is =B2*A2. When copied to the cells below, it changes to =B3*A3 and =B4*A4, respectively. When cell references can change in this way, they’re called relative cell references.
Relative references change when a formula is moved or copied from one cell to another. Relative references are a powerful tool in applying the same calculation to hundreds of rows and columns of data.
Use Absolute and Mixed Cell References
If you want a cell reference in a formula to remain the same when you copy it to another cell, then you’ll need to make it an absolute cell reference, which is a cell reference that doesn’t change if a formula is copied to other cells. For example, in the figure below, the formula (=C5*$B$2) refers to a tax rate of six percent in cell B2.
The selected cell’s formula includes an absolute cell reference, shown with dollar signs in front of the cell reference.
The cell reference has dollar signs in front of the column letter and row number to indicate that neither should change when you copy the formula to another cell. You create an absolute reference by pressing F4 after you type or click on the cell to be referenced.
In some cases, you may need a mixed cell reference in your formula where either the column or row doesn’t change when copied. In this case, you can press F4 until the appropriate reference appears.
Excel references column headings and table names instead of cell references when formulas are inserted into a table. Cell names and range names assigned to a given cell or range make it easier to understand what calculations are being performed in a formula and to reuse the references as necessary.
Watch this video to learn relative and absolute cell references.
Using Functions in Formulas
You might be thinking about all the work needed to create something as simple as a formula that sums the values in five cells. From what you’ve learned so far, the formula might look like =A1+A2+A3+A4+A5. That’s a long formula for a simple calculation! But Excel is a spreadsheet application with many features for analyzing data, so it includes built-in functions for use in formulas.
A function is a named set of operations that take one or more values and produce a single output. For example, the SUM function accepts a cell range and returns the sum of the values in those cells, as in =SUM(A1:A5). With this simple formula, you can calculate the sum of the values in cells A1 through A5.
Functions are always used as part of a formula and usually require data, called arguments, inside parentheses after the function name. The SUM function, for example, requires a cell range with the first and last cells separated by a colon (A1:A5) or a set of cell names separated by commas (A1,A3,A5).
The blue outline shows the range in the formula in cell C9.
To add a cell range to a function using a mouse, type the function up to the point where the cell range is needed, and then click and drag from the first cell in the range to the last before typing the closing parenthesis. To help you, Excel displays a colored outline showing the range.
If you want to edit the range using the mouse after you’ve entered the formula, click the cell with the formula and then drag the cell outline. You could also simply delete the range and select a new one.
Watch this video to learn how to use functions to perform calculations.
Commonly Used Functions
The following are some of the commonly used Excel functions:
SUM adds the values in a range and returns the sum.
AVERAGE adds the values in a range and then divides the total by the number of values.
COUNT returns the number of cells in a range that contains values.
COUNTA counts the number of cells that aren’t empty in a range of text or in logical or error values.
COUNTBLANK is a statistical function on the Formulas ribbon of the Functional Library group that will count the number of empty cells in a range of cells.
MAX returns the maximum value in a range of cells.
MIN returns the minimum value in a range of cells.
In many cases, you’ll need to create the formula with one of these functions to make the calculation you need. However, the Totals tab in the Quick Analysis gallery is another option for creating totals.
The Totals tab gives you a quick way to create totals. Pointing to a total allows you to preview the results.
Watch this video to learn how to use functions to count cells.
The IF Function
Another commonly used function is IF, which displays a value based on a comparison. For example, if you want to display STUDY when the grade average in cell G7 is below 85 or GREAT otherwise, use =IF(G7<85, “STUDY”, “GREAT”).
The IF function takes the general form IF (comparison, value if true, value if false) and can use the following logical operators in the comparison:
If the logical test entered as an argument is true, the IF function will return a value. If the logical test isn’t true, it will return a different value. For example, if the test is true it might return a 1, and if the test is false, it might return a 0.
Syntax: =IF(logical_test, value_if_true,value_if_false)
Example: =IF(F10>=$B5,F10*$B6,”No Commission”)
Financial Spreadsheets
Excel has hundreds of built-in functions, many of which are used in financial calculations for mortgages, loan payments, depreciation, accrued interest, and other complex calculations. You can search for these functions in Excel’s Help menu or the Show Me box to see what each does.
Sorting Data
Your spreadsheet should present data in an organized way. One way to make sure information is presented in a logical format is to order rows of data by sorting—organizing by using a set parameter. You can sort in ascending order, from low to high, or descending order, from high to low. If the data you’re basing the sort on is a date or time, then an ascending sort puts rows into chronological order.
The Sort & Filter option allows you to rearrange rows quickly and easily.
Sort your worksheet by first selecting the rows to be sorted. Drag from one row number to the last in the range to be sorted and then, on the Home tab, select Sort & Filter for options.
Use From Smallest to Largest for ascending order or From Largest to Smallest for descending order. These commands automatically base the sort on the data in column A. Use Custom Sort to display a dialog box where you can select a different column to base the sort on.
This dialog box pops up when you select Custom Sort.
Microsoft Excel Practice Files
Click the link to download practice files that will be used throughout this lesson.
Key Points and Links
Key Points
You can add formulas to your worksheet to make calculations. A formula must begin with an “=”.
You must create formulas with an order of operations in mind. To see formulas in cells instead of the calculated values, click Show Formulas on the Formulas tab.
When a formula can’t be calculated, you can display a description of the error with the Error Checking menu.
Use cell references instead of values in your formulas to use data from your worksheet to calculate a value.
Press the F4 key to change a cell reference in the formula you’re creating from relative to absolute.
Functions can be included as part of a formula to make calculations.
Common functions include SUM, AVERAGE, COUNT, MIN, MAX, and IF.
The IF function displays a value based on a comparison.
Apply Your Knowledge: Adding a Percent Formula (Semester Grades)
Complete this Apply Your Knowledge activity to learn the steps for adding a percent formula to a worksheet.
You’ve received your first grades of the semester and you want to update your spreadsheet to calculate your grades as percentages instead of points. Follow the directions below to update your spreadsheet.
Start Excel.
Open Semester Grades, the worksheet you created in Guided Practice: Organizing a Worksheet (Semester Grades).
Format the title to be larger, bold, and in your favorite color.
Bold the column titles and make them two points larger.
Bold your class names and widen the column, if necessary.
Select cells C4 through C8.
Select Percentage from the Number Format list on the Home tab.
Type “=90/100” in cell C4 and press Enter. (Your English test grade is shown as 90.00% instead of 90 points out of 100.)
Select cells C4 through C8.
Select Decrease Decimal from the Number group in the Home tab. (The English test grade is shown as 90.0% instead of 90.00%.)The Decrease Decimal Button
Type “=5/6” in cell C5. (Your quiz grade is displayed at 83.3%.)
Type “=3/5” in cell C6.
Type “=92/100” in cell C7.
Type “=100/100” in cell C8.
Select Show Formulas on the Formulas tab (or press Ctrl+`) to widen the columns and display the formulas.
Check your formulas for errors.
Select Show Formulas again to display values.
Select cells C4 through C8.
Select Home > Conditional Formatting > Data Bars > Gradient Fill Blue Data Bar to apply conditional formatting so you understand your grades at a glance.
Save the modified workbook.
The Finished Semester Grades Worksheet
Apply Your Knowledge: Creating a Payroll Workbook with Formulas (Clever Acme Corp.)
Complete this Apply Your Knowledge activity to learn the steps for creating a payroll workbook with formulas.
As the owner of Clever Acme Corp., you need to keep track of payroll. You’ll use the payroll spreadsheet to perform the following tasks:
Ensure the overtime pay is 50 percent higher than the hourly pay rate
Discover how many hours employees are working at their standard pay rate for 40 hours a week or less (known as straight time hours)
Calculate overtime hours by subtracting straight time hours from the total number of hours worked
Determine employees’ straight pay, which is the product of their hourly rate and their straight time hours
Calculate employees’ overtime pay, which is the product of their hourly overtime rate and their overtime hours
Calculate the employees’ gross pay (the sum of their straight pay and overtime pay)
Determine employee bonuses based on their week’s pay
Analyze the payroll data by determining the maximum, minimum, and average values for all the columns
Follow these steps to modify the Clever Acme Corp Payroll workbook:
Start Excel.
Open Clever Acme Corp Payroll.xlsx, one of the files you downloaded at the beginning of this section.
Note the cell titles with values that need to be calculated.
Type “=” in cell C4.
Type “*150%” in B4 and select Enter.
Select cell C4.The Worksheet with Inserted Formula
Drag the Fill Handle in the lower right of cell C4 to cell C14 to copy the formula for all employees. (Alternately, select cell C4 and press Ctrl+C. Then, select the range C5 through C14, and press Ctrl+V.)
Select cell C10. (Note that the cell reference in the formula bar has changed relative to the row.)
Type the formula “=IF(D4<=40,D4,40)” in cell E4. (Note that you can select the cell when you need to enter a cell reference.)
Drag the Fill Handle on cell E4 to copy the formula for the remaining employee rows. (Alternatively, copy the contents of cell E4 and paste the data to the remaining employee rows.)
Check your formula results to confirm they make sense. (If not, review your formulas.)
Add the rest of the data from the table below.
Type “=D4-E4” in cell F4. (Note that the result is 0 because the employee didn’t work beyond 40 hours that week.)
Copy the overtime hours formula in cell F4 to the remaining employees.
Check your formula results to confirm they make sense. (If not, review your formulas.)
Enter “=B4*E4” in cell G4. (Note that the first employee’s straight pay is $330.00.)
Copy the straight pay formula in cell G4 to the remaining employees.
Enter “=C4*F4” in cell H4.
Copy the overtime pay formula to the remaining employees.
Enter “=G4+H4” in cell I4.
Copy the gross pay formula to the remaining employees.The Payroll Worksheet with Additional Formulas
Add Minimum:, Maximum:, and Average: labels in cells A15, A16, and A17 and bold them.The Minimum, Maximum, and Average Rows
Enter the formula “=MIN(B4:B14)” in cell B15.
Enter the formula “=MAX(B4:B14)” in cell B16.
Enter the formula “=AVERAGE(B4:B14)” in cell B17.
Select cells B15 through B17.
Choose Copy in the Home tab to display a moving dashed line around the selected cells. (Alternately, press Ctrl+C.)
Select cell C15.
Choose Paste in the Home tab to duplicate the formulas in the cells. (Alternately, press Ctrl+V.)The Copy and Paste Function
Select cell D15.
Select the Paste arrow in the Home tab.
Choose Formulas to copy the formulas without formatting the cells as currency. (Alternately, press Ctrl+Alt+V.)
Copy the formulas to E15 and F15 by pasting formulas in the same manner.
Copy the formulas to cells G15, H15, and I15 by pasting.
Format cells D17 and F17 to display one decimal place. The Minimum, Maximum, and Average Rows with Formulas Applied
Select row 3.
Select Insert twice in the Home tab to insert two blank rows.
Type “Bonus” in cell A3, and then right-align and bold the label.
Type “10%” in cell B3.
Type “Bonus Pay” in cell J17.
Format cell J17 to be similar to the other column labels and widen the column if necessary.
Type “=I6*B3*” in cell J6.
Press the F4 key to make dollar signs appear in the cell reference.
Press Enter. (The bonus amount of $33.00 appears.)
Copy the bonus pay formula for all the employees.
Copy the minimum, maximum, and average formulas into the Bonus Pay column.
Change the standard pay rate for Employee AO-3450 to $15.50 per hour. (Notice how all the related cells are recalculated.)
Select rows 6 through 16.
Select Sort & Filter > Custom Sort in the Home tab to open a dialog box.
Choose Gross Pay in the Sort by list.
Choose Largest to Smallest in the Order list.
Select OK to reorder the rows in descending order by the data in the Gross Pay column.
Select cells I6 through J16.
Select the Quick Analysis button.
Choose Totals.
Choose the second Sum totals option to place totals in column K.
Enter “Total Pay” in cell K5 and make it bold
Save the modified Clever Acme Corp. workbook.
The Finished Payroll Worksheet
Describe How to Interpret Data Through Charts
Charts
Read this assignment.
Data are often easier to understand when presented in a chart. You can use charts to demonstrate a trend, show an area of growth or decline, and support your overall analysis. Charts are often used in conjunction with data to draw a conclusion because the numbers alone don’t provide the picture needed to fully understand a situation.
Chart Terminology
A chart, sometimes called a graph, visually represents a range of data. A pie chart displays data as a percentage of a whole.
This shows a pie chart as it appears in an Excel worksheet.
A chart is a graphic with many elements:
A chart title, which corresponds to the title of the data series column
Slices, or chunks of the chart that are sized relative to the values in the data series
A legend, which labels the components of the chart based on the category labels in the first column of data
A chart area, which provides the boundaries of the graphic
A Chart Elements button with quick access to elements that can be added or removed
A Chart Styles button for selecting a style and color scheme for the chart
A Chart Filters button for adding and removing categories, series titles, and category titles, and for editing the data source
A pie chart has only one data series, but other chart types can include multiple columns of values.
Column charts are useful for comparing differences in value by using vertical bars sized relative to the values in the data series.
Bar charts are useful for comparing differences in value by using horizontal bars sized relative to the values in the data series.
Line charts connect data values with a line to show how data changes over time.
Area charts are used to demonstrate the total value across a trend.
With the rise of the internet as the main information-gathering tool, people have become accustomed to getting information in smaller and smaller chunks. Often, a social news website will publish an article with just a graphic and one paragraph explaining that graphic. Charts in presentations serve the same purpose as these news graphics: They’re a way to present the information in a condensed and easy-to-understand format without needing to dig through the details of the data.
In your career, there may be times when your supervisor asks for a report, and he or she will likely be expecting a chart to summarize the information. If you simply hand in a bunch of numbers, you might spend hours breaking them down when a chart would have done the same in minutes.
Creating and Customizing a Chart
The easiest way to create a chart is to first select the data you want in it. Your selection should include column titles and row labels so that Excel can include this information automatically. Next, select Recommended Charts on the Insert tab to display a dialog box.
This dialog box shows charts that Excel recommends using.
Navigate through recommended charts on the left of the dialog box to see a preview, or select the All Charts tab and select charts there to see additional previews. After finding a chart that’s close to what you want, select the preview and then click OK. The chart is placed on the worksheet, where you can customize it.
Because the chart is a graphic, there are many ways to customize it. For example, in a pie chart, you can drag a slice away from the others to create an exploding pie chart. To make any changes, you must select the chart first. A selected chart displays handles and the Chart Elements, Chart Styles, and Chart Filters button. The Ribbon displays the contextual Design and Format tabs under Chart Tools. These tabs contain numerous options for further formatting your chart.
Watch this video to learn how to insert a chart.
Move, Size, Delete, and Change a Chart
Because a chart is a graphic, you can move it to another area of the worksheet by dragging. In some cases, your workbook will be better organized if you place the chart on a separate worksheet. To move a chart to its own worksheet, select the chart and then select Move Chart on the Design tab under Chart Tools. In the displayed dialog box, select New Sheet and then OK. You can access the chart by clicking the appropriate sheet tab below the worksheet.
To size a selected chart, you can drag a handle. Pressing the Delete key removes the chart from the worksheet. If the chart is on its own sheet, right-click the sheet tab and click Delete to remove the chart and any data on that sheet.
To change the actual chart type, select the chart and choose Change Chart Type on the Design tab under Chart Tools to display a dialog box for selecting a new chart.
Apply Chart Layouts
Many of the commands to modify and format charts are accessible on the contextual Format tab under Chart Tools. This tab appears when a chart or chart element is selected, and it disappears when you select a worksheet element outside the chart.
For additional formats, the Format task pane can provide the overall formatting of the chart. Select Format Selection from the Current Selection group in the Format tab under Chart Tools.
The Alt Text Box
In Excel, you can add alternative text (alt text) to objects such as shapes, pictures, charts, and SmartArt graphics within your document. Alt text is used to provide support for individuals with visual impairments to better understand the context of the objects. If an individual is using a screen reader, the alt text description will be read aloud, providing a bit more context to the user. Without alt text, users would know only that they’ve reached a picture, without knowing what the picture shows.
You can add alt text by right-clicking the object and selecting Edit Alt Text. You can also select the object and then choose Alt Text from the Format tab under Chart Tools.
Enter the alt text, being as concise but descriptive as possible to provide an overview of the object and its context.
Adding Data Series to Charts
To further identify details on your Excel chart, the Select Data Source dialog box can manage chart data. This feature allows you to edit the entire dataset feeding the chart, or you can make edits for the data added to any of the individual data series. There are options to remove or add back any of the individual data series to the chart, reorder how the data series appears on the chart, or switch the x and y axes on the chart.
Add data series to a chart using the Select Data Source dialog box by selecting Select Data from the Data group in the contextual Design tab under Chart Tools.
Reorder Data
In Excel, there are options to change the order of your data; for example, switching between rows and columns in the source data. To do so:
Select the chart that you want to change.
Choose the Design tab under Chart Tools.
Select Switch Row/Column.
Quick Analysis Charts and Sparklines
You can also create charts by clicking the Quick Analysis button and then selecting the Charts tab.
Another option for creating charts is to use the Quick Analysis button, which is displayed when you select a range of numeric data. You can also access the Quick Access dialog box by pressing Ctrl+Q. The Charts tab has several options, or you can select More Charts to display the Insert Charts dialog box.
Sparklines are tiny charts inside single cells. They’re useful for showing data trends, as the example image shows.
Sparklines can show data trends.
In the Sparklines tab of the Quick Analysis dialog box, you can point to a Sparkline type—such as line, column, or win/loss—to see a preview, and then click an option to insert the chart into the selected cell.
Sparklines are also available from the Quick Analysis button.
You’ll probably want to change the cell width to better accommodate the Sparkline chart. Sparklines can be customized with commands on the contextual Design tab under Sparklines Tools, which is added to the Ribbon by selecting a cell with a Sparkline.
When you want to remove a Sparkline chart, right-click the cell with the chart and click Delete or click Clear > Clear All on the Home tab.
Watch this video to learn how to use sparklines.
Printing a Chart
You may need to change the worksheet margins or drag a chart below the data in order to print it on a page. To determine how a chart will look, choose Print on the File tab for a preview.
Remove Conditional Formatting
Conditional formatting is a feature that will format data if it fits within a specific set of criteria, and it allows you to visually identify concerns, patterns, and trends. This is done by highlighting with color or using other formatting options, such as bold or the addition of icon sets, to feature data that fits within the set parameters of the set condition. For example, if a condition is met, the cells that fit within that criteria are formatted. Excel has a variety of built-in conditions, or you can create your own.
To remove conditional formatting in a worksheet, navigate to the Home tab, and select the arrow next to Clear Rules > Clear Rules from Entire Sheet from the Conditional Formatting menu.
To remove conditional formatting in a range of cells, choose the range of cells that contains conditional formatting. Click the Quick Analysis button or press Ctrl+Q. Then, click Clear . . . in the Formatting tab to remove the conditional formatting from the cells.
You can also find and remove a specific type of conditional formatting throughout a whole worksheet. To do so:
Choose a cell within your worksheet that has the conditional formatting you wish to remove.
Select the arrow next to Find & Select in the Home tab.
Choose Go To Special.
Choose Conditional formats.
Select Same under Data validation so that it will search for all the cells that contain the same formatting rules.
Return to the Home tab.
Select Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Key Points and Links
Key Points
A chart is a graphic that contains many elements.
A selected chart displays the Chart Elements, Chart Styles, and Chart Filters button for editing.
To create a chart, select the data you want in the chart and then click Recommended Charts on the Insert tab.
You can also create a chart with the Quick Analysis button, which is displayed when a range is selected.
Sparklines can be created in individual cells with commands in the Quick Analysis button.
When a chart is selected, the contextual Design and Format tabs appear on the Ribbon under Chart Tools.
You can drag a chart to move it on a worksheet.
If you want a chart on a separate sheet, click Move Chart on the contextual Design tab under Chart Tools.
You delete a selected chart by pressing the Delete key.
Apply Your Knowledge: Adding Charts (Sales Data)
Complete this Apply Your Knowledge activity to learn the steps for adding charts to a workbook.
You’ve been asked to chart the data in a workbook. The workbook contains several sheets with unit sales data. Follow the directions below to add charts to the workbook.
Start Excel.
Open Unit Sales.xlsx, one of the files you downloaded in the previous section.
Ensure you’re viewing the Total Division Sales worksheet. (There are two sheets in the workbook.)
Select cells A3 through B7.
Select Recommended Charts in the Insert tab to open a dialog box.
Choose the pie chart on the left of the dialog box. (Note the preview on the right.)
Select OK. (A pie chart appears on the worksheet.)
Modify the chart title to read “Total Division Sales.”
Format the text as size 18, bold, using the Font commands in the Home tab.
Click the chart area to display the Chart Elements button.
Select Chart Elements.
Select the arrow by Legend.
Choose Top to move the legend to the top of the chart.
Select the Chart Styles button.
Select a style that displays the percent values on each slice.
Click the chart area to select it.
Drag a handle around the chart to make the chart slightly larger.
Move the chart closer to the data.
Save the modified workbook.The Total Division Sales Chart
Click the North America Sales tab at the bottom of the workbook to open a worksheet showing unit sales for each quarter.
Select cells A3 through E5.
Select the Quick Analysis button (or press Ctrl+Q).
Choose Charts in the gallery.
Select Clustered Column to add a chart to the worksheet.
Make the chart larger and move it below the data.
Update the title to read “Quarterly Sales” and then format it as size 18, bold.
Ensure the Quarterly Sales title is still selected.
Select the first WordArt style in the Chart Tools Format tab.
Select the chart area.
Choose Chart Elements.
Select Data Labels to display the unit sales above the bars.
Select the chart area.
Choose Chart Elements.
Select the arrow next to Axis Titles.
Select Primary Vertical to display a y-axis label.
Update the y-axis label to read “Units Sold.”
Save the modified workbook.The Quarterly Sales Chart
Select Print in the File tab.
Review the print preview to ensure the chart is displayed entirely on one page. (If not, use the Back Arrow and move the chart. Continue checking until you have the chart fitting on the same page as the data.)
Select cells A4 through E5.
Select the Quick Analysis button (or press Ctrl+Q).
Choose the Sparklines tab in the gallery.
Select Line to add Sparklines to each row.
Widen column F to see the Sparklines more clearly.SparklinesThe 3-D Area Chart Option
Select cells A3 through E5.
Select Insert Line or Area Chart from the Charts group in the Insert tab.
Choose 3-D Area to insert an area chart.
Select the chart.
Choose Move Chart in the Chart Tools Design tab to open a dialog box.
Select New Sheet.
Name the sheet “Quarterly Sales.”Use these settings to move the chart to a new sheet.
Select OK to move the chart to a new sheet.
Change the chart title to “North America Quarterly Sales” and make the font size 24.
Save the modified workbook.
The Finished Quarterly Sales Chart
Analyze Data Organized in Tables
Tables
Read this assignment.
It’s easier to manage and analyze related data in a spreadsheet table. Tables are also a good choice when you want to place information in a separate format with borders, formatting, and other features that improve data understanding.
In Excel, a table is a dataset composed of many rows and columns that Excel identifies as a single, collective object. Excel tables contain functionalities that enable organization, allow display changes, and perform calculations on worksheet data quickly and easily. The raw data initially entered remains the same no matter how many times the table data changes. Tables can be created from existing ranges, or you can create empty tables and populate them with data. Tables can also revert into simple ranges.
Additionally, defined names for tables can be made with cells and ranges to create references. When you create a table, Excel automatically assigns a generic name such as Table 1, Table 5, and so on. However, you can edit these names. Excel allows you to expand existing tables to accommodate additional data by inserting or deleting columns and rows within tables.
Creating and Editing a Table
When you select Table from the Insert tab, a dialog box appears, showing the range of data for the table.
Insert a Table
A table is a selected range of cells in a worksheet. Your selected range should contain labels in the first row for the table column headers. To create a table, you select the range and then select Table on the Insert tab, which displays a dialog box.
New tables appear with automatic formatting, which can be adjusted later.
After you verify the range and confirm that there are headers, select OK to create the table. Excel automatically applies formatting, which can be customized later.
The table headers include an arrow for displaying a menu with sorting and searching commands.
Modify a Table
You can customize table formatting with the contextual Design tab under Table Tools, which is available on the Ribbon when any table cell is selected. In the Design tab under Table Tools, you can select or clear Table Style Options to emphasize different aspects of your table. You can also use a different table style to change the entire look of your table.
To add a new row to a table, select the last cell of the table and press Tab. You can also resize a table by using the Resize Table command on the Design tab under Table Tools. To convert the table back to a cell range, select Convert to Range on the Design tab under Table Tools.
Insert and Configure Total Rows
The Total Row option allows you to add total data in an Excel table. There are several functions available in a drop-down list for each table column that can be used and applied. For example, the Total Row default uses the SUBTOTAL function. This allows you to include (or ignore) any hidden table rows. However, you can use other functions provided in the drop-down list.
Total Row displays the results of column-specific calculations and provides you with access to some of Excel’s built-in table summary function capabilities.
Configure Worksheet Total Rows
The following steps show how to insert and configure Total Row.
Select any cell in the table.
The Table Tools tab will appear.
Choose Design and check the Total Row box.
The Total row is added under the last row of the table.
Choose the column you’re interested in being totaled and select the appropriate option from the list. (None, Average, Count, Count Numbers, Max, Min, Sum, StdDev, and Var are the most common.)
Sorting, Searching, and Filtering Table Data
When you click a table header arrow, you see a menu of options, which include sorting A to Z, Z to A, or by color. The menu also includes the Text Filters option.
This menu displays when you click on a table header arrow.
Select any sort command to order the table rows based on the data in that column.
To locate a specific entry in the column, type the text you’re looking for in the Search box and then click OK. When a search is applied, the table displays only those rows containing the search text. To clear the search and again display all table data, click the header arrow and then click the Clear Filter command.
A filter is an advanced form of search where you can specify multiple criteria. For example, if you want to search for a value in a range, select Number Filters, and then Between to display a dialog box for entering the criteria.
You can customize filters when you search for a value in a range of data.
Watch this video to learn how to filter data.
Sort Data by Multiple Columns
Excel offers features to sort data by multiple columns in a worksheet. Cell ranges can be sorted by a Quick Sort or a Custom Sort.
Quick Sort allows you to sort the data in a range according to a set of predefined criteria. By using quick sorts, data can be sorted one column at a time, in ascending or descending order according to the type of content stored in that column. With quick sorts, you can sort by column only (not by row).
Custom Sort allows you to sort your data range by using more highly defined criteria than is possible using Quick Sort. Custom sorting allows you to sort by row or column, and to sort on multiple rows or columns and define specific short criteria. Custom Sort provides options to sort based on cell or font color or based on conditional formatting icons.
Use the Sort dialog box to define and manage custom sorts. Access the Sort dialog box by selecting Sort in the Sort & Filter group on the Data tab.
Watch this video to learn how to sort data by multiple columns.
Using Quick Analysis
The Quick Analysis button appears when you select a range of data in a table. With this button, you can quickly add formatting, generate charts and Sparklines, add summaries, and perform data analysis. Click Quick Analysis or press Ctrl+Q to display available options.
Choose a tab along the top of the gallery, and then point to the options to preview the results. Choose any option to apply it to the selected range.
PivotTables
Quick Analysis Tables include options for creating a PivotTable. You use a PivotTable to analyze and summarize your data. For example, in a table with duplicate entries associated with dollar amounts, a PivotTable combines the duplicate entries into one and then sums their dollar values.
A PivotTable is one of the table options in the Quick Analysis button.
A PivotTable is placed in a new worksheet. If you later want to remove the PivotTable, right-click the worksheet tab and then click Delete.
You can also create a PivotTable from a selected range of data by clicking Recommended PivotTables or PivotTable on the Insert tab. These commands give you the option of placing the PivotTable on the same worksheet as the selected range.
Tables are very useful for organizing data into a formatted area. This allows a worksheet to have other elements such as charts with less confusion. Many of the Excel templates, which are available on the File tab when you create a new workbook, use tables.
Reflect and Respond
Now that you’ve learned about many ways to organize and present data, respond to the following questions.
Why should you consider the audience when preparing data for a presentation?
Describe the differences in the way you might present numeric data to a group of children compared to a group of financial advisers.
Key Points and Links
Key Points
A table is used to manage and analyze spreadsheet data more easily.
You create a table from a selected range by clicking Table on the Insert tab.
When a table is created it has headers with arrows for displaying a menu of commands.
You can customize table formatting with options on the contextual Design tab under Table Tools.
You can resize a table with the Resize Table command on the contextual Design tab under Table Tools.
You sort table data with commands that are displayed when you click a header arrow button.
Filtering a table to display rows that meet certain criteria is an advanced form of search.
You filter and search a table with commands on the menu displayed by clicking a header arrow.
When you want to display all the rows again after a search or filter, click the header arrow button and click Clear Filter.
Apply Your Knowledge: Creating a Table from Data (Sales Data)
Complete this Apply Your Knowledge activity to learn the steps for creating a table from data.
You’ve been asked to analyze and summarize data and report which cities have sales greater than $175,000. You’ve decided you can best present the information in the most informative way you can by creating a table from the data. Follow the directions below to modify the workbook.
Start Excel.
Open Southeast Sales.xlsx, one of the files you downloaded in an earlier section.
Note the large amount of data, which can be overwhelming to analyze.
Select cells A3 through C29.
Select Table in the Insert tab to open a dialog box.
Choose OK. (The data are now formatted into a table, and the column headers have arrows.)
Sort the State column in A-to-Z order to reorder the rows alphabetically by state.
Select the arrow in the Sales column.
Select Number Filters > Greater Than to open a dialog box.
Type “175000” in the list next to Is greater than.
Select OK. (The filter is applied and only those cities matching the criteria are displayed.)
Select the Sales header arrow and choose Clear Filter. All the data are displayed again.
Select cells A4 through C29.
Choose the Quick Analysis button in the lower right of the selection (or press Ctrl+Q).
Select Color Scale from the formatting options to format the numeric data with colors indicating sales low to high.
Select cells A4 through C29 again.
Choose the Quick Analysis button in the lower right of the selection (or press Ctrl+Q).
Choose Clustered Column in the Charts tab to display a chart.
Move the chart so that it’s beside the data.
Change the chart title to “Sales by City.”
Select cells A4 through C29 again.
Choose the Quick Analysis button in the lower right of the selection (or press Ctrl+Q).
Choose Totals and then Sum in the dialog box. (A double line is added to the bottom of the table with total sales below it.)
Select cell B30 and delete the value.
Select all the data, excluding the new sum (cells A4 through C29).
Choose the Quick Analysis button in the lower right of the selection (or press Ctrl+Q).
Select the Tables tab in the dialog box.
Choose the first PivotTable option. (A PivotTable that combines the sales by state is placed on a new sheet.)
Select the numeric data in the new PivotTable.
Format it as Currency with 0 decimal places.
At the bottom of the worksheet, click the Sheet1 tab to return to the original table.
Save the modified workbook.
Your table and chart should look similar to the figure below.
The Finished Southeast Sales Worksheet
Excel Graded Project
Follow the instructions provided to complete your exam.
Introduction
This project requires you to develop a spreadsheet that tracks the cost of elementary school supplies distributed to teachers over a two-month period. You’ll assume the role of Office Manager, who has multiple responsibilities, including distributing and maintaining classroom supplies. For this project, you must prepare an Excel spreadsheet and chart that will be sent to the school District Manager.
Instructions
Create a Spreadsheet
Start Excel and create a blank workbook.
Enter the data shown in the following table and then format, sizing columns as necessary and right-aligning all number values. Save your file, naming it School Supplies.
Merge and center cells A1 through H1. Format the school name as Title style.
Merge and center cells A2 through H2. Format the label as Heading 1 style.
Bold the labels in row 4 and right align the labels above the numeric values.
Format the Cost/Unit values as Currency with 2 decimal places. Your spreadsheet should look like the figure below.
In column D, enter appropriate formulas to calculate the September costs for each school supply. Your formulas must use cell references. Format the calculated values as Currency with 2 decimal places.
In cell A16, enter the label “Grand Total” and format it as bold.
In cell D16, enter a formula that uses a function to calculate the total of the column.
In column E, enter formulas that calculate the percentage of the grand total for each type of school supply (total cost of each item divided by the grand total). Your formulas must use appropriate absolute cell references. Format the calculated values as Percentage with 0 decimal places.
In cell E16, enter a formula that uses a function to calculate the total of the column (it should total 100%).
Repeat the process you used in steps 7–11 to calculate values for October.
Sort the school supplies alphabetically. (Be sure to select cells A5:H14 to sort the entire row of data.) Your spreadsheet should look similar to the figure below.
Save the modified spreadsheet.
Create a Chart
Create a chart by first selecting the cell ranges for the chart. Drag from cell A4 to A14 to select the range and then press and hold the Ctrl key while you drag from cell D4 to D14. Continue to press and hold the Ctrl key while dragging from cell G4 to G14. Your selected cells should look like the figure below.
Insert a 3-D Clustered Column chart.
Change the chart title to read “Cost of Supplies.”
Position the chart with the upper-left corner in cell A18 and then size the chart until it extends to column H, as shown in the figure below.
Save and close the workbook.
Scoring Guidelines
Rubric
Submission Checklist
Before submitting your project, make sure you’ve correctly completed the following:
Create, save, and name an Excel file.
Enter data and labels.
Format labels with indicated styles.
Merge and center labels.
Format data with Currency and Percentage formats to the correct number of decimal places.
Size columns appropriately.
Use formulas that include cell references and a function where applicable.
Use relative and absolute cell references in formulas.
Sort data.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
