The Ultimate Microsoft Excel 2010 and 2013 Training Bundle

Install and verify Excel 2013, as this advanced course differs from earlier versions and emphasizes hands-on practice with sample files and a scratch folder.

Receive essential information for a successful training experience, including downloadable exercise files, how to download and unzip them, and how to adjust video quality and playback speed.

Explore Excel 2013 functions, including arguments, cell references, ranges, and nesting, while learning syntax rules and how to navigate category help for new and compatible functions.

Explore advanced aspects of Excel 2013 functions, learn to locate functions by category, use insert function, and leverage search, autocomplete, and function arguments dialogs.

Demonstrates auto sum in Excel 2013, totaling numeric columns or rows via smart range recognition and excluding non-numeric headers; uses the dropdown to apply max.

Learn how the pmc function in Excel 2013 computes monthly loan payments to amortize a loan, showing function arguments, monthly rate, and end-of-period vs beginning payments.

Explore how to use the future value function in Excel 2013 to forecast retirement savings. Model monthly deposits, interest rate, and present value to see how saving over decades grows.

Explore excel 2013 advanced finance features by modeling a loan with monthly payments, and analyze principal and interest using PMT, IPMT, and PPMT, with absolute references.

Explore depreciation in Excel 2013 using straight-line with sln, declining balance with db, and digits depreciation, comparing year-end values for a five-year asset.

Explore how Excel stores dates as day counts since 1900 and times as fractions of a day, distinguish real dates from text, and convert with date value and time value.

Master dates and times in Excel 2013 by learning the date and now functions, adding days and months, and calculating differences between dates for practical invoicing and time calculations.

Explore Excel 2013 date and time functions, using birth dates, retirement ages, and regional settings and date formats to calculate retirement months and future value.

Explore descriptive statistics in Excel 2013, using minimum, maximum, and mean, and apply averageifs for conditional averages; the course covers description, prediction via regression, and inference.

Explore computing percentiles in Excel 2010/2013 with percentile.inc and percentile.exc, including the 25th percentile and the median, and learn about interquartile range and standard deviation.

predict the dependent variable sales via regression in Excel 2013, using linear regression with slope and intercept, and forecast, trend, and array functions for quarterly forecasts.

Master the LINEST function in Excel 2013 to compute linear regression, extract slope and intercept, and interpret regression statistics such as coefficient of determination and sum of squares.

Explore inferential statistics in Excel using normal distribution functions to estimate population probabilities from sample data, with mean and standard deviation, and compute lower quartile, median, and upper quartile.

Clean messy employee data in Excel 2013 using text functions like proper, find, left, and right to split semicolon-separated lines into columns.

Clean imported employee data by converting text dates to proper dates, formatting them, and using substitute, replace, find and replace, and trim to normalize departments and remove spaces.

Explore Excel 2013's lookup and reference functions, including VLOOKUP and HLOOKUP, with exact and approximate matches, named ranges, and catalog and invoice examples.

Explore Excel 2013 advanced logical functions, including if, and, or, not, and nestings, to handle conditional discounts, lookup results, and error-free invoice formulas.

Learn to connect data across worksheets and workbooks in Excel, using paste link and paste special, view two windows, and manage linked updates between open and closed files.

Connect Excel to Access databases by importing tables and queries, building a data model with relationships, and refreshing data across sheets for integrated analysis.

Learn how to connect Excel 2013 to web data by importing tabular data with get data from web and configuring refresh options for automatic or manual updates.

Learn how to convert a worksheet range into a structured Excel 2013 table, name the table, apply filters, remove duplicates, and add a total row with automatic formatting.

Master modifying Excel 2013 tables by inserting rows and columns, resizing table ranges, and using design tab options to manage totals and auto expansion.

Learn to reference the Order Details table from outside the table using qualifiers to access the total price column and totals row, and build dynamic text with concatenation.

Explore table styles in Excel 2013 using the quick styles gallery to preview light, medium, and dark options. Create, duplicate, and customize styles, then convert a table to a range.

Learn to select data in a table column, whole rows, and header/totals in Excel 2013 using keyboard shortcuts and mouse toggles.

Explore the basics of pivot tables in excel 2013, turning transactional data into insights with rows, columns, and fields like branch, department, and value through pivot tables and charts.

