Download and open the Excel day 1 workbook, then navigate between sheets, insert and arrange sheets, and copy data across sheets to consolidate information from fiscal 2009–2011.

Insert a column, copy data from the 2010 worksheet, and paste both the data and column widths into the circulation sheet, labeling the new column as 2010.

Learn how to unfreeze panes and split the screen in Excel 2010 to compare data across the top and bottom or left and right, with tips on placement and conflicts.

Explore working with lists and charts in Excel 2010, as module 3 shows how to manage data lists and create charts alongside math.

Learn to sort in Excel 2010 with custom sort lists to order days of the week or months, using built-in lists and avoiding mixing long and abbreviated forms.

Learn to create a custom sort list in Excel 2010 to sort data non-alphabetically, by a compass order (north, west, south, east) using the start button and the custom list editor.

Turn off all filters with the clear button to restore all records. Apply auto filter and natural language date filters to refine data across multiple fields.

Filter data with autofilter arrows, sort, and clear filters in Excel 2010. Remove duplicates by adjusting which columns must match to preserve or delete similar records.

Learn to reproduce a duplicate record problem in excel 2010 by selecting records, copying to create duplicates, and using remove duplicates to delete exact duplicates and triplicates.

Learn to subtotal by division in Excel 2010, summing gross pay by division, understanding subtotals, grand totals, and detail levels.

Convert a formatted Excel 2010 table to a range using the table tools design tab, then confirm to remove the auto filtering arrows and table headers.

Learn to create a two-dimensional clustered column chart in Excel 2010, understand how chart height mirrors data values, and why excluding totals from the data selection matters.

Learn how to re-add accidentally removed data to an Excel 2010 chart by dragging week three back into the data, copying and pasting, and adjusting the legend order.

Add and customize data labels in Excel 2010, show the amount and percentage on each slice from the layout tab, and use leader lines for skinny slices.

Explore sparklines in Excel 2010, creating in-cell line charts that show trends without overlap, customizing colors and markers for high and low points to highlight data patterns.

Select the data range in Excel 2010, insert tab, and create column sparklines to display trends. Note that column sparklines cannot compare heights across, and line sparklines are preferred.

Establish a data connection in Excel 2010 to a text file, use the text import wizard to define delimited data, and set refresh every 60 minutes or on open.

edit a text file in notepad by deleting the last two rows, adding an April row, and saving with tab delimiters, to practice preparing data for Excel 2010.

Learn how to refresh Excel connections to update data from external sources, observe how new data replaces old entries, and understand how manual edits can be overwritten.

Pivot tables transform a large range of cells with sales data into insights by organizing fields in the pivot table field list on a new worksheet.

learn how to build a pivot table with multiple fields in Excel 2010, placing state and rep in rows and types and groups in columns, swapping fields for cleaner totals.

Learn how to format totals in Excel 2010 by selecting the total rows, applying bold and color styles, and using precise mouse techniques to target specific cells.

Explore linking across sheets and workbooks in Excel, including a 3-D link with workbook, sheet, and cell references. Insert a comment from the Review tab to annotate the total.

Learn how to print comments in Excel 2010. Toggle show comments in page setup and choose display on sheet or at end.

Use conditional formatting color scales in Excel 2010 to compare data in a column automatically, with the biggest numbers green and the smallest red.

Learn to use data bars in Excel 2010 by selecting data, applying conditional formatting, and choosing a blue gradient to visualize value magnitude like a mini bar chart.

Learn how to protect the worksheet in Excel 2010 by locking and unlocking cells, using the protect sheet feature with a password, and allowing edits only in designated cells.

Create named ranges in Excel by selecting a range and naming it in the name box (no spaces, must start with a letter); then use auto sum for totals.

Explore how to convert a numeric function into a logical test in Excel 2010 by testing whether the average of totals exceeds a threshold, returning true or false.

Create and troubleshoot the Excel if function by testing conditions, returning true or false values, and using absolute references with dollar signs to ensure correct results when dragging down.

Learn to use vlookup to search the leftmost column for a target and return a value from the matching row, using exact match and a named range for reliability.

learn how to use vlookup with true for approximate matches, then build a nested if to switch bonuses between column 2 and column 3 based on holiday in e14.

HLOOKUP, the horizontal counterpart to VLOOKUP, searches the first row and returns a value from the same column in a specified row, with true for approximate and false for exact.

Master the sumif function in Excel 2010 to total expenses by category across divisions. Set the range, criteria, and sum range, and compare with manual totals to confirm accuracy.

Learn to use sumifs to total values with multiple criteria across columns, selecting sum ranges and criteria ranges for division and category such as east and software.

Create a data validation lookup list to drive a pull-down menu from a specified source and summarize totals by category. Switch to a pivot table for large data sets.

Master date functions in Excel by treating dates as numbers, inserting a static date with ctrl+semicolon, and calculating days overdue through date subtraction with absolute references for fills.

Learn how Excel's Goal Seek tool finds a target by adjusting variables, shown with the loan amortization template; access templates via file, new, sample templates, and create.

Learn to use Goal Seek in Excel 2010 to reach a specified monthly payment by varying the interest rate while keeping the loan amount fixed.

install the solver add-in in excel via the backstage view, then use solver to minimize shipping costs by adjusting plant production under constraints like at least 20 units per quarter.

Apply capacity constraints to a multi-plant model by setting Sunnyvale at most 92 units per quarter, Portland at most 45, and Austin at most 55, using add constraints.

Explore constraint four, noting that shipping costs vary by plant locations and are factored into total costs, with no constraint needed, then run the solver to view the results.

Use the solver to minimize shipping costs by producing more at cheap plants and less at expensive ones, with at least 20 units per plant and Sayville at 92 capacity.

Learn to use the F4 key to toggle absolute references in Excel 2010, and set up data tables to explore multiple outputs from varying inputs.

Explore how to view and edit Excel macros in the Visual Basic Editor, record macros with absolute references, and run or playback them using shortcuts like Alt+F11.

Debug a relative macro in Excel 2010 by tracing how starting at F3 causes negative offsets and break mode, then using reset and starting at H3 to run it successfully.

Learn to distinguish absolute and relative references in Excel macros, using offset values to move rows and columns. Explore recording relative formatting actions in a workbook and switching between sheets.

Pre-select the cells, start the macro recorder, apply yellow font and dark blue fill, then stop and run the 'Fancy format' macro on other ranges.

Review the Excel 2010 course check list to guide your study and ensure you cover the key topics included in the course.