ExecuTrain Houston

Microsoft Excel 2010:
Part Two

Functions in Excel 2010

Explore Excel’s wide variety of built-in functions in the function libraries.

Details:

• Dates and times are values that can be added, subtracted or used in other calculations. The Today function displays the current date in your worksheet.
• Use the PMT function to calculate a periodic loan payment.
• The concatenate function is used to combine the contents of multiple cells into one cell. You might use the function to combine a cell containing First Name and a cell containing Last Name to display the full name.
• Use the IF function to perform the specified action if the criteria is true and another action if the criteria is false.
• Use the VLOOKUP function to search a table for a specified item and then move across the row to return the value of the designated column.
• Explore the Statistical functions of Average, Min, Max, and Count.
• Use the Convert function to convert one measurement unit to another.

Working with Multiple Worksheets in Excel 2010

While data are routinely stored on a single worksheet, Excel does not limit your calculations to that sheet. Use a variety of methods to collect, calculate, and present information from many sheets simultaneously in the format you choose.

Details:

• Use the Fill Across Worksheets feature to quickly copy worksheet data to multiple worksheets. You can also link information between worksheets to update the copied data whenever the source data has changed.
• You can create formulas and functions that contain references to cells that are on separate worksheets or workbooks.
• The Consolidate feature enables you to summarize multiple worksheets that contain related lists into one location. You can choose to display a total of each item or use another function to calculate the data in a different way.
• Use conditional formatting to apply a specified format to only the information that meets the criteria you specify.

Sorting, Filtering and other Data Commands in Excel 2010

Explore a variety of techniques for displaying precisely the information you need in a given moment or for a particular purpose. Use subtotals to stay apprised of activity in smaller groups of numbers.

Details:

• Use the sort ascending, sort descending, and sort dialog box to rearrange the data list.
• Allow Excel to quickly calculate subtotals to a list based on the data of a particular field.
• AutoFilter is a quick and easy way to display only those rows the meet the criteria you specify.
• Use Advanced Filters to query your data and display only the information that meets the criteria you specify.
• Data validation ensures correct data is entered into a data list.
• Use the Text to Columns feature to separate data in one column to multiple columns.
• If there are formulas for which you need up-to-the-minute values, the Watch Window enables you to view cell contents without having to navigate to that particular cell.

PivotTables in Excel 2010

PivotTables enable you to organize and summarize your data so that it includes the information you want to display and excludes the rest. Change a PivotTable’s focus by moving the fields to different locations, and create PivotCharts to represent the data in a graphical format.

Details:

• Field buttons in PivotTables enable you to quickly change the information being displayed. Calculated fields can be modified to display the data using a different calculation or a new calculation can be added to meet your individual needs.
• Group fields to broaden the view of the selected field. This feature works especially well to group dates that span several years into months or quarters, but can be used for text and numbers as well.
• PivotCharts present you PivotTable data in graphical form. Just like PivotTables, the information viewed can be quickly changed by using the field buttons to include or exclude field items.

Macros & Shapes in Excel 2010

Instead of repeatedly performing routine and/or complex tasks, let Excel remember them for you so they can be carried out with a click or a keystroke – then store them on your Quick Access toolbar for easy access.
Enhance the appeal and impact of your worksheet by including visual objects that supplement your data, then format and arrange them for maximum effect.

Details:

• Macros replay a series of events such as keystrokes or mouse clicks. Learn how to record a macro, assign it to the Quick Access toolbar, and then run the macro.
• Learn to make simple edits to a macro such as renaming the macro or formatting a column heading by using the Visual Basic Editor.
• Learn how to use Shapes to enhance a spreadsheet. Learn to insert, move, and resize Shapes.