Explore how to create dynamic pivot tables from a table, customize fields and layouts, group dates by months and quarters, and apply currency formatting for clear, presentational analysis.

Learn to filter pivot tables in Excel 2013 using filters, slices, and timelines to analyze branches, departments, and dates. Refresh data and group dates by quarters for focused insights.

Explore what-if analysis in Excel 2013 with one-variable and two-variable data tables to model loan payments. Use pmt and cum pmt to compare total repayments.

Master Excel scenario manager to manage multiple what-if analyses with up to 32 variables, using defined names for changing cells like loan amount and payment type, and generate summary reports.

Master goal seek in Excel 2013 to solve for angles from sine values, adjust iterative calculation for greater precision, and explore applying it to loan payments using the PMT function.

Explore how to use Excel 2013 solver for a staffing optimization problem, enforcing no overtime, five consecutive workdays, and integer constraints while minimizing total staff.

Master advanced charting in Excel 2013 by using area charts, including 3D and stacked variants, to compare performance and illustrate parts of a whole with transparency.

Master surface charts in Excel 2013 to visualize three-variable relationships, plot functions, and customize axes, colors, and chart types like 3d surface, wireframe, and contour.

Explore stock charts in excel 2013 by analyzing historical prices from yahoo finance, including adjusted close, dividends, and splits, and learn how to prepare data for plotting.

Create line charts of adjusted close and volume in Excel, using a secondary axis, and explore stock charts like high low close and candlesticks for visual analysis.

Learn how to create radar charts in Excel 2013 to compare employee performance against a company standard across six metrics: customer focus, critical reasoning, adaptability, creativity, responsiveness, and loyalty.

Explore bubble charts in excel 2013, turning a three-column data set into a three-dimension scatter plot where bubble size encodes total gdp, with formatting, labeling, and common pitfalls.

Learn to create a scatter chart in Excel 2013, add a trend line, display line of best fit with its coefficients and the r-squared value to analyze regression and correlation.

Learn to create and customize pivot charts from pivot tables in Excel 2013, using filters and layouts to visualize store sales data.

Insert sparklines in Excel 2010/2013 to create compact line or column charts within a cell, compare multiple series, and format groups with high points and win/loss options.

Explore how the Excel web app lets you create and save workbooks online via Sky Drive, with touch-friendly, auto-saving features and desktop Excel integration.

Share your Excel workbook online with the Excel web app and SkyDrive, set read or edit permissions, and embed the workbook on a web page.

Conclude the Excel 2013 advance training with Toby's farewell, inviting learners to continue online and reflect on the course experience.

Encourage learners to leave a review and rate this course with a five-star rating, share comments, and provide feedback to improve the training material.

Join Cindy as she introduces the Excel 2010 course, guiding you from familiarizing the screen and terminology to creating your first workbook, customizing it, and exploring printing and formulas.

Learn the Excel 2010 window layout, including workbooks and worksheets, title bar, quick access toolbar, ribbons, tabs, and named cells, plus normal, page layout, and page break preview views.

Learn how Excel mouse states work: select with the white cross, use the fill handle to auto-fill lists or formulas, move with the arrow, and type at the insertion point.

Explore excel options and customize the ribbon to tailor your workbook experience. Navigate general settings, live preview, mini toolbar, font size, and other options to optimize your Excel 2010 workflow.

Start a new workbook, enter text and numbers, and accept entries using enter, tab, or the check mark. Learn headers, Jan–Mar data, the fill handle, and text vs. number alignment.

Master basic Excel formulas by creating calculations with cell references across worksheets and files, using the equals sign, plus sign, and the fill handle to compute sales totals and profits.

Discover how relative references adjust across columns and rows when copying formulas, illustrated by B-3, C-3, D-3, and B-7. Later, you’ll learn how to create absolute references.

Master the order of operations in Excel formulas by using parentheses to control calculation, note that exponents, then multiplication/division, then addition/subtraction apply; learn with relative references and the fill handle.

A range in Excel is a group of adjacent cells. Learn to select ranges by dragging or shift-click, and use range-aware formulas with sum, average, min, and max.

Master saving workbooks by using save and save as, selecting location and naming the file in the save as window, with Excel automatically adding the extension (xlsx) for unsaved workbooks.

