Excel 2010

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 worksheet, rename it circulation, copy cells A1 and A2 from fiscal 2009, and paste into the new sheet using the clipboard commands and the enter key.

Copy cells A1 and A2 from fiscal 2009, paste into the circulation sheet, and left-align to improve readability; rename sheets as shown.

learn to copy data from fiscal 2009 to the circulation sheet in Excel 2010, paste with source column widths, auto-fit columns, delete a column, and update the year label.

Copy a range from fiscal 2011, paste into the circulation sheet with the correct column, replace circulation with the year, and prepare for inserting a missing column in Excel 2010.

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.

Master cut and paste in Excel 2010 by moving data between worksheets, demonstrating how cut removes from the original location and pastes to a new spot, unlike copy and paste.

Use drag and drop to cut, copy, and paste within the same sheet by selecting cells and dragging from a border; hold the control key to copy.

Discover how to use the Excel 2010 autofill handle to fill a series of months, from January to April, by dragging left or right and counting forward or backward.

Explore autofill techniques in Excel 2010, using the fill handle to generate weekdays, months, numbered series, and ordinals, with options for abbreviations and custom series.

Explore formatting in Excel 2010 by applying and overriding cell styles to color-code quarters, adjust fonts and fills, and manage totals for clear, eyeball-friendly worksheets.

Increase font size in the home tab with live preview to make text stand out, auto fit columns by double-clicking borders for columns B, C, D and F, G, H.

Learn how to format numbers in Excel 2010, adjusting decimals, applying currency or general formats, and adding thousand separators to improve readability.

Discover how to use Excel templates to avoid overwriting originals by saving as templates in the backstage view, and leverage built-in templates for common tasks like expense reports.

Learn how to manage worksheet tabs in Excel 2010 by coloring and grouping multiple sheets, selecting with shift and control, and applying a unified tab color.

Learn how to ungroup and manage sheets in Excel 2010 by selecting a tab not in your group, inserting and renaming new worksheets, and deleting sheets with a confirmation prompt.

Group and edit multiple sheets in Excel 2010 by selecting fiscal 2009 to 2011 with the shift key, then apply changes to font color and background across all sheets.

Excel 2010: copy entire sheets by selecting multiple sheets, using move or copy, check create a copy, and choose a destination workbook while preserving formatting.

Use the Excel spell checker from the review tab to run it on demand with F7, checking against a dictionary and offering change, ignore, or add to dictionary options.

Learn to freeze panes in Excel 2010, keeping the top row visible while you scroll large worksheets, using the view tab to apply and verify the header remains.

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.

Learn to create and manage custom views in Excel 2010, saving original, zoomed, and hidden rows and columns with print settings and filters, then switch between views to fit needs.

Set up printing in excel by using page layout and print titles, repeat the top row on every page, and fit all columns on one page for clean printouts.

Open the page layout view in excel 2010 to see exactly what fits on each page with live data. Edit headers and footers, adjust margins, and preview multiple pages.

Follow the Excel 2010 course checklist to navigate the course and stay on track with essential tasks.

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

Create a well-defined list by keeping consistent data in each column with a header row, and avoid blank rows or columns and merging across cells, which complicates sorting and filtering.

Learn how to sort a table by a single field, group records by division and department, and restore original order using a key number column in Excel 2010.

Master multi-level sorting in Excel 2010 with the big sort button to sort by division, department, last name, and first name, including up to 64 levels and custom lists.

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.

Sort in Excel 2010 by a field with a to z or z to a, or use a custom list to sort by numbers, text, or dates newest to oldest.

Turn on auto filters, then use the division drop-down to select Connecticut with checkboxes, hiding all other data while preserving the original order.

Learn to apply multi-field filtering in Excel 2010 by using the filter arrows to combine conditions, see blue row highlights, and observe status bar counts.

Master filtering ranges in Excel 2010 using natural language filters and custom auto filter, with a combo box, to show employees who work less than 40 hours across departments.

Learn to remove filters in Excel 2010, one at a time or all at once, using clear filter from division or select all, while department and hours filters stay active.

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.

