Microsoft Excel 2013 Advanced. Online Excel Training Course

Explore basic Excel functions, sum, count, min, and max, across 12 monthly sheets, and learn to copy formulas with fill without formatting for efficient replication.

Learn to compute mean, mode, and median in Excel using the average, mode, and median functions. Discover how mean equals total divided by count and how mode and median operate.

Learn to use the sumif function to total sales by criteria across ranges. Apply criteria such as more than 25, or group by salesman, month, or fruit.

Apply the countif function for selective counting to count items using a single range, with criteria in quotes, across sales, salespeople, and categories, mirroring sumif but simpler.

Learn to use the Excel 2013 average if to compute the mean of a range based on criteria, with two ranges and quoted criteria, shown through sales and fruit examples.

Learn to apply multiple criteria with sumifs, countifs, and averageifs to analyze sales data by month and fruit, using absolute references to copy formulas across a table.

Learn to compute circumference, area, and volumes in Excel using pi, radius from diameter, and the product function for cylinder, rectangle, and cube shapes.

Master the Excel if function syntax and evaluation, returning true or false and triggering actions in both branches, with examples showing conditional text and error handling.

Explore nesting an if inside another to create multi-level decisions in Excel 2013. Apply nested if for age and score thresholds, use absolute references, and note vlookup as an alternative.

Explore using the and operator inside an if to test multiple criteria, such as three exams above 55%, to produce pass, merit, or distinction with absolute references.

Learn how to use the or operator inside an if statement in Excel, including embedding the or inside an and to determine pass, merit, or fail based on exam scores.

Explore how the not operator combines with and and or in an if function to test criteria and detect improvements in exam scores in Excel 2013 advanced.

Excel 2013 adds the formulatext function to display a cell's formula on the sheet, and combine it with isformula and if to handle non-formula cells and update dynamically.

Explore vlookup syntax and usage to perform data lookups in Excel, including lookup value, table array, first column of your lookup table, column index, and exact versus near matches.

master vlookup in live action to fill an order form from a stock table, retrieving unit costs, descriptions, and weights to calculate line totals and shipping.

Explore hlookup, a horizontal alternative to vlookup, define a named cost table, and pull weekly waiter, chef, bar staff, and manager totals by day using exact matches.

Learn to manage lookup tables in Excel by maintaining named ranges for VLOOKUPs and HLOOKUPs, extend ranges with extra rows, and insert above the bottom row to keep lookups accurate.

Create sparklines, mini graphs in a single cell to visualize a data series, such as sales, using line, column, or win/loss styles.

Alter sparkline designs by switching between line, column, and win-lose styles, then customize color schemes, markers, and highlights for high, low, first, and last points.

Learn how sparklines handle empty cells by connecting data points with a line, treating missing values as zero, or leaving gaps in column charts.

Learn to compare sparklines within a sparkline group by setting the vertical axis minimum and maximum to the same values, enabling cross-sparkline comparison.

Remove sparklines from a worksheet by using the sparkline tools design ribbon to clear selected sparklines or groups, or delete the entire column or row containing them.

Learn to work with time in Excel by entering times with colons, using am/pm or 24-hour formats, converting to decimals, and calculating durations and cross-day differences using an if statement.

Learn how Excel treats time as a decimal portion of a day and perform time calculations using sum, sumif, and averageif, with formatting to show hours, minutes, and seconds.

Explore essential time and date functions in Excel 2013 advanced, extracting hour, minute, and second values and reassembling them with time and date formulas to manipulate dates and times.

Explore how int and mod functions in Excel 2013 extract the whole number and remainder of division, and use absolute references when copying formulas to test even or odd values.

Generate random numbers in Excel 2013 using rand and randBetween. Convert to whole numbers with int, and map to days or months via VLOOKUP and named ranges on refresh.

Use the PMT function to calculate loan payments and savings toward a target sum, incorporating rate, nper, and present value and understanding compound interest.

Learn to automatically outline data in Excel 2013, group and hide rows and columns, use subtotals, and expand or collapse quarters and half-year totals.

Create and adjust outlines manually and with auto outline, grouping columns and rows to expand or collapse groups and reveal subtotals and grand totals.

Learn how to edit or remove outlines in Excel 2013, using the data ribbon to group, ungroup, or clear outlining, and extend formulas when adding new rows.

Use the Excel scenario manager to store multiple sets of changing cells for fruit sales, testing normal, awful, and good weather with what-if analysis in the same sheet.

Learn to use Excel's scenario manager to display and compare alternative value sets by adding changing cells and labels that identify which scenario is active.

Learn to use Excel 2013's scenario manager to generate a scenario summary that compares changing cells across multiple scenarios in one place, including outlining and hiding results.

Learn how to create and switch between custom views in Excel, saving versions of a sheet with hidden columns and rows, print settings, and zoom for quick comparisons.

Explore outlining with custom views to hide or show rows and columns in Excel 2013, then save and switch between full, quarters, and CEO views to streamline analysis.

Learn to edit and delete custom views in Excel by overwriting existing views, while preserving print settings, print preview, orientation, and headings.

Learn to speed up switching between custom views in Excel by adding a views group to the view ribbon and placing the custom views dropdown for one-click access.

Practice left and right text functions in Excel 2013 to extract first or last characters, including from names and phone numbers, with optional number of characters and safe handling.

Leverage len and trim alongside left and right to extract text from strings, clean imported data by trimming trailing spaces, and dynamically obtain image names without extensions.

Learn to use find and mid in Excel to locate the @ symbol, extract the domain, and capture file names before extensions across .jpg, .jpeg, and .tiff.

Learn how to join text from multiple cells using Excel's concatenate function and the ampersand operator, including adding spaces, embedding functions, and creating full names, addresses, and emails.

Learn how to standardize inconsistent data by using excel's changing case functions—upper, lower, and proper—to convert text, apply title case, and generate lowercase email addresses.

Learn how to use Excel 2013's replace and substitute functions to insert an employee id into email addresses by manipulating the string around the at symbol.

Use the char function in Excel 2013 to insert line feeds into concatenated strings, creating multi-line addresses and other characters, with if logic to skip empty fields.

Learn to format dates and numbers in Excel using the format menu and custom formats, then apply the TEXT function to display concatenated values like names with dates or salaries.

Learn how to convert formulas to text values after string manipulations in Excel by using paste special values, preserving formats, and applying upper, lower, and title case.