Learn how file extensions work when saving Excel workbooks, from three-digit to four-digit extensions, and choose a compatible save as type like xls/xlsx, template, csv, and other formats.

Open and close workbooks in Excel using the File tab, search desktop folders, and navigate different views to locate and manage your files.

Learn to work with larger Excel files by linking cells across sheets, using the payment function to model loans, and navigate large workbooks with the name box and freeze panes.

Use freeze panes to keep headers visible while scrolling by freezing rows above or columns to the left. Access view, freeze panes, choose top row or first column, and unfreeze.

Explore how to use the split screen option in Excel to view multiple parts of a worksheet simultaneously, adjust the splitter, and navigate with dual scroll bars.

Explore page setup options in Excel, including margins, orientation, size, and print area, learn to manage page breaks, center pages, and apply print titles for professional workbook printing.

Create and customize headers and footers in Excel's page layout, with left, center, and right sections, page numbers, and date. Apply different first-page and odd/even options and use print preview.

Learn how to set print titles in Excel by selecting rows to repeat at top so headers appear on page in print preview, and optionally repeat columns on the left.

Master adding, viewing, and managing comments in Excel, including inserting new comments, resizing with handles, and using print options such as none, as displayed, or print on sheet.

Learn how to fit an Excel workbook onto one page by adjusting margins, changing orientation, and using page break preview to place breaks exactly where needed.

Explore printing Excel workbooks using the file tab and page layout options. Choose to print the active sheet, a selection, or the entire workbook, with or without collated copies.

Learn to add and delete rows, columns, and cells in Excel, using insert options from the home tab or right-click, and choose how to shift cells.

Adjust column widths and row heights in Excel 2010 and 2013 using auto fit, double-click, and drag, and learn how to hide or unhide columns for clean data.

Learn to cut, copy, and paste in Excel by using the clipboard to move data between cells, understand how the clipboard stores items, and paste from a chosen item.

Copy formulas across and down with the fill handle. Explore paste options, including formulas and values, and use relative references and transpose to adapt formulas between regions.

Master formulas and functions in Excel by starting with the equal sign, using + - * /, and referencing data across sheets to compute totals, averages, and highs.

Learn to create formulas with functions in Excel, using ranges, function arguments, and the auto sum and insert function tools to perform sums, averages, counts, minimums, and maximums.

Master creating formulas using functions in Excel, using insert function, multiple ranges with comma separation, apply average and max, copy with the fill handle, and remember absolute values.

Explore how absolute values fix cells in Excel formulas, using $G$3 to prevent row or column changes while dragging with the fill handle, illustrated by a 15% commission example.

Rename, add, and delete sheet tabs to organize a workbook; insert sheets between others, move them, and avoid disrupting three dimensional formulas.

Move or copy sheets with the right-click menu or by dragging, including creating copies or moving between files, and protect, hide, unhide, color the tabs, delete, and view code.

Learn to build three dimensional formulas across multiple sheets by selecting a range of sheets, referencing cells with the sheet range syntax, and using the fill handle to copy across.

Explore comprehensive cell formatting in Excel 2010 and 2013, including font styles and sizes, color options, alignment, text orientation, wrap text, and merge-and-center across cells to enhance worksheets.

Learn to format numbers in Excel with currency, percentages, dates, and custom formats, including display value vs actual value, decimals, and special formats like phone numbers.

Apply borders around your selection with various styles and colors, including box borders and double lines, and enhance cells with shading and fill effects.

Learn how to convert data ranges to Excel tables, apply table styles, manage headers and row banding, and modify tables with add rows or columns, refresh data, and export options.

Apply styles to format cells in Excel quickly, including normal styles, headings, and custom styles. Use conditional formatting to highlight values with top/bottom rules and thresholds; explore the format painter.

Copy formatting with the format painter, using double-click to keep it active and escape to turn it off, then apply the copied style from Australian division to European division.

Protect worksheets by locking specific cells, the sheet, and the workbook; use find and select to identify formulas or constants, unlock cells for input, and set passwords to prevent edits.

Utilize the fill handle to auto-fill weekdays, months, and numeric sequences in Excel. Create, import, and edit custom lists to streamline sorting and data entry across workbooks.

