-
Keyboard Shortcuts
To use Excel 2010 well and efficiently; you should consider using keyboard shortcuts. Using the keyboard shortcuts allows you to keep your hands on the keyboard instead of having to reach for the mouse constantly. In this video; Adam Wilcox will demonstrate how to use several keyboard shortcuts to improve your efficiency.
-
Fixing Text
Microsoft Excel 2010 allows you to extract information from strings and fix text to appear the way you want. In this video; Adam Wilcox demonstrates how to concatenate two strings and to change the case of the text.
-
Manipulating Text
Microsoft Excel 2010 allows you to manipulate strings that comprise text; numbers; and special characters; such as Product IDs. In this video; Adam Wilcox demonstrates how to apply text functions to return the length of a string; the position of characters within the string; and the prefix; suffix; and number from the string.
-
Useful Math Functions
Excel 2010 includes many math functions including ones to find integers; to round a number; to find greatest common denominator; to find the circumference and area of a circle; and many more. In this video; Adam Wilcox will demonstrate how to use some useful math functions.
-
About Range Lookups
In Microsoft Excel 2010; range lookups allow you to return values even if the information you’re looking up isn’t precisely within the lookup range. The Range Lookup function can be used to return all the sales figures below a certain value in a table. In this video; Adam Wilcox uses the Range argument of the VLOOKUP function to find and return data from a worksheet.
-
Add Data Validation Rule
Data validation is a means to check that the data entered in a workbook conforms to certain rules. You can specify the validation criteria for a cell range; and use messages to communicate to users what the rules are. In this video; Adam Wilcox demonstrates how to add data validation rules to a workbook.
-
Add Drop Down Validation Rule
You can add drop-down lists to Microsoft Excel 2010 workbooks using validation rules. This allows you to select a value for each cell from a defined list. In this video; Adam Wilcox demonstrates how to add a list-based validation rule to a workbook.
-
Additional Date Features
Microsoft Excel 2010 has some handy functions that you can use when you work with dates. In this video; Adam Wilcox demonstrates how he calculates a date based on a number of days; the number of workdays between two dates; and how to display the month; day; or year as text or as a number.
-
Additional PivotTable Features
Microsoft Excel 2010 provides additional PivotTable features making it easier for you to view data and garner information from spreadsheets. In this video; Adam Wilcox uses the additional PivotTable features to view data by date; summarize data by second; minute; hour; month; day; quarter; or year; view data by subtotals; and filter the data using slicers.
-
Align Text in Cells
Microsoft Excel 2010 has several alignment options for altering the way text displays in a cell. For example; you can center; rotate; or wrap text. In this video; Adam Wilcox demonstrates how to use the various alignment features to modify the appearance of text in a worksheet.
-
Analysis ToolPak
In Microsoft Excel 2010; you can load add-ins such as the Analysis ToolPak to perform statistical analysis on your data. Some common analysis tools are correlation; sampling; and histograms. In this video; Adam Wilcox demonstrates how to load the Analysis ToolPak and use some of its tools to analyze data.
-
Apply and Modify Number Formats
Microsoft Excel 2010 provides several number formats. You can easily format numbers as currency; dates; or phone numbers. In this video; Adam Wilcox demonstrates how to apply various formats to numbers.
-
Apply Conditional Formatting
In Microsoft Excel 2010; you can use conditional formatting to apply formats based on a condition to cells in a worksheet. You can use preset conditional formatting; such as highlighting cells; data bars; or icon sets. Alternatively you can create your own formatting rules. In this video; Adam Wilcox demonstrates how to use preset rules and create your own rules.
-
Array Formulas
In Excel 2010 you can use arrays to perform some very sophisticated calculations. In this video; Adam Wilcox defines what an array is and then demonstrates how to enter an array and use it to perform calculations.
-
Calculations in Pivot Tables
In Excel 2010; you can control the calculations on the values in a pivot table. In this video; Adam Wilcox demonstrates how to show values and summarize values on the data in a pivot table.
-
Chart Layout and Formatting
Microsoft Excel 2010 includes several tools for formatting charts to improve their appearance and the information they convey. You can change the chart type; select a style; and insert labels and titles. In this video; Adam Wilcox demonstrates how to format the style and data labels of a pie chart.
-
Choosing Appropriate Chart Types
In Excel 2010; there is a wide range of chart types you can use to illustrate your data. In this video; Adam Wilcox will demonstrate how to select the appropriate chart type to illustrate your data.
-
Cleaning Data and Removing Duplicates
If you receive data from a source other than an Excel worksheet; you might need to clean up the data to use in Excel 2010. In this video; Adam Wilcox demonstrates how to clean the data including removing extra characters; converting text to numbers; and removing duplicate values.
-
Combining Text
Microsoft Excel 2010 includes functions for manipulating text. You can combine text from different cells into one cell or you can separate parts of a string into different cells. In this video; Adam Wilcox demonstrates how to use the CONCATENATE; LEFT; RIGHT; and MID functions to manipulate text.
-
Consolidating Data in Several Workbooks
The Consolidate command in Microsoft Excel 2010 allows you to take information from several similar workbooks and combine it in one workbook. In this video; Adam Wilcox demonstrates how to consolidate data.
-
Copying and Moving Data
In Microsoft Excel 2010; you can copy or move data in a worksheet from one location to another. In this video; Adam Wilcox demonstrates several methods of copying and pasting data as well as moving data from one cell to another in a worksheet.
-
Copying Cell Formatting
In Microsoft Excel 2010; you can copy the formatting in a cell or cell range to another location in your workbook; using the Paste options or Format Painter. In this video; Adam Wilcox demonstrates how to copy formatting to a single cell and a range of cells in a workbook.
-
Create a Scenario
In Microsoft Excel 2010; you can create scenarios as a way to look at different combinations of values in your workbook. In this video; Adam Wilcox demonstrates how to use the Scenario Manager to create and switch between different sets of data in the same worksheet.
-
Creating PivotTable Reports
In Microsoft Excel 2010; PivotTables are an incredibly useful tool. They allow you to generate useful information from large amounts of data. In this video; Adam Wilcox demonstrates how to create and modify a PivotTable report.
-
Custom Number Formats
Microsoft Excel 2010 enables you to customize number formats in various ways. You can customize positive numbers; negative numbers; zeroes; and text. In this video; Adam Wilcox demonstrates how to customize various number formats.
-
Elements of an Excel Chart
Charts are visual representations of data. In Microsoft Excel 2010; you can easily create various types of charts from an existing data source. In this video; Adam Wilcox demonstrates how to create and modify an Excel chart.
-
Entering and Understanding Functions
Microsoft Excel 2010 includes a built-in library of calculations; known as functions. You can use functions to perform various calculations; such as adding numbers or calculating interest payments. In this video; Adam Wilcox uses the SUM; AVERAGE; and SUMIF functions to calculate the total and average sales; as well as the sales per region in a worksheet.
-
Excel Options
The Excel Options dialog box allows you to configure how the Microsoft Excel 2010 environment behaves. You can change the default font; the tabs on the Ribbon; the options for saving and proofing workbooks; as well as the way formulas work. In this video; Adam Wilcox demonstrates how to configure some of these options.
-
Fill Series
In Microsoft Excel 2010; you can use the fill handle to easily enter a series of data; such as sequential numbers or dates; down a column or across a row. In this video; Adam Wilcox demonstrates how to create series of numbers; text; and dates in a worksheet.
-
Formula Concepts
You can perform calculations in Microsoft Excel 2010 using formulas. Formulas always start with an equal sign and then use combinations of operators; references; and constants to get a result. In this video; Adam Wilcox demonstrates the components of basic formulas as well as the order in which Excel evaluates the operations in a formula.
-
Goal Seek
In Microsoft Excel 2010; you can perform What-If analysis using tools such as Goal Seek. Goal Seek lets you set a target value to determine what input values will help you reach that target. For more complex analysis; you can use Solver; which is an Excel add-in. In this video; Adam Wilcox demonstrates how to use Goal Seek and Solver.
-
Graphics and Smart Art
Microsoft Excel 2010 has several tools for adding simple and effective graphics; such as pictures; shapes; and SmartArt; to a workbook. In this video; Adam Wilcox demonstrates how to add and format graphics in Excel.
-
Grouping and Subtotals
In Excel 2010; you can organize large amounts of data in a worksheet into groups and then subtotal the groups for a higher-level view of the data. In this video; Adam Wilcox demonstrates how to organize a large worksheet containing sales data into groups and create subtotals for these groups.
-
Headers and Footers
Microsoft Excel 2010 lets you add headers and footers to a worksheet that is being printed. You can add the title or page number to the header or footer on each page. In this video; Adam Wilcox demonstrates how to add headers and footers to a worksheet.
-
Import Data from the Web
The Web Query feature in Microsoft Excel 2010 lets you create and maintain a live connection between your workbook and data on the Web. In this video; Adam Wilcox demonstrates how to add a live currency conversion table to a workbook.
-
Import Delimited Text Files
Microsoft Excel 2010 has some useful tools for parsing data from different kinds of files; such as a text file or a Word document; into Excel worksheets. In this video; Adam Wilcox demonstrates how to bring external data into Excel and display it in columns.
-
Inserting and Deleting Rows and Columns
Microsoft Excel 2010 allows you to easily insert and delete data in the middle of a worksheet. However; you need be aware of how insertions or deletions affect the surrounding data. In this video; Adam Wilcox demonstrates how to add and remove data from a worksheet.
-
Styles and Themes
In Excel 2010; you can quickly and easily apply consistent formatting to cells or ranges in your worksheets using themes and styles. In this video; Adam Wilcox will demonstrate how to create; apply; and modify styles as well as to change the theme including the color palette; fonts; and effects.
-
Using Match and Index
Microsoft Excel 2010 provides the MATCH and INDEX functions to find values in an array based on the position of those values. In this video; Adam Wilcox demonstrates two methods of returning the product ID in a table using the year and model number.
-
Using Pivot Charts
In Excel 2010; you can use pivot charts to illustrate large amounts of data in digestible chunks. In this video; Adam Wilcox demonstrates how to create pivot charts from pivot table data.
-
Using Simple Finance Functions
In Excel 2010; there are many financial functions you can use in your worksheets. In this video; Adam Wilcox will demonstrate how to use three of the simple financial functions to calculate future value; present value; and payments.
-
Using Structured References with Tables
Tables are a powerful feature of Excel 2010 and structured references are one of the best things about them. In this video; Adam Wilcox will demonstrate the syntax for referring to table data by structured reference name in formulas.
-
Using Workspaces for Multiple Workbooks
There are several ways of arranging multiple open workbooks in Microsoft Excel 2010. You can also save the configuration as a workspace. In this video; Adam Wilcox demonstrates how to manage your workbooks and workspaces.
-
Variable-Sized Range Names
Microsoft Excel 2010 enables you to create names for ranges that could vary in size. You can use the OFFSET function to specify the range based on a starting cell and the number of rows and columns you want to include. In this video; Adam Wilcox uses the OFFSET function to create a variable sized range name for a PivotTable.
-
Workbook Properties
Each Microsoft Excel 2010 workbook has a set of properties that are associated with it as a file. These properties include the title; the author; and keywords. In this video; Adam Wilcox demonstrates how to access and change a workbook's properties.
-
Working with Dates
Microsoft Excel 2010 stores dates in a sequential way to enable you to perform calculations; such as the amount of time between two dates. In this video; Adam Wilcox demonstrates how to calculate the number of years that an employee has worked at a company.
-
Working with Panes
Using panes and extra windows in Microsoft Excel 2010 allows you to view different parts of a workbook at the same time. In this video; Adam Wilcox demonstrates how to use panes and windows to display more data in a workbook at the same time.
-
Link Cells from Different Workbooks
In Microsoft Excel 2010; you can connect information in different worksheets or workbooks by using links. In this video; Adam Wilcox demonstrates how to create links to connect data in separate workbooks.
-
Making Use of Logical Functions
Logical functions are some of the most powerful functions in Microsoft Excel 2010 because they make decisions before doing calculations. For example; Excel can perform different calculations; depending on whether a condition is true or not. In this video; Adam Wilcox demonstrates how to use logical functions to calculate whether employees qualify for goal bonuses; category bonuses; or club membership.
-
Managing Worksheets
In Microsoft Excel 2010; workbooks usually contain several worksheets. You can add; delete; and move them as you need. In this video; Adam Wilcox demonstrates the different ways to manage worksheets in a workbook.
-
Page Layout Options
Microsoft Excel 2010 enables you to change the page layout of a workbook before printing it. You can use the Page Layout options to change the page orientation; fit data on one page; and set print titles to repeat headings on multiple pages. In this video; Adam Wilcox demonstrates how to change the page layout of a large worksheet.
-
Protect Workbooks
Microsoft Excel 2010 offers workbook protection; which allows you to add passwords and control what changes other people can make. In this video; Adam Wilcox demonstrates how to add different levels of protection to a workbook; and to the cells within a workbook.
-
Publishing Workbook Data to the Web
Microsoft Excel 2010 allows you to save your workbook as a web page. You can also maintain a connection between the workbook and the web page. In this video; Adam Wilcox demonstrates how to save a worksheet as a web page and keep the connection to the published page.
-
Recently Used File List Tips
The Recently Used file list in Excel 2010 is a way you can access the worksheets you use most often. In this video; Adam Wilcox will demonstrate how the Recently Used file list works and how to control the way it behaves.
-
Record and Run a Macro
A macro in Microsoft Excel 2010 is a set of instructions you can record and then play back to repeat the same actions on another worksheet or workbook. In this video; Adam Wilcox demonstrates how to record and run a macro.
-
Reference Types
In Microsoft Excel 2010; you can copy formulas containing relative; absolute; or mixed references from one cell to another in your worksheet. Using the correct type of reference ensures the formula works as expected in the cells you copy it to. In this video; Adam Wilcox demonstrates how to apply formulas containing different types of references to a Sales table.
-
Replacing Data and Checking Spelling
In Excel 2010; you can perform some of the same proofing tasks as you would in a word processor including finding and replacing data; spell checking; and using a thesaurus. In this video; Adam Wilcox demonstrates how to find and replace data; check your spelling; find synonyms; and search the Internet for information on a selected item.
-
Row and Column Formatting
Microsoft Excel 2010 has several options available for formatting rows and columns to improve a table’s appearance and to make it easier to view the content of a large worksheet. In this video; Adam Wilcox demonstrates how he formats a large worksheet by hiding columns; as well as splitting the window and freezing panes.
-
Save Data into Different Formats
Microsoft Excel 2010 allows you to save your workbook in a number of formats if you want to display or import it to another program. Some common formats are web pages and CSV files. In this video; Adam Wilcox demonstrates how to save an Excel workbook as a web page and a CSV file.
-
Send Files Through E-mail
You can send a workbook directly through Excel 2010 as an email attachment or as the body of an email message using your default mail application. In this video; Adam Wilcox will demonstrate how to email a worksheet or a workbook directly through Excel.
-
Solver
Solver is a Microsoft Excel 2010 add-in that lets you set a particular goal for a formula and then work backwards to find the input values that will give you that goal based on rules that you set. In this video; Adam Wilcox demonstrates how to install and use the Solver add-in.
-
Sorting Information
Microsoft Excel 2010 provides various ways you can sort information; such as alphabetically or numerically. You can also create custom sorts or randomize the data. In this video; Adam Wilcox demonstrates the different sort methods in a list of sales employees in various departments as well as their earnings.
-
Sparklines
You can use sparklines to visually represent data in Microsoft Excel 2010. A sparkline is a small in-cell chart that shows the data. In this video; Adam Wilcox demonstrates how to add and format sparklines in a workbook.
-
Tables and Filtering
You can use Microsoft Excel 2010 to easily filter data in various ways in a table created from a list of data. In this video; Adam Wilcox demonstrates how he creates a table from a list of data and then filters the content.
-
Tracing Precedents
Microsoft Excel 2010 includes auditing tools for tracing the relationships between formulas in the cells of your workbook. You can check which cells feed formulas and which cells depend on the results of formulas. In this video; Adam Wilcox uses the Trace Precedents and Trace Dependents features to trace relationships between cells in a workbook.
-
Tracking Changes and Merging Workbooks
When you share a workbook in Microsoft Excel 2010; you can track changes you and others have made to the workbook. You can also merge several versions of a shared workbook into one workbook. In this video; Adam Wilcox demonstrates how to use the Track Changes feature and merge different versions of a shared workbook.
-
Trend Lines
Trendlines in Microsoft Excel 2010 are a way to look at the data in your charts to see where it's heading. There are different types of trendlines you can use and you can format them in a range of ways. In this video; Adam Wilcox demonstrates how to add a trendline to a chart and modify it.
-
Use Comments
In Microsoft Excel 2010; you can add comments to cells or ranges to mark-up a worksheet if you want to convey extra information about the data. In this video; Adam Wilcox uses the Comments feature to add; format; and edit comments.
-
Use Names in Formulas
In Microsoft Excel 2010; you can name ranges and particular values in cells to make formulas and functions more understandable and easier to manage. In this video; Adam Wilcox demonstrates how to use named cells and ranges in formulas in a worksheet.
-
Useful Statistical Functions
In Excel 2010; you can perform some simple statistical functions based on your data and one or more conditions that you want to test. In this video; Adam Wilcox will demonstrate how to perform several useful statistical functions on spreadsheet data.
-
Using Copy and Paste
The Paste function in Microsoft Excel 2010 allows you to paste content according to your needs. For example; you might want to paste a piece of text without its formatting; or a number without its formula. In this video; Adam Wilcox demonstrates how to paste content in various ways.
-
Using Data Tables for What If Analysis
In Excel 2010; you can use data tables to show output for a function based on different input values. In this video; Adam Wilcox will demonstrate how to use the What-if Analysis for a data table to calculate values for a single variable -- interest rate; and for two variables -- interest rate and term.
-
Using Lookup Functions
You can use lookup functions in Microsoft Excel 2010 to look up a value in a table based on another value. In this video; Adam Wilcox demonstrates how to use the VLOOKUP function to look up the region to which each sales rep is assigned and add it to the relevant column in the table.
-
Printing
Microsoft Excel 2010 enables you to print your entire workbook or only a portion of the data in it. You can use the Print options to specify which data you want to print and set the page layout before printing it. In this video; Adam Wilcox demonstrates how to print a portion of the data in a Sales workbook.
-
Templates
Templates are premade forms in Microsoft Excel 2010 that can provide a lot of the labels and formulas you might need without having to set them up. You can create your own or use built-in templates in Excel. In this video; Adam Wilcox demonstrates how to open; save; and modify templates.
-
Handling Errors
Microsoft Excel 2010 makes it easy to find and fix errors in your workbooks using the Data Validation; Error Checking; Trace Error; and Evaluate Formula features. In addition; you can use functions to test for errors. In this video; Adam Wilcox demonstrates how to find; resolve; and test for errors in a workbook.
