Paste Special
- Understanding the different layers of formatting
- Pasting contents without formatting
- Using Paste Special to consolidate data
- Transposing data
- Creating a dynamic link to the source data
- Creating automatic subtotals
Protecting cells, worksheets and workbooks
- Protecting a cell or cell range
- Protecting and un-protecting a worksheet
- What can you protect against?
- Setting and un-setting a password
- Workgroup protection
- Protecting a workbook
- Encryption
- Locking down a final version of a spreadsheet
Templates
- Workbook and worksheet templates
- Saving, modifying and deleting a template
- Creating a new workbook based on a template
Formulas that link to other cells, worksheets & workbooks
- Creating formulas that reference cells within other worksheets and other workbooks
- Viewing and updating links manually Updating links automatically
Advanced Find & Replace Techniques
- Overview of Find & Replace
- Specifying where to search
- Case-sensitive searches
- Searching for and replacing a format, rather than text
- Using AutoCorrect
- Using the Go-To-Special feature
Links (formerly called Hyperlinks)
- Creating, editing and removing hyperlinks
- Linking to another file
- Linking to another place in the same workbook
- Linking to a web page
- Linking to a new e-mail message
- Activating the hyperlink from a graphic
Comments
- Creating, editing and deleting comments
- Reviewing and navigating through comments
- Moving and resizing comments
- Changing the default user name
Customising the Quick Access Toolbar (QAT)
- What is the Quick Access Toolbar?
- What can be added to the QAT?
- How to add elements to or remove elements from the QAT
- Adding a custom iconRunning a macro from the QAT
Using the Microsoft Graphic Tools
- Using the graphics ribbons
- Working with the 157 drawing shapes in the shapes gallery
- Adding and editing your own pictures
- Aligning graphics for a professional finish
- Adding glow, reflection, shadow, soft edges, 3D, and bevel effects
- Using the new and improved WordArt
- Creating a multitude of different business diagrams using Smart Art
More What If? Analysis Tools
Advanced Date & Time Functions
- Doing clever things with advanced date and time function combos including TODAY, NOW, DAY, MONTH, YEAR, DATE,
- NETWORKDAYS, YEARFRAC, TIME, HOUR, MINUTE, SECOND
- Custom date & time formats
- Incrementing a date by a day / month / year
- Calculating the difference between two dates
- Calculating the last day of the month
- Determining if the current year is a leap year
- Determining if this month is somebody's birthday, renewal date etc.
- Time as a duration (e.g. 48 hours) vs time as a time
- Calculating a duration when start and end times span midnight
- Calculating / converting duration as a decimal (e.g. 3.5 hours) vs duration as hours, minutes and seconds (e.g. 3:30)Second
Additional Database Functions
- DSUM, DCOUNT, DMAX, DMIN, DAVERAGE, DGET
Importing External Data into Excel
- Importing a CSV file
- Importing an entire or filtered Access database table
Custom Views
- What can be stored in a view?
- Creating, editing, displaying and deleting a view
Sharing Workbooks
- Advantages and disadvantages of sharing a workbook
- Allowing a workbook to be shared
- Protecting worksheets and workbooks
- Handling conflicts
- Implications of sharing a workbook
Online Forms
- Exploring the Form tools
- Adding labels
- Adding a drop-down list or list box
- Adding a spinner control
- Adding tick boxes or option buttons
- Grouping option buttons together
- Adding a scrollbar
- Showing or hiding the gridlines
- Selecting and moving controls
- Save the form so it can be used
Array Formulas
- Creating an array formula
- Understanding array rules and characteristics
- Locating and editing an array formula
- Using logic functions with arrays
- Creating a Frequency Distribution
- Creating a Histogram
