Excel Quizlet Lessons 25-34
Lesson 31
https://youtu.be/GopZzmkThPU
To use Document Inspector:
Click the File tab to access Backstage view. From the Info pane, click Check for Issues, then select Inspect Document from the drop-down menu. Clicking Inspect Document You may be prompted to save your file before running Document Inspector. Document Inspector will appear. Check or uncheck boxes, depending on the content you want to review, then click Inspect. In our example, we'll leave everything selected. Inspecting the workbook The inspection results will appear. In our example, we can see that our workbook contains comments and some personal information, so we'll click Remove All on both items to remove this information from the workbook. Removing personal information from the workbook When you're done, click Close. Closing the Document Inspector Protecting your workbook By default, anyone with access to your workbook will be able to open, copy, and edit its content unless you protect it. There are many different ways to protect a workbook, depending on your needs.
To protect your workbook:
Click the File tab to access Backstage view. From the Info pane, click the Protect Workbook command. In the drop-down menu, choose the option that best suits your needs. In our example, we'll select Mark as Final. Marking your workbook as final is a good way to discourage others from editing the workbook, while the other options give you even more control if needed. Selecting Mark as Final A dialog box will appear, prompting you to save. Click OK. Clicking OK to save the workbook Another dialog box will appear. Click OK. Clicking OK The workbook will be marked as final. A workbook marked as final Marking a workbook as final will not prevent others from editing it. If you want to prevent people from editing it, you can use the Restrict Access option instead.
What are reference styles?
Every Excel spreadsheet contains rows and columns. Most of the time, columns are identified by letters (A, B, C), and rows are identified by numbers (1, 2, 3). In Excel, this is known as the A1 reference style. However, some prefer to use a different method where columns are also identified by numbers. This is known as the R1C1 reference style. In the example below, the image on the left has a number over each column, which means it is using the R1C1 reference style. The image on the right is using the A1 reference style. While the R1C1 reference style is helpful for certain situations, you'll probably want to use the A1 reference style most of the time. This tutorial will use the A1 reference style. If you're currently using the R1C1 reference style, you'll need to turn it off. To turn off the R1C1 reference style: Click the File tab to access Backstage view. Click File on the Ribbon to go to the Backstage view. Click Options. Screenshot of Excel 2013 The Excel Options dialog box will appear. Click Formulas on the left side of the dialog box. Uncheck the box next to R1C1 reference style, then click OK. Excel will now use the A1 reference style.
To review tracked changes:
From the Review tab, click Track Changes, then select Accept/Reject Changes from the drop-down menu. Selecting Accept/Reject Changes If prompted, click OK to save your workbook. A dialog box will appear. Make sure the box next to the When: field is checked and set to Not yet reviewed, then click OK. Clicking OK A dialog box will appear. Click Accept or Reject for each change in the workbook. Excel will move through each change automatically until you have reviewed them all. Accepting a change Even after accepting or rejecting changes, the tracked changes will still appear in your workbook. To remove them completely, you'll need to turn off Track Changes. From the Review tab, click Track Changes, then select Highlight Changes from the drop-down menu. Clicking Highlight Changes... A dialog box will appear. Uncheck the box next to Track changes while editing, then click OK. Turning off Track Changes Click Yes to confirm that you want to turn off Track Changes and stop sharing your workbook. Confirming that Track Changes will be turned off To accept or reject all changes at once, click Accept All or Reject All in the Accept or Reject Changes dialog box. Turning off Track Changes will remove any tracked changes in your workbook. You will not be able to view, accept, or reject changes; instead, all changes will be accepted automatically. Always review the changes in your worksheet before turning off Track Changes.
To turn on Track Changes:
From the Review tab, click the Track Changes command, then select Highlight Changes from the drop-down menu. The Track Changes menu on the Review tab. The Highlight Changes dialog box will appear. Check the box next to Track changes while editing. Verify that the box is checked for Highlight changes on screen, then click OK. The Highlight Changes dialog box If prompted, click OK to allow Excel to save your workbook. Excel may prompt to save the workbook. Track Changes will be turned on. A triangle and border color will appear in any cell you edit. If there are multiple reviewers, each person will be assigned a different color. Select the edited cell to see a summary of the tracked changes. In our example below, we've changed the content of cell D14 from ? to Getting to know your team. The change made in this cell is tracked. When you turn on Track Changes, your workbook will be shared automatically. Shared workbooks are designed to be stored where other users can access and edit the workbook at the same time, such as a network. However, you can also track changes in a local or personal copy, as seen throughout this lesson.
More resources
If you're interested in learning more about Excel, we recommend checking out the tutorials below. Excel Formulas Our Excel Formulas tutorial has more information on creating formulas, and it will give you opportunities to practice with real-world scenarios. Excel Tips Our Excel Tips tutorial has articles about specific Excel functions, such as VLOOKUP and COUNTA, as well as general Excel tasks.
To list changes on a separate worksheet:
You can also view changes on a new worksheet, sometimes called the Tracked Changes history. The history lists everything in your worksheet that has been changed, including the old value (previous cell content) and the new value (current cell content). Save your workbook. From the Review tab, click the Track Changes command, then select Highlight Changes from the drop-down menu. Selecting Highlight Changes... The Highlight Changes dialog box will appear. Check the box next to List changes on a new sheet, then click OK. Listing changes on a new worksheet and clicking OK The tracked changes will be listed on their own worksheet, called History. A summary of all changes on their own worksheet To remove the History worksheet from your workbook, you can either save your workbook again or uncheck the box next to List changes on a new sheet in the Highlight Changes dialog box.
Which version of Office is right for me?
If you're still not sure which version of Office you want, take some time to think about the features that are most important to you and how they fit into your budget. Below are some questions you may want to ask yourself. Do I just need Word, Excel, and PowerPoint, or do I need the entire Office suite? If you only need the core Office applications, it may be best to buy Office Home & Student. While the initial price is more expensive, it may be the cheapest option over the long term if you plan to use this version for more than a few years. However, if you need other Office programs like Outlook or Access, you'll need to purchase either Office Home & Business, Office Professional, or a subscription to Office 365. Do I want to install Office on more than one computer? If your household has several computers, you may want to get an Office 365 Home subscription. Office 365 Personal and the nonsubscription versions can only be installed on one computer. Will I do a lot of editing on the go? If you want to edit Office documents on the go, Office 365 may be your best option because it will give you access to advanced features for free Office mobile apps. However, keep in mind that you can do basic editing within the mobile apps or in a web browser with Office Online, even if you don't own a copy of Office. Student and business pricing Microsoft offers discounts for the Office 2016 suite to college students and businesses. Check out Office 365 University and Office 365 for Business to learn more. How to buy Office 2016 Office 2016 and Office 365 are available directly from the Microsoft Store, as well as through retailers like Amazon. Office 365 is also available for a free trial, which can help you decide if it's right for your needs. For more help making a decision, you can always ask an associate at your local Microsoft Store for advice. Alternatively, you can call 1-800-MY-MS-STORE for more information on the product you're interested in. Office for other devices Office for Mac Office 2016 for Mac is the newest version of the Office suite for Mac computers, and it's a big upgrade from previous versions, including Office for Mac 2011 and Office for Mac 2008. While Office 2016 for Mac is more similar to the Windows version than previous versions of Office for Mac, keep in mind that it doesn't include all of the features you'll find in the Windows version. Office for mobile devices If you have a Microsoft account, you can use Office mobile apps to create and edit Office documents from various devices, including iOS, Android, and Windows Phone. The basic editing features are available for free, but you'll need an Office 365 subscription to use more advanced features like real-time co-authoring. Keep in mind that none of the Office mobile apps include all of the features found in the desktop version. They will likely be most helpful for making minor edits when you're on the go.
To add a filter:
In the example below, we'll filter out certain salespeople to determine how their individual sales are impacting each region. Drag a field from the Field List to the Filters area. In this example, we'll use the Salesperson field. dragging a field The filter will appear above the PivotTable. Click the drop-down arrow, then check the box next to Select Multiple Items. enabling select multiple items Uncheck the box next to any item you don't want to include in the PivotTable. In our example, we'll uncheck the boxes for a few salespeople, then click OK. unchecking/deselecting items The PivotTable will adjust to reflect the changes. filtered pivottable Slicers Slicers make filtering data in PivotTables even easier. Slicers are basically just filters but are easier and faster to use, allowing you to instantly pivot your data. If you frequently filter your PivotTables, you may want to consider using slicers instead of filters.
To use Goal Seek (example 1):
Let's say you're enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class. In the image below, you can see that the grades on the first four assignments are 58, 70, 72, and 60. Even though we don't know what the fifth grade will be, we can write a formula—or function—that calculates the final grade. In this case, each assignment is weighted equally, so all we have to do is average all five grades by typing =AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade we'll need to make on that assignment. the average function being used to average the other class grades Select the cell with the value you want to change. Whenever you use Goal Seek, you'll need to select a cell that already contains a formula or function. In our example, we'll select cell B7 because it contains the formula =AVERAGE(B2:B6). selecting cell B7 From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu. clicking the Goal Seek option from the What-if Analysis command drop-down menu A dialog box will appear with three fields. The first field, Set cell:, will contain the desired result. In our example, cell B7 is already selected. The second field, To value:, is the desired result. In our example, we'll enter 70 because we need to earn at least that to pass the class. The third field, By changing cell:, is the cell where Goal Seek will place its answer. In our example, we'll select cell B6 because we want to determine the grade we need to earn on the final assignment. When you're done, click OK. setting parameters in the goal seek dialog box The dialog box will tell you if Goal Seek was able to find a solution. Click OK. a successful result in the goal seek dialog box The result will appear in the specified cell. In our example, Goal Seek calculated that we will need to score at least a 90 on the final assignment to earn a passing grade. the value in cell B6 (90) determined by goal To use Goal Seek (example 2): Let's say you're planning an event and want to invite as many people as you can without exceeding a budget of $500. We can use Goal Seek to figure out how many people to invite. In our example below, cell B5 contains the formula =B2+B3*B4 to calculate the total cost of a room reservation, plus the cost per person. Select the cell with the value you want to change. In our example, we'll select cell B5. selecting cell B5 From the Data tab, click the What-If Analysis command, then select Goal Seek from the drop-down menu. clicking the Goal Seek option from the What-if Analysis command drop-down menu A dialog box will appear with three fields. The first field, Set cell:, will contain the desired result. In our example, cell B5 is already selected. The second field, To value:, is the desired result. In our example, we'll enter 500 because we only want to spend $500. The third field, By changing cell:, is the cell where Goal Seek will place its answer. In our example, we'll select cell B4 because we want to know how many guests we can invite without spending more than $500. When you're done, click OK. setting parameters in the goal seek dialog box The dialog box will tell you if Goal Seek was able to find a solution. Click OK. The result will appear in the specified cell. In our example, Goal Seek calculated the answer to be approximately 18.62. In this case, our final answer needs to be a whole number, so we'll need to round the answer up or down. Because rounding up would cause us to exceed our budget, we'll round down to 18 guests.
To add a slicer:
Select any cell in the PivotTable. From the Analyze tab, click the Insert Slicer command. insert slicer command A dialog box will appear. Check the box next to the desired field. In our example, we'll select Salesperson, then click OK. field dialog box The slicer will appear next to the PivotTable. Each selected item will be highlighted in blue. In the example below, the slicer contains all eight salespeople, but only five of them are currently selected. slicer and pivottable Just like filters, only selected items are used in the PivotTable. When you select or deselect an item, the PivotTable will instantly reflect the change. Try selecting different items to see how they affect the PivotTable. Press and hold the Ctrl key on your keyboard to select multiple items at once. adjusting the pivottable You can also click the Filter icon in the top-right corner of the slicer to select all items at once. PivotCharts PivotCharts are like regular charts, except they display data from a PivotTable. Just like regular charts, you'll be able to select a chart type, layout, and style that will best represent the data.
To create a PivotChart:
Select any cell in your PivotTable. From the Insert tab, click the PivotChart command. pivotchart command The Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK. chart dialog box The PivotChart will appear.
To add a comment:
Select the cell where you want the comment to appear. In our example, we'll select cell D17. Selecting cell E8 From the Review tab, click the New Comment command. Clicking the New Comment command A comment box will appear. Type your comment, then click anywhere outside the box to close the comment. Adding a comment The comment will be added to the cell, represented by the red triangle in the top-right corner. Comment indicator Select the cell again to view the comment. Selecting a cell to view a comment To edit a comment: Select the cell containing the comment you want to edit. From the Review tab, click the Edit Comment command. Clicking the Edit Comment command The comment box will appear. Edit the comment as desired, then click anywhere outside the box to close the comment. Editing a comment To show or hide comments: From the Review tab, click the Show All Comments command to view every comment in your worksheet at the same time. Clicking the Show All Comments command All comments in the worksheet will appear. Click the Show All Comments command again to hide them. Viewing all comments at the same time You can also choose to show and hide individual comments by selecting the desired cell and clicking the Show/Hide Comment command. Showing and hiding individual comments To delete a comment: Select the cell containing the comment you want to delete. In our example, we'll select cell E13. Selecting cell E8 From the Review tab, click the Delete command in the Comments group. Clicking the Delete command The comment will be deleted. After deleting the comment
To create a PivotTable:
Select the table or cells (including column headers) you want to include in your PivotTable. selecting a table From the Insert tab, click the PivotTable command. pivottable command The Create PivotTable dialog box will appear. Choose your settings, then click OK. In our example, we'll use Table1 as our source data and place the PivotTable on a new worksheet. pivottable dialog box A blank PivotTable and Field List will appear on a new worksheet. blank pivottable Once you create a PivotTable, you'll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Field List, check the box for each field you want to add. In our example, we want to know the total amount sold by each salesperson, so we'll check the Salesperson and Order Amount fields. selecting fields The selected fields will be added to one of the four areas below. In our example, the Salesperson field has been added to the Rows area, while Order Amount has been added to Values. Alternatively, you can drag and drop fields directly into the desired area. fields added to areas The PivotTable will calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson. finished pivottable Just like with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter command on the Home tab. You can also apply any type of number formatting you want. For example, you may want to change the number format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable. sorted and formatted If you change any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and then go to AnalyzeRefresh. Pivoting data One of the best things about PivotTables is that they can quickly pivot—or reorganize—your data, allowing you to examine your worksheet in several ways. Pivoting data can help you answer different questions and even experiment with your data to discover new trends and patterns.
To add columns:
So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you'll need to add a field to the Columns area. Drag a field from the Field List into the Columns area. In our example, we'll use the Month field. adding a column The PivotTable will include multiple columns. In our example, there is now a column for each person's monthly sales, in addition to the grand total. finished pivottable To change a row or column: Changing a row or column can give you a completely different perspective on your data. All you have to do is remove the field in question, then replace it with another. Drag the field you want to remove out of its current area. You can also uncheck the appropriate box in the Field List. In this example, we've removed the Month and Salesperson fields. removing fields Drag a new field into the desired area. In our example, we'll place the Region field under Rows. dragging a field The PivotTable will adjust—or pivot—to show the new data. In our example, it now shows the amount sold by each region.
Comments
Sometimes you may want to add a comment to provide feedback instead of editing the contents of a cell. While often used in combination with Track Changes, you don't necessarily need to have Track Changes turned on to use comments.
Reviewing changes
Tracked changes are really just suggested changes. To become permanent, the changes must be accepted. On the other hand, the original author may disagree with some of the tracked changes and choose to reject them.
Understanding Track Changes
When you turn on the Track Changes feature, every cell you edit will be highlighted with a unique border and indicator. Selecting a marked cell will show the details of the change. This allows you and other reviewers to see what's been changed before accepting the revisions permanently. In the image below, each edited cell has a blue border and a small triangle in the upper-left corner. Changes in an Excel worksheet There are some changes Excel cannot track. Before using this feature, you may want to review Microsoft's list of changes that Excel does not track or highlight. You cannot use Track Changes if your workbook includes tables. To remove a table, select it, click the Design tab, then click Convert to Range.
Goal Seek
Whenever you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way: It lets you start with the desired result, and it calculates the input value that will give you that result. We'll use a few examples to show how to use Goal Seek.
Document Inspector
Whenever you create or edit a workbook, certain personal information may be added to the file automatically. You can use Document Inspector to remove this information before sharing a workbook with others. Because some changes may be permanent, it's a good idea to save an additional copy of your workbook before using the Document Inspector to remove information.