Use the sum total command to filter and sum data by department or division in tables. Use subtotals on the data ribbon to sum up gross pay by department.

Excel 2010 lesson demonstrates sorting by division and department before subtotals, using multiple sorts to group salespeople and development teams and avoid junk.

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

Learn to subtotal by department without replacing current subtotals, add department subtotals atop division subtotals, view four levels, and use plus and minus to show details.

Format a contiguous range as a table to apply styles, alternating bands, and auto filtering arrows, with header visibility and new-row expansion; subtotals are unavailable.

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.

Select the data range and turn it into a chart with the insert tab, choosing common chart types like column, line, or pie. Aim for one idea per chart.

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.

Modify chart colors by selecting the blue column that represents Smyth's numbers to apply changes to all blue columns, then use charting tools to customize fills, styles, and color schemes.

learn to change a chart column color in Excel 2010 by selecting the series, using the format tab, choosing fill color, and applying a solid canary yellow to Smith columns.

Place a chart title to explain the data, such as the sales figures from January 2012, using the layout tab to position it above the chart or as an overlay.

Apply a gradient fill to the plot area in Excel 2010, format the plot area, and customize color stops and positions to create a layered, dynamic background.

Switch rows and columns in a chart via the design tab to place weeks in the legend and salespeople on the category axis, then delete totals to compare weeks.

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.

Reorder the chart columns by using select data to move weeks up or down, then delete, copy, and paste data to restore or adjust the legend and columns.

Learn to create line charts in Excel 2010 by using the design tab to change the chart type and apply a line chart with markers to show trends over time.

Switch rows and columns to place time on the bottom of a line chart, read trends clearly, and avoid totals that distort the message.

Explore how to build and customize a two-dimensional pie chart in Excel 2010, including selecting data, switching rows and columns, and interpreting labels, legend, and percentages.

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.

Learn to create and reuse an Excel 2010 column chart template, saving a gradient-filled chart for use across different data sets.

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.

Explore win/loss sparklines in Excel 2010, showing positive or negative markers and comparing to line sparklines, with applications like stock picks.

Explore Excel 2010 through a course check list that guides learners through the course structure and introduces the Excel 2010 topics highlighted by the lecture title.

Learn how Excel 2010 handles importing and exporting data, creates pivot tables, protects workbooks and worksheets, and links data with other programs.

Start Excel 2010 from scratch, open a text file, and import data using tab delimited or CSV formats to bridge Excel with Ashton Tate dbase 3.

Explore how a tab-delimited text file organizes data into columns like month, store number, SKU, sales, and units sold, viewed in notepad with tab and hard return separations.

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.

Save the workbook by clicking the File tab and choosing save, then place it in the desktop Excel 2010 practice Files folder. Name the workbook imported stuff.

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.

Learn how to bring data in from an Access database into Excel 2010, create the connection, and refresh and filter the imported data.

Explore how Excel 2010 links to Access to edit and refresh data across applications, including enabling connections, read-only modes, and automatic saves when updating records.

Export from Excel to Microsoft Word, and explore common destinations like Access databases, file tab operations, and opening day two work files from the Excel practice files folder.

use the save as command in the backstage view to export from excel, choosing a save as type such as web page, template, tab delimited, comma delimited, text, or pda.

Explore how pivot tables analyze large data sets, and learn to rename the subtotals worksheet to wine sales using a right-click and enter to confirm.

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.

Master the classic pivot table layout in Excel 2010, using the pivot table tools to drag fields and summarize wine sales data into one row per sales rep.

Create a pivot table in Excel 2010 by dragging fields into row labels, column labels, and values to total sales by sales rep and wine type.

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 how to use a report filter and a slicer in Excel 2010 pivot tables to filter by month and select multiple items.

Turn a pivot table into a pivot chart by selecting the pivot chart option for a columnar chart, then simplify by removing the slicer and slices, keeping wrap and type.

Learn to filter pivot tables and pivot charts in Excel 2010, see their interaction, and turn a pivot table into a pivot chart with data labels and colors.

Insert a new sheet, link charting one and charting two totals across workbooks or worksheets, and use cell references to auto-update grand totals across sheets in Excel 2010.