Create charts from data in worksheets or tables using headers and axes. Explore embedded charts, chart sheets, data labels, legends, and chart areas.

Explore the range of Excel chart types and how to change them, from column and line to pie, bar, area, scatter, stock, surface, donut, bubble, and radar charts.

Edit charts by changing chart type and applying styles, layouts, and titles. Add data labels, axis titles, legends, and data tables, and customize fills, borders, and effects.

Learn to enhance charts by adding graphics—shapes, a text box, and arrows—highlighting Germany's top total in a pie chart, with fills, outlines, and arrowheads for clarity.

Explore how to manage named ranges in Excel using the name manager to edit, delete, and correct names, update what they refer to as ranges, and filter names for errors.

Learn to create and manage range names in Excel, using the name box and the create from selection feature to quickly navigate large workbooks with absolute range references.

Learn to use defined names in Excel formulas, including absolute references and cross-sheet references. Create sums with named ranges and insert them from the formulas tab to avoid errors.

Identify and remove duplicates in Excel using sorting and the remove duplicates tool. Learn to flag duplicates with an if formula and review results with find and select.

Learn how to clean and sort data in Excel 2010 and 2013, including removing duplicates, preparing a proper header row, and performing single, multi-level, and custom list sorts.

Apply filters in Excel using the Data tab and filter button to display only the needed rows. Combine conditional formatting with sorting and filtering to highlight data by color.

Use Excel's advanced filters by building a criteria range with exact column headings to apply double-criteria (and) filters, pulling records that meet Canada and children or Germany and history.

Master creating an outline in Excel to show levels of importance, using clean ranges with labels, and building quarterly and annual totals with formulas.

Enable auto outline from data tab’s outlining group to create levels of importance from formulas, then collapse or expand to hide or show details, and group and ungroup before subtotals.

Master Excel's subtotals feature to automatically total by division and count records, while following data hygiene guidelines, sorting, and outline controls to view or collapse results.

Use Excel's new window option to view two sheets side by side in the same file, such as Hanover's and Jane's numbers, for direct comparison.

Discover how to arrange multiple Excel files on screen with the arrange all options, maximizing each window and choosing horizontal, vertical, or cascade layouts to work with data across files.

Learn how to create a workspace by opening four files, arranging them tiled, saving the workspace named 'my workspace', and reopening it to automatically restore the four-file view.

Create and manage custom views in a single workbook to display only selected data, such as fruits or vegetables, by hiding rows; save original and other views, like quarterly totals.

Use the scenario manager in Excel to create original and alternate scenarios, adjust rents and insurance, and view profit differences on a single summary sheet.

Learn to link sheets and files in Excel, create three-dimensional sum formulas across multiple source sheets, rename tabs, select ranges, and build a summary from linked workbooks.

Master managing Excel links by updating values, changing sources, breaking links, and checking status via data connections, with startup prompts guiding automatic updates.

Explore advanced if statements in Excel by testing totals against quotas, using absolute references and named ranges, and calculating commissions with an if formula.

Learn to use vlookup to search a table vertically and return a price from a chosen column for a given item code, using exact match and table naming.

Consolidate data across multiple Excel files using the consolidate tool to sum quantities into a central workbook, then use links to source data to show file-level breakdown with outlines.

Apply data validation in Excel 2010/2013 to create drop-down lists, restrict entries to a range or list, and show input prompts and error alerts for consistent data.

Explore formula auditing in Excel by turning on show formulas, tracing precedents and dependents, and evaluating and debugging formulas to ensure accuracy and understand dependencies.

Learn to add and manage cell comments in Excel 2010/2013, including inserting notes, viewing with show all comments, navigating between comments, and printing comments on sheet or separate sheets.

Learn how to use Excel's goal seek under data > what-if analysis to determine the principal amount needed for a set monthly payment, given rate and term.

Learn to use Excel's text to columns to split names into first and last by delimited data with space, insert columns, and rename.

Master the watch window to monitor formulas across sheets with floating values and named ranges. See updates without switching to total sheet as you move between Europe, Australia, and totals.

We completed all 18 modules and encourage you to review the videos often. Practice regularly to reinforce what you've learned about Excel 2010, and reach out with questions.

