Learning Method: Instructor Led Training
Upcoming Dates: Please Call
Excel 2010: Tips and Tricks
Request This Class, or Additional Information
Usage and Navigation
Customize the Quick Access Toolbar and Ribbon Tabs
Add or remove commands to the Quick Access Toolbar for your frequently used commands.
Create and customize ribbon tabs and groups to put all your frequently used commands together. You can create a new tab of group and you can also change the order of the ribbon tabs or give them a different name.
The Ribbon in Excel 2010 comes with new shortcuts, called Key Tips. Learn how to make the Key Tips appear and how to use access common keyboard shortcuts.
Change the Worksheet Tab Color
Change the tab color for worksheets to help you find specific information in your workbook.
The Excel Status Bar
The quickest way to view data information in your worksheet is to use the Status Bar. By default the Status Bar displays the Average, Count, and Sum of cells selected. You can also customize the status bar to display other information like Minimum and Maximum.
Protecting Microsoft Excel Worksheets
Learn how to assign password protection, protect a worksheet, and protect the structure of a workbook.
Recovering Unsaved Workbooks
Open a recent workbook that was closed without saving by using the Recover Unsaved Workbooks feature.
Data Entry and Data Analysis
Office Clipboard Task Pane
The Clipboard Task Pane can hold up to 24 copied items at a time. This pane will save all information copied to the clipboard and you can select any item to paste or delete into another Office document.
Automatically Fill a Row or Column and Create a Custom Auto Fill List
Excel can automatically fill a row with data such as consecutive numbers, names of months, and weekdays, when you type in the first few items in the row by using the Fill Handle. In addition you can also create a Custom Auto fill List if you have a series of words or names that you frequently enter.
Use the Fill Handle to copy formulas in your worksheet.
Find and Replace Data
Find and Replace allows you to edit specific contents across a workbook.
Sort arranges rows in a list in a specific order. A list is a set of data in continuous columns and rows.
Prevent invalid data from being entered into a cell by using the Data Validation feature. The Data Validation feature allows you to specify a list of the values that can be accepted into a cell. You can also force input to be chosen from a drop down list of values you specify. Use the Error Alert tab to show an error alert to a user when invalid data is entered.
Locking Columns and Rows
Freezing highlighted panes in the spreadsheet so that they remain visible while you enter data further down the page can be handy when you need to compare facts and figures. You can also split panes into multiple worksheets so that you can scroll in one pane while information displayed within the other remains static.
View Multiple Sheets Side by Side
View two worksheets side by side so that you can compare their contents. Use Synchronous Scrolling to synchronize the scrolling of the worksheets.
Split View Feature
Use Split to split the window into multiple resizable panes containing view of your worksheet. You can use the feature to view multiple distant parts of your worksheet at once.
Apply cell styles, use AutoFormat, and number formats.
Format worksheets using the Font, Alignment, Number and Cells groups.
Use the Format Painter to copy formatting from one place and copy it to another.
Apply the Same Formatting and Data to Multiple Sheets
Apply the same formatting and/or data to multiple sheets at one time by grouping multiple sheets. Any content or formatting done on one sheet in the grouped sheets will be applied to all the other sheets. Ungroup sheets when completed.
Working with Shapes
Learn how to insert, resize, move, group, and format shapes into your worksheet.
Screenshot lets you capture a picture of all or part of an open window and place it in your Excel document.
Conditional formatting allows you to format data meeting specified criteria to display with particular emphasis, making it easy to find.Apply conditional formatting using the Gallery Tool, using a New Rule, and learn how to remove conditional formatting.
Formulas and Functions
Name Range and Using a Name Range in a Formula
The Name Range feature will let you specify the name of a datasheet portion and then use it in a formula. This makes the use of formulas easier so that you do not have to remember cell location. You can then use a named range in a formula in Excel.
Convert an Excel Formula Result into Static Text
Keep the result of a formula and discard the original data that went into the formula result.Learn how to use the Paste Values feature.
Filtering is a screening process that excludes items not essential for the result. When you filter a list in Excel, you temporarily hide all the rows that do not meet the criteria.
Create a SmartArt Diagram
Use SmartArt to quickly and easily create simple and complex diagrams that communicate text in a graphical format.
Charts and Graphics
Creating Charts and Graphics
With Excel, you can create colorful pictures and graphs or generate appealing reports in minutes that help you identify usage or spending patterns at a glance. There are a variety of ways to represent data such as area, bar, column, line, and pie charts.
Sparklines are tiny charts in a worksheet cell that provide a visual representation of data. Taking up a small amount of room, a sparkline can display a trend based on adjacent data in a clear and compact graphical representation.
Use the Align feature to align the edges of multiple selected objects.
Use the Selection Pane feature to find graphic objects and change their visibility and order. Use this feature to hide, rename, reorder, and select graphics.
Working with Macros
When you record a macro, you have the option of storing it in the current workbook, a new workbook, or in the Personal Macro Workbook.
Macros allow you to replay a series of events, such as keystrokes or mouse clicks.
Assigning Macros to a Menu or Button
With Excel, you can customize the Quick Access Toolbar by assigning a created macro to the toolbar button or shape.