| | Level | Topic | Video Links | Lesson Number | I have completed the tab work for this Lesson: [Insert Name] | Tab Number | Points Possible | Points Earned | Sections | Description |
| | New User | Getting Started | Create your first Excel 2013 workbook: | 1 | Insert Name | 1 | 2 | | Start using Excel (4:24) | The best way to learn Excel 2013 is to start using it. | |
| | | | | | | | | | Save and print an Excel workbook (1:48) | Learn how to save your workbook to prevent losing all your hard work, and how to print your workbook. | |
| | | | | | | | | | Insert columns and rows (4:12) | Expand your workbook by adding columns and rows, and then adding data to them. | |
| | | | | | | | | | Add formulas and references (2:50) | See how Excel calculates formulas for your tables and uses cell references to automatically update totals when you change values in a table. | |
| | | | | | | | | | Advanced formulas and references (4:19) | This video shows you how to write a basic formula and demonstrates a couple important concepts. | |
| | | | | | - | - | - | - | What's new in Excel 2013 | The first thing you’ll see when you open Excel is a brand new look. It’s cleaner, but it’s also designed to help you get professional-looking results quickly. You’ll find many new features that let you get away from walls of numbers and draw more persuasive pictures of your data, guiding you to better, more informed decisions. | |
| | | | | | | | | | Basic tasks in Excel 2013 | Excel is an incredibly powerful tool for getting meaning out of vast amounts of data. But it also works really well for simple calculations and tracking almost any kind of information. The key for unlocking all that potential is the grid of cells. Cells can contain numbers, text or formulas. You put data in your cells and group them in rows and columns. That allows you to add up your data, sort and filter it, put it in tables, and build great-looking charts. Let’s go through the basic steps to get you started. | |
| | | | | | | | | | Course summary: Create your first Excel 2013 workbook | A brief reminder of the key points in this course. | |
| | Beginner | Getting Started | Make the switch to Excel 2013 | 2 | Insert Name | 2 | 2 | | Get going fast (1:57) | This video shows you the top 5 things that have changed in Excel 2013. | |
| | | | | | | | | | A first look at Excel 2013 (3:30) | In this video, take an in-depth look at Excel 2013: from starting it up, to saving your file. Also learn how to convert your .xls file to an .xlsx file. | |
| | | | | | | | | | How things are organized (1:58) | This video shows you the three major places to find things: in the workbook, on the ribbon, and in the Backstage. | |
| | | | | | | | | | A closer look at the ribbon (4:03) | Take a tour of the ribbon and learn where all your favorite buttons and menus are in Excel 2013. | |
| | | | | | | | | | Save, publish, and share (2:39) | Save your Excel file either to your computer, or to an online service like OneDrive. Then find out how to share it with others, even if they have an older version. | |
| | | | | | - | - | - | - | Course summary: Make the switch to Excel 2013 | A brief reminder of the key points in this course. | |
| | Beginner | Basic Forumulas | Add numbers in Excel 2013 | 3 | Insert Name | 3 | 2 | | Add numbers in Excel 2013 (3:03) | You can use Excel to add numbers using formulas, buttons, and functions. | |
| | | | | | Insert Name | 4 | 2 | | The SUM function (4:48) | When adding a few cells, the SUM function will save you time. With larger ranges of cells, it’s essential. | |
| | | | | | Insert Name | 5 | 2 | | The SUMIF function (5:37) | The SUMIF function allows you add cells that meet a criteria that you provide. | |
| | | | | | - | - | - | - | Add numbers | Suppose you want to calculate a price total for the inventory of a store or the total gross profit margins for all departments that are under budget for the year. There are several ways to add numbers. | |
| | | | | | | | | | Subtract numbers | Let's say you want to find out how many inventory items are not profitable (total inventory minus profitable items) or how many employees are approaching retirement age (total employees minus employees under 55). Excel gives you several ways to subtract numbers. | |
| | | | | | | | | | Multiply numbers | Let's say you want to figure out how much bottled water that you need for a customer conference (total attendees × 4 days × 3 bottles per day) or the reimbursement travel cost for a business trip (total miles × 0.46). There are several ways to multiply numbers. | |
| | | | | | | | | | Divide cells or numbers | Let's say you want to find out how many person hours it took to finish a project (total project hours ÷ total people on project) or the actual miles per gallon rate for your recent cross-country trip (total miles ÷ total gallons). Excel gives you several ways to divide numbers. | |
| | | | | | | | | | Sum numbers by creating a formula | Let Excel be your calculator. With a simple formula Excel can sum numbers automatically. | |
| | | | | | | | | | SUMIF function | This article describes the formula syntax and usage of the SUMIF function in Microsoft Excel. | |
| | | | | | | | | | Course summary: Add numbers in Excel 2013 | A brief reminder of the key points in this course. | |
| | Beginner | Basic Charts | Create a Chart | 4 | Insert Name | 6 | 3 | | Create a chart (1:04) | Create a chart (graph) that's recommended for your data, almost as fast as using the chart wizard that's no longer available. | |
| | | | | | | | | | Create pie, bar, and line charts (3:35) | In this video, see how to create pie, bar, and line charts, depending on what type of data you start with. | |
| | | | | | Insert Name | 7 | 2 | | Customize charts (5:36) | After you create your chart, you can customize it to show additional chart elements, such as titles and data labels, or to make it look exactly the way you want. | |
| | | | | | Insert Name | 8 | 2 | | Create a combo chart (1:51) | Change the chart type of one or more data series in your chart (graph) and add a secondary vertical (value) axis in the combo chart. | |
| | | | | | Insert Name | 9 | 2 | | Copy a chart (3:17) | To copy a chart to another worksheet in the same or a different workbook, right-click the chart and click Copy. | |
| | | | | | - | - | - | - | Course summary: Create a chart | A brief reminder of the key points in this course. | |
| | | | | | | | | | Excel 2013 Tutorial for Beginners - Basic Graph (3:28) | Alternate (Not Microsoft) Chart Tutorial plus many more | |
| | Beginner | Basic Math | Basic math in Excel | 5 | insert Name | 10 | 1 | | Basic math in Excel 2013 (2:35) | Let Excel be your calculator. This video shows you how to add, subtract, multiply, and divide in your workbook. | |
| | | | | | insert Name | 11 | 1 | | Using functions in Excel 2013 (5:19) | A function is a predefined formula that helps save you time. For example, use the SUM function to add lots of numbers or cells, and the PRODUCT function to multiply them. | |
| | | | | | insert Name | 12 | 1 | | Operator order in Excel 2013 (2:12) | How do you know what part of your formula Excel acts on first, second, third, and so on? This video explains it. | |
| | | | | | insert Name | 13 | 1 | | More complex formulas in Excel 2013 (4:28) | Watch this video to learn how to create more complex formulas using multiple operators, cell references, and functions. | |
| | | | | | - | - | - | - | Use AutoSum to sum numbers | If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on theHome tab, press Enter, and you’re done. | |
| | | | | | | | | | Sum numbers by creating a formula | Let Excel be your calculator. With a simple formula Excel can sum numbers automatically. | |
| | | | | | | | | | Course summary: Basic math in Excel 2013 | A brief reminder of the key points in this course. | |
| | Beginner | Basic Controls | Freeze or lock panes | 6 | insert Name | 14 | 1 | | Freeze or lock panes (1:23) | You want the header row to remain visible when you scroll, to show what’s in each column. To do this, click Click View > Freeze Panes > Freeze Top Row. If, instead, you want column A to always remain visible, click View > Freeze Panes > Freeze First Column. To unfreeze the panes, click Freeze Panes > Unfreeze Panes. | |
| | | | | | insert Name | 15 | 2 | | Freeze panes in detail (4:00) | In this video, we’ll cover freeze panes in detail, including freezing the top row and left column at the same time, multiple rows and columns, and printing the header on every page. | |
| | | | | | - | - | - | - | Freeze rows and columns | To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can either freeze specific rows or columns in one area to lock them in place, or create multiple worksheet areas that can scroll separately from each other by freezing panes. | |
| | | | | | | | | | Freeze panes to lock the first row or column | You want to scroll down and see your rows of data, but when you get to the bottom of the screen, your column names in the top row have disappeared. To fix this, you freeze the top row so that it's always visible. Good news – the command is easy to get to. Click View > Freeze Panes > Freeze Top Row. | |
| | | | | | | | | | Video: Freeze panes in detail | In this video, we’ll cover freeze panes in detail, including freezing the top row and left column at the same time, multiple rows and columns, and printing the header on every page. | |
| | | | | | | | | | Video: Freeze or lock panes | You want the header row to remain visible when you scroll, to show what’s in each column. To do this, click Click View > Freeze Panes > Freeze Top Row. If, instead, you want column A to always remain visible, click View > Freeze Panes > Freeze First Column. To unfreeze the panes, click Freeze Panes > Unfreeze Panes. | |
| | | | | | | | | | Course summary: Freeze or lock panes | A brief reminder of the key points in this course. | |
| | Beginner | Basic Forumulas | Understand and use cell references | 7 | insert Name | 16 | 2 | | Cell references (1:44) | Watch this video to learn the basics. When you use cell references in a formula, Excel calculates the answer using the numbers in the referenced cells. When you change the value in a cell, the formula calculates the new result automatically. | |
| | | | | | insert Name | 17 | 2 | | Copying formulas (3:13) | We just saw how to create a formula that adds two cells. Now let’s copy the formula down the column so it adds the other pairs of cells. | |
| | | | | | - | - | - | - | Create or change a cell reference | A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. | |
| | | | | | | | | | Use cell references in a formula | When you create a simple formula or a formula by that uses a function, you can refer to data in worksheet cells by including cell references in the formula arguments. For example, when you enter or select the cell reference A2, the formula uses the value of that cell to calculate the result. You can also reference a range of cells. | |
| | | | | | | | | | Create a reference to the same cell range on multiple worksheets | A reference that refers to the same cell or range on multiple sheets is called a 3-D reference. A 3-D reference is a useful and convenient way to reference several worksheets that follow the same pattern and contain the same type of data, such as when you consolidate budget data from different departments in your organization. | |
| | | | | | | | | | Course summary: Understand and use cell references | A brief reminder of the key points in this course. | |
| | Beginner | Basic Controls | Use AutoFill and Flash Fill | 8 | insert Name | 18 | 1 | | AutoFill and Flash Fill (1:47) | Watch this video for a quick introduction to AutoFill and Flash Fill, two helpful time savers that we’ll cover in more detail in this course. | |
| | | | | | insert Name | 19 | 2 | | AutoFill (3:01) | Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells. | |
| | | | | | insert Name | 20 | 2 | | Flash Fill (3:10) | Use Flash Fill, new in Excel 2013, to fill out data based on an example. Flash Fill typically starts working when it recognizes a pattern in your data, and works best when your data has some consistency. | |
| | | | | | - | - | - | - | Fill data automatically in worksheet cells | Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with data that follows a pattern or that is based on data in other cells. | |
| | | | | | | | | | Split a column of data based on what you type | You need to separate first and last names (or part names and numbers, or any other data) into separate columns. A new Excel 2013 feature called Flash Fill can help. | |
| | | | | | | | | | Change the case of text | If you need to quickly change the text case to uppercase, lowercase, or proper (normal) case, use Flash Fill instead of retyping all the text. | |
| | | | | | | | | | Course summary: Use AutoFill and Flash Fill | A brief reminder of the key points in this course. | |
| | | | | | | Points Possible --> | 35 | 0 | <-- Points Earned |
| | Intermediate | Time | Add or subtract time | 9 | Insert Name | 21 | 5 | | Add time (3:33) | The best way to add time is to use the AutoSum button on the Home tab. You can also add a unit of time, such as 1½ hours, to a time of day, such as 10:00 a.m., by using the TIME function. | |
| | | | | | | | | | Subtract time (4:06) | You can subtract time in Excel almost like you subtract other types of numbers. To do this, you use a formula; there isn’t an AutoSubtract button. Like with adding time, you can also subtract a unit of time, such as 1½ hours, from a time of day, such as 10:00 a.m., by using the TIME function. | |
| | | | | | - | - | - | - | Add or subtract time | Not many people can easily add two different times together to get a total, or subtract one time from another to get the total time spent working on a project. Fortunately, Excel can easily add or subtract time, using simple formulas. | |
| | | | | | | | | | Create or delete a custom number format | Excel provides many options for displaying numbers as percentages, currency, dates, and so on. If these built-in formats do not meet your needs, you can customize a built-in number format to create your own. To learn more about how to change number format codes, you may want to review the guidelines for customizing a number format before you get started. | |
| | | | | | | | | | TIME function | This article describes the formula syntax and usage of the TIME function in Microsoft Excel. | |
| | | | | | | | | | Course summary: Add or subtract time | A brief reminder of the key points in this course. | |
| | Intermediate | Formulas | Average a group of numbers | 10 | Insert Name | 22 | 5 | | Average a group of numbers (2:14) | You may have used AutoSum to quickly add numbers in Excel. But did you know you can also use it to calculate other results, such as averages? | |
| | | | | | insert Name | 23 | 5 | | AVERAGEIF function (3:14) | The AVERAGEIF function returns the average of cells in a range that meet criteria you provide. | |
| | | | | | insert Name | 24 | 5 | | Weighted average (2:26) | Usually when you calculate an average, all of the numbers are given equal significance; the numbers are added together and then divided by the number of numbers. With a weighted average, one or more numbers is given a greater significance, or weight. | |
| | | | | | - | - | - | - | Find the average of a group of numbers in Excel for Windows | When you need to find an average, you can click a button, or use a function in a formula. | |
| | | | | | | | | | AVERAGE function | This article describes the formula syntax and usage of the AVERAGE function in Microsoft Excel. | |
| | | | | | | | | | AVERAGEIF function | This article describes the formula syntax and usage of the AVERAGEIF function in Microsoft Excel. | |
| | | | | | | | | | Course summary: Average a group of numbers | A brief reminder of the key points in this course. | |
| | intermediate | Page Formats | Video: Insert headers and footers | 11 | Insert Name | 25 | 5 | | Insert headers and footers (2:10) | Learn what headers and footers are, where they're located, and how to print them in Excel. | |
| | | | | | | | | | Page numbers in depth (3:34) | Adding page numbers to a worksheet is one of the most common uses of headers and footers. Watch this video to see how. | |
| | | | | | | | | | Header and footer details (4:02) | To save time, add headers and footers to several worksheets at the same time, make them different on the first page. And format, move, or delete them. | |
| | | | | | - | - | - | - | Headers and footers in a worksheet | You can add headers or footers at the top or bottom of a printed worksheet in Microsoft Excel. For example, you might create a footer that has page numbers, the date, and the name of your file. You can create your own or use many built-in headers and footers. | |
| | | | | | | | | | Remove a header or footer from a worksheet | Text and images in headers print at the top of every page of your worksheet. Similarly, text and images in footers print at the bottom of every page. If you want to remove some of the information in your header or footer, here’s how: | |
| | | | | | | | | | Add a header to a worksheet | You can add information to a header so it’ll print at the top of every page. For example, you can create a header that contains page numbers to help your readers find pages in a printed worksheet . | |
| | | | | | | | | | Video: Insert headers and footers | Learn what headers and footers are, where they're located, and how to print them in Excel. | |
| | | | | | | | | | Course summary: Insert headers and footers | A brief reminder of the key points in this course. | |
| | Intermediate | Sorting | Sort and filter data | 12 | insert Name | 26 | 5 | | Sort and filter data (1:54) | Sort data in Excel quickly, in just a few clicks. To change the order of your data, sort it. To focus on a specific set of data, filter a range of cells or a table. | |
| | | | | | | | | | Sort details (5:14) | We sorted numbers in the previous video. You can also sort text and dates, either alphabetically, by format (such as color), or by a custom list you create. | |
| | | | | | insert Name | 27 | 5 | | AutoFilter details (3:55) | Filter for blank cells, non-blank cells, use criteria to filter, filter by cell or font color, just to name a few. Watch this video for more details. | |
| | | | | | | | | | Advanced filter details (4:29) | You can use the Advanced Filter to create more powerful filters, such as filtering for unique records and using operators such as OR. | |
| | | | | | insert Name | 28 | 5 | | Sort by dates | When dates in a worksheet aren't in the order you want, you can sort them. For example, you can sort them from oldest on top to newest on top. | |
| | | | | | insert Name | 29 | 5 | | Sort data using a custom list | With built-in custom lists, you can sort data by days of the week or months of the year. Or create your own custom lists to sort by any other characteristic that doesn’t sort well alphabetically, such as high, medium, and low or S, M, L, XL. | |
| | | | | | - | - | - | - | Filter a range of data | Built-in comparison operators such as “greater than” and “top 10” can show the data you want and hide the rest. That’s a big help with large worksheets or when you just want to focus on particular data ranges. | |
| | | | | | | | | | Course summary: Sort and filter data | A brief reminder of the key points in this course. | |
| | intermediate | Data Formatting | Use conditional formatting | 13 | insert Name | 30 | 5 | | Use conditional formatting (2:03) | Conditional formatting provides visual cues to help you quickly make sense of your data. For example, it’ll clearly show highs and lows, or other data trends based on criteria you provide. | |
| | | | | | | | | | Conditionally format dates (2:44) | Say you want to see, at a glance, what tasks in a list are late. In other words, those tasks with due date dates before today. Or which tasks aren’t due for a week or more. Watch this video to learn how. | |
| | | | | | insert Name | 31 | 5 | | Conditionally format text (2:12) | Use the Quick Analysis tool to conditionally format cells in a range that have duplicate text, unique text, and text that’s the same as text you specify. You can even conditionally format a row based on the text in one of the cells in the row. | |
| | | | | | insert Name | 32 | 5 | | Copy and remove conditional formatting (1:22) | This video shows you how to quickly copy a cell’s formatting, remove conditional formatting from specific cells, and remove all conditional formatting from an entire worksheet. | |
| | | | | | - | - | - | - | Apply conditional formatting to quickly analyze data | It can be hard to get a lot of meaning out of numbers in a worksheet. But Excel provides a bunch of ways to quickly analyze your data using conditional formatting. For example, you can use a color scale to differentiate high, medium, and low temperature values. | |
| | | | | | | | | | Add, change, find, or clear conditional formats | Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. | |
| | | | | | | | | | Change, find, or clear conditional formats dialog box options | Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. | |
| | | | | | | | | | Manage conditional formatting rule precedence | When you create more than one conditional formatting rule for a range of cells, it helps to understand in what order these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how to change the order in which rules are evaluated, and when to stop rule evaluation. | |
| | | | | | | | | | Course summary: Use conditional formatting | A brief reminder of the key points in this course. | |
| | intermediate | Data Formatting | Take conditional formatting to the next level | 14 | insert Name | 33 | 5 | | Take conditional formatting to the next level (3:38) | Watch this video to learn how to apply conditional formatting to specific cells, tables, PivotTables, and worksheets. You can also format cells that are blank or contain errors. | |
| | | | | | | | | | Use formulas to apply conditional formatting (4:15) | To control more precisely what cells will be formatted, you can use formulas to apply conditional formatting. | |
| | | | | | | | | | Manage conditional formatting (4:37) | In the Conditional Formatting Rules Manager you can choose to show formatting rules for areas of a workbook that have rules, such as the current selection or a specific worksheet. You can then create, edit, and delete rules as well as manage the precedence of rules for the cells or worksheet you selected. | |
| | | | | | - | - | - | - | Create conditional formulas to find data or apply formatting | When Excel's built-in conditional formatting rules don’t meet your needs, you can use a formula to get the results you want. For example, you can use a conditional formula to find blank cells and turn them red so you can see them easily. | |
| | | | | | | | | | Course summary: Take conditional formatting to the next level | A brief reminder of the key points in this course. | |
| | Intermediate | Data Analysis | Misc Topics | 15 | insert Name | 34 | 5 | | Analyze your data instantly | It used to take a bit of work to analyze your data, but now it only takes a few steps. You can instantly create different types of charts, including line and column charts, or add miniature graphs (called sparklines). You can also apply a table style, create PivotTables, quickly insert totals, and apply conditional formatting. | |
| | | | | | | | | | Analyze trends in data using sparklines | Sparklines are tiny charts inside single worksheet cells that can be used to visually represent and show a trend in your data. Sparklines can draw attention to important items such as seasonal changes or economic cycles and highlight the maximum and minimum values in a different color. Showing trends in your worksheet data can be useful, especially when you’re sharing your data with other people. | |
| | Intermediate | Lookups | VLOOKUP: When and how to use it | 16 | insert Name | 35 | 5 | | VLOOKUP: When and how to use it (2:37) | The basics of using VLOOKUP. | |
| | | | | | | | | | The nuts and bolts of VLOOKUP (3:04) | An explanation of each of the function's arguments. | |
| | | | | | insert Name | 36 | 5 | | Look up values on a different worksheet (2:37) | How to find values on another worksheet. | |
| | | | | | insert Name | 37 | 5 | | Copy a VLOOKUP formula (3:30) | How to use absolute cell references to copy a formula down a column. | |
| | | | | | - | - | - | - | VLOOKUP function | Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row. For example, look up an employee's last name by her employee number, or find her phone number by looking up her last name (just like a telephone book). | |
| | | | | | | | | | Quick reference card: VLOOKUP refresher | VLOOKUP is one of the most popular, useful functions in Excel, but it’s hard to remember the formula if you don’t use it that often. | |
| | | | | | | | | | Quick reference card: VLOOKUP troubleshooting tips | If you use the VLOOKUP function to look up values in a list or table, it’s possible to end up with #N/A errors, particularly when you’re looking for exact matches. Although these errors are annoying, they occur for a good reason—either the value you’re trying to find doesn’t exist, or it does exist but Excel doesn’t perceive it as a match due to a formatting mismatch or other issue. | |
| | | | | | | | | | Lookup and reference functions (reference) | To get detailed information about a function, click its name in the first column. | |
| | | | | | | | | | Course summary: VLOOKUP: When and how to use it | A brief reminder of the key points in this course. | |
| | | | | | | Points Possible --> | 85 | 0 | <-- Points Earned |
| | Advanced | If Functions | Advanced IF functions | 17 | Insert Name | 38 | 5 | | Advanced IF functions (3:23) | Before we get started with advanced IF functions, let's do a quick IF function refresher first. Watch this video for some examples. | |
| | | | | | | | | | Nested IF functions (3:31) | Nested IF functions, meaning one IF function inside of another, allow you to test multiple criteria and increases the number of possible outcomes. | |
| | | | | | insert Name | 39 | 5 | | IF with AND and OR (3:35) | Watch this video to see how to extend the functionality of the IF function by nesting AND and OR functions. | |
| | | | | | insert Name | 40 | 5 | | COUNTIFS and SUMIFS (5:31) | COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. SUMIFS adds the cells in a range that meet multiple criteria. | |
| | | | | | insert Name | 41 | 5 | | AVERAGEIFS and IFERROR (4:51) | AVERAGEIFS returns the average of all cells that meet multiple criteria. IFERROR returns the value specified if AVERAGEIFS evaluates to an error. | |
| | | | | | - | - | - | - | Nest a function within a function | When you create a formula by using a function, you can nest functions inside other functions. When you do, you're using the results of the nested functions as arguments— pieces of data the first function needs to run properly. You can always tell when a function is nested because it's inside parentheses. | |
| | | | | | | | | | IF function | Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. | |
| | | | | | | | | | SUMIFS function | The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value. | |
| | | | | | | | | | COUNTIFS function | This article describes the formula syntax and usage of the COUNTIFS function in Microsoft Excel. | |
| | | | | | | | | | AVERAGEIFS function | This article describes the formula syntax and usage of the AVERAGEIFS function in Microsoft Excel. | |
| | | | | | | | | | IFERROR function | This article describes the formula syntax and usage of the IFERROR function in Microsoft Excel. | |
| | | | | | | | | | Course summary: Advanced IF functions | A brief reminder of the key points in this course. | |
| | Advanced | Arrays | Array formulas | 18 | insert Name | 42 | 5 | | Array formulas (2:57) | Create array formulas, often called Ctrl Shift Enter or CSE formulas, to perform calculations that generate single or multiple results. Watch this video to learn more. | |
| | | | | | | | | | Use SUM, AVERAGE, and MAX in array formulas (4:24) | An array formula can also return multiple values. Calculate totals, averages, and maximum values using the SUM, AVERAGE, and MAX functions. | |
| | | | | | | | | | Edit and delete array formulas (2:25) | Editing an array formula requires special steps, and this video shows you what they are. You'll also see how to delete array formulas. | |
| | | | | | - | - | - | - | Guidelines and examples of array formulas | Turning some of the formulas in your worksheet into array formulas can make your worksheet more powerful and simplify it at the same time. A formula used as an array formula can do calculations on rows and columns of cells where you might otherwise need to use several formulas. | |
| | | | | | | | | | Create an array formula | Also referred to as Ctrl Shift Enter or CSE formulas because you need to press Ctrl+Shift+Enter to enter them in your worksheet, array formulas are very powerful formulas that enable you to perform calculations you can’t do with standard worksheet functions. | |
| | | | | | | | | | Rules for changing array formulas | You're trying to edit an array formula, and you're stopped in your tracks. You click the formula in the cell or formula bar and you can't change a thing. | |
| | | | | | | | | | Course summary: Array formulas | A brief reminder of the key points in this course. | |
| | Advanced | Drop-Down Lists | Create and manage drop-down lists | 19 | insert Name | 43 | 5 | | Drop-down lists (1:01) | Data entry is quicker and more accurate when you use a drop-down list to limit the entries people can make in a cell. When someone selects a cell, the drop-down list’s down-arrow appears, and they can click it and make a selection. | |
| | | | | | | | | | Drop-down list settings (4:37) | You can use a comma-delimited list, a cell range, or a named range to define the options in a drop-down list. This video focuses on how to use a cell range and named range. | |
| | | | | | | | | | Input and error messages (2:47) | To help people decide what drop-down list option to select, and even to let them know that a cell contains a drop-down list, you can create a message that appears when they select a cell. Or that shows an error message when a drop-down list option isn't selected properly. | |
| | | | | | | | | | Manage drop-down lists (6:20) | To prevent changes to your drop-down list data, you can hide the columns, rows, or the entire worksheet that contains the data. And unhide them if you need to make changes. You can also lock and password protect the cells on the worksheet or even the entire worksheet. | |
| | | | | | - | - | - | - | Create a drop-down list | Provide a more efficient worksheet by using drop-down lists in a cell where your customers can make a yes or no choice, pick a date from a calendar, or pick from another list you insert. Someone using your worksheet clicks an arrow, and then clicks an entry in the list. | |
| | | | | | | | | | Add or remove items from a drop-down list | After you create a drop-down list, you might want to add more items or delete items. How you do that depends on how the list was created. When you create a drop-down list, you connect it to a list of entries, which are used to populate your drop-down list. When you hook up those entries to your drop-down list, you can point to either a name that you’ve given the list (called a named range), or to the cells that contain your list of entries (called a range of cells). | |
| | | | | | | | | | Remove a drop-down list | If you no longer want a drop-down list in your worksheet, you can remove it. | |
| | | | | | | | | | Lock cells to protect them | Your boss wanted you to protect a workbook, but she also wanted to be able to change a few cells after you were done. So, before you password protected the workbook (or a worksheet), you unlocked some cells. Now your boss is done, so you can lock the cells. Here's how. | |
| | | | | | | | | | Course summary: Working with drop-down lists | A brief reminder of the key points in this course. | |
| | Advanced | PivotTable | Create a PivotTable and analyze your data | 20 | insert Name | 44 | 5 | | Create a PivotTable and analyze your data (1:35) | Learn what a PivotTable and PivotCharts are and how you can use them to summarize and analyze data in Excel 2013. | |
| | | | | | | | | | Create a PivotTable report manually (5:36) | Create a manual PivotTable if you prefer to design the PivotTable yourself. Here's how. | |
| | | | | | insert Name | 45 | 5 | | Sort, filter, summarize, and calculate your PivotTable data (4:07) | You can analyze PivotTable data in many ways, including sorting to quickly see trends. We’ll also look at how to filter, summarize and calculate your data. | |
| | | | | | insert Name | 46 | 5 | | Use slicers, timelines, and PivotCharts to analyze your PivotTable data (3:52) | Filter your PivotTable data with slicers and timelines, and see what filters are applied. PivotCharts visually show your PivotTable, making trends easier to see. | |
| | | | | | - | - | - | - | Create or delete an Excel table in a worksheet | When you create a table in an Excel worksheet, it's not only easier for you to manage and analyze its data, but you can use built-in filtering, sorting, and row shading. | |
| | | | | | | | | | Create a PivotTable to analyze external data | Being able to analyze all the data can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data that is stored outside of Excel, like in a Microsoft Access or Microsoft SQL Server database, or in an Online Analytical Processing (OLAP) cube file. In that case, you’ll connect to the external data source, and then create a PivotTable to summarize, analyze, explore, and present that data. | |
| | | | | | | | | | Create a PivotTable to analyze data in multiple tables | PivotTables are great for analyzing and reporting on your data. And when your data happens to be relational—meaning it's stored in separate tables you can bring together on common values—you can build a PivotTable like this in minutes: | |
| | | | | | | | | | Show different calculations in PivotTable value fields | Instead of writing your own formulas in calculated fields, you can use Show Values As to quickly present values in different ways. Show Values As is not a new feature but in Excel 2010, it’s much easier to find and use. It also provides several new calculation options, such as % of Parent Total or % Running Total In. | |
| | | | | | | | | | Create a PivotChart | It can be hard to see the big picture when you have data in a huge PivotTable or when you have a lot of complex worksheet data that includes text and numbers with column headings, like this: | |
| | | | | | | | | | Available chart types | When you create a chart in an Excel worksheet, a Word document, or a PowerPoint presentation, you have a lot of options. Whether you’ll use a chart that’s recommended for your data or one that you’ll pick from the list of all charts, it might help to know a little more about each type of chart. | |
| | | | | | | | | | Course summary: Create a PivotTable and analyze your data | A brief reminder of the key points in this course. | |
| | Advanced | Security | Password protect workbooks and worksheets | 21 | insert Name | 47 | 5 | | Password protect workbooks and worksheets (2:29) | Add a password to protect worksheets or your entire workbook and control whether others can open or make changes to them. | |
| | | | | | | | | | Password protect workbooks and worksheets in detail (6:07) | To help improve your privacy in Excel, a password adds protection to workbooks and worksheets to prevent others from changing, moving, or deleting important data. | |
| | | | | | - | - | - | - | Password protect worksheet or workbook elements | To help prevent someone from accidentally or deliberately changing, moving, or deleting important data, you can protect certain worksheet or workbook elements, with or without a password. You can remove the protection from a worksheet as needed. | |
| | | | | | | | | | Password protect a workbook | Excel gives you several ways to protect a workbook. You can require a password to open it, a password to change data, and a password for changing the file's structure—adding, deleting, or hiding worksheets. You can also set a password in Backstage view that encrypts your workbook. | |
| | | | | | | | | | Protect a worksheet with or without a password | To help protect your data from deliberate or unintentional changes, you can protect your worksheet, with or without a password. A password prevents other people from removing the worksheet protection—it needs to be entered to unprotect the sheet. | |
| | | | | | | | | | Course summary: Password protect workbooks and worksheets | A brief reminder of the key points in this course. | |
| | Advanced | Printing | Print worksheets and workbooks | 22 | insert Name | 48 | 5 | | Print worksheets and workbooks (1:09) | Print Excel worksheets and workbooks one at a time, or several at one time. You can also print a partial worksheet, such as an Excel table. | |
| | | | | | | | | | More print options (6:16) | Use print preview to change many default print settings and see what you’ll print. Also select a specific range of cells to print, and print a table in a worksheet. | |
| | | | | | | | | | Print a worksheet on a specific number of pages (4:40) | You want to print a worksheet on a set number of pages. There are several ways to do this without shrinking the worksheet, possibly making it hard to read. | |
| | | | | | | | | | Print headings, gridlines, formulas, and more (4:52) | Print gridlines and, for multiple page worksheets, print row or column headers or labels (also called print titles) in the first row or multiple rows on every page. | |
| | | | | | - | - | - | - | Print a worksheet or workbook | You can print Microsoft Excel worksheets and workbooks one at a time, or several at one time. You can also print a partial worksheet, such as an Excel table. | |
| | | | | | | | | | Course summary: Print worksheets and workbooks | A brief reminder of the key points in this course. | |
| | Advanced | Macros | Work with macros | 23 | insert Name | 49 | 5 | | Work with macros (5:08) | The best way to automate a repetitive task in Excel so that you can do the task again with a single click? Record a macro. | |
| | | | | | | | | | Edit a macro (3:29) | Watch this video to see how to edit a macro in a special text editor called the Visual Basic Editor. Visual Basic is the programming language that macros are recorded in. | |
| | | | | | | | | | Save your macro (3:09) | To make sure your macro is always available on your computer, save it to your Personal Macro Workbook, which is a special hidden workbook that loads every time you start Excel. | |
| | | | | | | | | | Assign a button to a macro (2:55) | In this final video, we’ll hook the macro up with a button and make it available in two places: In a custom group on the Developer tab, and on the Quick Access Toolbar. | |
| | | | | | - | - | - | - | Create or delete a macro | To automate a repetitive task so that you can do the task again with a single click, you can record a macro in Excel. You can also create a macro by using the Visual Basic Editor in Microsoft Visual Basic for Applications (VBA) to write your own macro programming code, or you can copy all or part of a macro to a new macro. After you create a macro, you can assign it to an object on your spreadsheet (such as a toolbar button, graphic, or control) so that you can run the macro by clicking the object. If you no longer use a macro, you can delete it. | |
| | | | | | | | | | Edit a macro | You use the Visual Basic Editor to edit the macros attached to a workbook. On the Developer tab, in the Code group, click Macros, and in the Macro name box, click the macro you want to edit. | |
| | | | | | | | | | Assign a macro to a button | After you create a macro, you can assign it to a button you click to run the macro. You can assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon. | |
| | | | | | | | | | Create and save all your macros in a single workbook | When you first create a macro in a workbook, it works only in that workbook. But what if you want to use the macro in other workbooks? To make your macros available every time you open Excel, create them in a workbook called Personal.xlsb. That’s a hidden workbook stored on your computer, which opens every time you open Excel. | |
| | | | | | | | | | Course summary: Work with macros | A brief reminder of the key points in this course. | |
| | Advanced | Data Model | Webinar: Understanding the Excel Data Model
| 22 | insert Name | 50 | 5 | | Webinar: Understanding the Excel Data Model (15:56) | The Excel Data Model is revolutionizing how you can work with data in Excel. In this webinar, guest host Owen Duncan teaches us how Excel Data Models provide lightning fast data analysis and visualizations. | |
| | | | | | | | | | Create a Data Model in Excel (how-to) | A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables, PivotCharts, and Power View reports. | |
| | | | | | | | | | Find out which data sources are used in a workbook data model (how-to) | As you work with a PivotTable and other data visualizations over time, you might lose track of which tables and data sources were added to the data model. | |
| | | | | | | | | | Tutorial: PivotTable data analysis using a Data Model in Excel 2013 (tutorial) | In less than an hour, you can build a PivotTable report in Excel that combines data from multiple tables. The first part of this tutorial steps you through data import and exploration. In the second half, you’ll use the Power Pivot add-in to refine the data model that lives behind the report, learning how to add calculations, hierarchies, and optimizations for Power View reporting. | |
| | Advanced | Power Pivot | Webinar: Understanding Power Pivot in Excel 2013 | 23 | insert Name | 51 | 5 | | Webinar: Understanding Power Pivot in Excel 2013 | Power Pivot lets you import massive amounts of data from different sources and mash it together to create useful reports. But perhaps you’ve been struggling with it. Or maybe you’d like some tips on how to best work with a data model. Owen Duncan, Senior Content Developer for Power Pivot, will show you the ropes. He’ll walk you through some basics and also teach you some best practices. | |
| | | | | | | | | | What is Power Pivot? (Microsoft Business Intelligence website) | Bring the power of data to every user in your organization with BI analytics | |
| | | | | | | | | | Starting the Power Pivot add in (how-to) | Power Pivot is an add-in you can use to perform powerful data analysis in Excel. The add-in is built into certain versions of Excel, but by default, it's not enabled. Here’s how you enable Power Pivot before using it for the first time. | |
| | | | | | | | | | The difference between data modeling in Excel versus Power Pivot (how-to) | Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily. | |
| | | | | | | | | | Calculations in Power Pivot (how-to) | DAX formulas in Power Pivot are very similar to Excel formulas. In fact, DAX uses many of the same functions, operators, and syntax as Excel formulas. However, DAX also has additional functions designed to work with relational data and perform more dynamic calculations. | |
| | | | | | | | | | Data Analysis Expressions (DAX) in Power Pivot (how-to) | Data Analysis Expressions (DAX) sounds a little intimidating at first, but don’t let the name fool you. DAX basics are really quite easy to understand. First things first - DAX is NOT a programming language. DAX is a formula language. You can use DAX to define custom calculations for Calculated Columns and for Measures (also known as calculated fields). DAX includes some of the functions used in Excel formulas, and additional functions designed to work with relational data and perform dynamic aggregation. | |
| | | | | | | | | | Hierarchies in Power Pivot (how-to) | One of the modifications you can make to a Data Model in the Power Pivot add-in is to add hierarchies. For example, if you have geographic data, you might want to create a hierarchy that starts with country, and drill down to region and city. | |
| | | | | | | | | | Time Intelligence in Power Pivot in Excel 2013 (how-to) | Data Analysis Expressions (DAX) has 35 functions specifically for aggregating and comparing data over time. Unlike DAX’s date and time functions, Time-intelligence functions don’t really have anything similar in Excel. This is because time-intelligence functions work with data that is constantly changing, depending on the context you select in PivotTables and Power View visualizations. | |
| | | | | | | | | | Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel (downloadable white paper) | Profit and Loss Data Modeling and Analysis with Microsoft PowerPivot in Excel | |
| | | | | | | Points Possible --> | 70 | 0 | <-- Points Earned |
| | | | | | | Total Points Possible --> | 190 | 0 | <-- Points Earned |
DTE
BA336UExcelAssignmentv1nameversion.xlsx