Explore advanced Excel 2010 graphs and charts, learning the updated graphing engine, ribbon interface, and when to use specific chart types for effective data visualization and conditional formatting.

Follow four basic steps to create charts in Excel 2010: select the data, insert the chart, choose a layout and design, then customize it.

Learn to create and customize charts in Excel 2010, using default charts and shortcuts, choose chart types, handle noncontiguous data, and move charts between worksheets.

Learn to create and customize charts in Excel 2010, switch rows and columns, edit series and legend entries, adjust data ranges, and apply themes and titles for effective charts.

Master detailed chart formatting in Excel 2010, covering chart and plot areas, chart titles, axis titles, and layout options with fill, border, and 3D formatting.

Format chart legends in Excel by positioning with the layout tab, handling overlay legends, adjusting fills, borders, and orientation, and explore data labels and the data table with legend keys.

Explore how to format axes and grid lines in Excel charts, adjust horizontal and vertical axis options, tick marks, display units, and grid line styles to improve data interpretation.

Explore creating a column chart in Excel 2010 to display yearly sales trends, with year on the horizontal axis and a clear, presentation-ready title and axis formatting.

Learn to reveal complex trends in quarterly iPod sales by converting column charts to line charts, adding moving average trendlines, and formatting data series for clearer patterns.

Explore how Excel 2010 handles trends in charts over time, distinguishing date versus text axes, applying trendlines, forecasting with polynomial models, and converting text to dates.

Use bar charts to compare values and reveal differences, convert column charts to bars, adjust data ranges, and explore dual-series, 3-D, stacked, and 100% stacked bar charts.

Explore building and formatting pie charts in Excel 2010 using land-use data to show how a whole divides into categories.

Explore pie charts in Excel 2010 and 2013, including rotation, exploded slices, and color by slice, plus alternatives such as pie of pie for many categories.

Explore alternative chart options to show differences, including pie, bar, and donut charts, and learn to compare multiple countries' land use categories with proper formatting.

Explore how to create scatter charts in Excel to reveal relationships between inquiries, season of the year, and policies sold; apply trend lines and R-squared for forecasting.

Learn to present relationships in Excel charts using stacked bars with negative-inquiries versus policies, adjust axes and formatting, and use bubble and radar charts to compare multiple variables and targets.

Learn to chart and graph financial data by importing Yahoo Finance historical prices into Excel, adjusting for dividends and stock splits, and creating charts.

Create a dual-axis chart in Excel 2010 to show the adjusted closing price over 10 years with volume, using a line for price and a secondary clustered column for volume.

Discover how to create and customize finance charts in Excel 2010/2013, including hi-lo-close and candlestick charts with open, high, low, close, volume, and live data.

Create a live stock chart in Excel using Yahoo Finance data for Footsie and Dow Jones. Refresh every minute to show percent change.

Explore sparklines in Excel 2010 for compact data visualization, select line or column sparklines, configure data ranges and axis settings, group and ungroup sparklines, and add labels and marker options.

Apply and customize data bars, color scales, and icon sets in Excel 2010/2013, manage rules, and define minimum and maximum values to visualize comparable data faithfully.

Explore pivot tables and pivot charts in Excel 2010, learn to organize data in tables, apply filters, and adjust value settings to analyze sales by department.

Format pivot tables and charts in Excel 2010. Change data selection, switch chart types, add labels, and group dates by month to reveal sales by branch and department.

Learn to apply and manage filters in pivot tables and charts, create and use grouping for branches and years, and leverage report filters and slices to refine data analyses.

Explore how slices enhance filtering in pivot tables and pivot charts. Create calculated fields, such as value inc tax, to enrich analysis and dashboards with tax-inclusive totals.

Master Excel 2010 graphics tools, inserting shapes and SmartArt to enhance charts and spreadsheets, format colors and styles, resize with handles, and integrate images.

Explore graphics tools in Excel by building live data visuals with SmartArt, progress percentages, and customized shapes to present team performance without traditional charts.

Export Excel charts to Word using paste options like link, embed workbook, and picture. Learn how destination theme, keep source formatting, and update behaviors affect charts.

Explore exporting Excel charts and data to Word and PowerPoint, compare linking and embedding, and publish charts to the web or PDF formats using Office 2010 tools.