Copy the grand total from charting two, switch to sheet1, and paste a link using the paste that has a link option to create an absolute reference to source cell.

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.

In Excel 2010, use the show all comments button to keep comments visible, drag the comment by its border to reposition, and ensure it stays even when you click away.

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

Learn to make the three biggest and three smallest numbers in a data set pop using conditional formatting in Excel 2010, via the Home tab and top bottom rules.

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 to set up data validation in Excel 2010 by restricting entries to a list, with input messages and error alerts.

Enforce a top credit limit of 5000 using data validation rules in Excel 2010, allowing decimal values up to 5000, with an input message and a warning-style error alert.

Shows how to apply data validation and conditional formatting in Excel 2010 to highlight values greater than 5000 with red text, using highlight cells rules to flag rule breaches.

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.

Protect the workbook using the review tab to guard the structure and the windows. Set a password to further protect the workbook and prevent deleting tabs, reordering sheets, and resizing.

Learn to password protect Excel workbooks, test by reopening, and remove passwords, while exploring data imports, pivot tables and charts, and basic protections for sheets and workbooks.

Navigate the Excel 2010 course using a check list to stay organized and monitor your learning progress.

Download the Excello day three work files from the practice files area, enable content, and begin in the if function worksheet to study advanced Excel 2010 module 5 functions.

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.

Master named ranges in Excel 2010 by inserting and using the totals range in formulas. Sum and average named ranges with use in formula, F3, and the three-key shortcut.

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.

Master nested if functions in Excel 2010 by testing sales thresholds of 40000 and 36000 to calculate 2 percent or 1 percent bonuses on total sales.

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 to create a named range for vlookup, freeze references with dollar signs, and use fill handle to copy across while adjusting the column index for department and pay rate.

Use vlookup with the fourth argument true to return the largest value less than the target when an exact match isn't found, using a named bonus lookup table.

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.

Learn how to nest if statements inside a vlookup to assign column-based bonuses for holidays, and use absolute references to freeze a specific row while dragging the fill handle.

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.

Excel 2010 teaches how to use averageif by specifying a range, a criteria, and an average range, showing its similarity to sumif with examples.

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.

Master named ranges and absolute references in Excel 2010, and apply core functions such as if, iferror, vlookup, and hlookup, including nesting and approximate matches, plus date calculations.

A course checklist for learning Excel 2010, guiding learners through essential topics and study steps.

Learn to consolidate data in Excel 2010 using data tables, scenario manager, macros, and the goal sequence solver tools, while managing worksheets and cleaning the consolidated sheet.

Consolidate data across the Jane, Monder, and Hanover sheets by using the top row and left-hand column, summing values and creating links to source data.

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 how to use Excel 2010 goal seek to adjust loan amount for a 25-year mortgage and reach a 4,500 monthly payment. The lesson highlights named ranges and what-if analysis.

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.

An exact equality constraint makes the total plant production equal to warehouse requirements, enforcing just-in-time delivery and matching row 9 to row 11.

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.

Learn to build a two-variable data table in Excel 2010, substituting inputs for interest rate and term to analyze mortgage payments with row and column input cells.

Use Excel's scenario manager to run what-if projections with multiple inputs and outputs. Create original, better growth, better Q1, and worst-case scenarios to see their impact on the bottom line.

Learn to use the summary sheet in Excel 2010 to compare multiple scenarios and see how changes affect cell G9 and the bottom line, without creating extra worksheets.

Enable the developer tab in Excel to record macros and automate tasks by right-clicking the ribbon, selecting customize the ribbon, checking developer, and clicking okay.

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.

Learn to use relative references when recording macros in Excel 2010, including turning on relative mode, starting anywhere, recording actions, and safely running the macro while noting potential pitfalls.

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.

Assign a keyboard shortcut to a macro in excel 2010 by selecting the macro, opening options, and setting a description and key combo such as control shift.

Create a custom ribbon tab in Excel 2010 to run macros by adding a new tab and groups, then drag your macros into the my macros group and test them.

Learn to automate tasks with macros and use speak cells on enter in the quick access toolbar to read data aloud during entry in Excel 2010.

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