Excel Simplified - 101 Tips
Created by Lisa Newton, Last Updated 28-Oct-2020, Language:English
Excel Simplified - 101 Tips
101 quick tips and tricks to master Excel 2007.
Created by Lisa Newton, Last Updated 28-Oct-2020, Language:English
What Will I Get ?
- Work with Formulas and Functions
- Copy data and formats
- Use excel lists
- Create charts
- Present worksheets nicely
- Save and print worksheets
Requirements
- Students will need to have Excel 2007 installed, as this is the system used in the teaching
Description
This course is aimed at the excel user who already knows the basics of Excel 2007. This course will help you to go beyond the basics - to reach a higher-intermediate level. With shortcuts, tricks and tips - you will be able to work smarter and faster.
If you want to be fairly competent on the software, then this course will be very handy. Its a lot quicker to be shown things, then to try and muddle through and work things out by yourself. Guaranteed, there'll be some items which we cover, that you have no idea that Excel was capable of doing!
We don't want you to spend a day of your life in the classroom... learn 99 quick and very useful, practical things which you can apply on your job or on your projects.
We'll cover:
- Working with Data - using series, symbols and special characters, hide rows and freeze panels
- Formulas and Functions - Calculate the duration between two dates/times, best loan terms, create conditional formula and conditional sums
- Copying Data - transposing rows into columns and paste specials
- Using Excel lists - sort and filter a list, remove duplicate records, count filtered records, look up information in a list
- Data Patterns - Pivot tables, pivot charts, what-if analysis
- Creating charts - histogram, trendlines, piecharts, error bars
- Presenting data - formatting columns and numbers
- Saving and printing worksheets - printing multiple worksheets, area, cell ranges, repeat headings of a row or column
- Extending excel - hyperlinks, embed a chart, importing a worksheet
- Customizing Excel - custom workspace, custom view, macros
The target audience is those who have a basic level with Excel and want to learn other handy functions and features. We use Excel 2007 only in this course. If you have Excel 2003, this course will be difficult to follow as although the functions and features exist in 2003, the layout changed very dramatically between the two versions. Excel 2007 isn't too dissimilar from 2010.
Where necessary, we provide a spreadsheet - but as long as you have Excel 2007, you'll be able to copy and do exactly what you see on the screen by pausing the video and following along.
The course will take approx 7.5 hours to complete.
Take this course if you want to take your basic understanding of Excel to a higher intermediate-level.
Course Content
-
Introduction
1 Lectures 00:02:47-
Introduction
Preview00:02:47
-
-
Data
11 Lectures 01:07:12-
Enter numbers and fractions
00:05:00 -
Dates and Times
00:07:46 -
Name Cells and Ranges
00:03:45 -
Validate Data entry using a pick list
Preview00:07:34 -
Extend a series of dates with autofill
00:04:18 -
Add a symbol or special character
00:03:56 -
Compare multiple spreadsheets using freeze panels
00:06:08 -
Hide rows
00:04:24 -
Keyboard
00:09:58 -
Speak Cells
00:03:48 -
Find and replace data
00:10:35
-
-
Formulas and Functions
18 Lectures 01:11:09-
Add values
Preview00:03:08 -
Function wizard
00:06:07 -
Formulas with comments
Preview00:04:32 -
OneNote
00:05:43 -
Define a constant
00:04:01 -
Apply names in functions
00:04:23 -
Figure out the best loan terms
00:04:23 -
Internal Rates of Return
00:04:16 -
Nth largest value
00:03:53 -
Large, Small, Max, Min
00:04:38 -
Conditional formula
00:02:59 -
Conditional formula with names
00:03:58 -
Count If
00:02:02 -
Conditional sum
00:02:53 -
Inner calculator
Preview00:02:32 -
Square Roots
00:03:06 -
Calculate the duration between two times
00:04:07 -
Calculate days between two dates
00:04:28
-
-
Copying Data, Formats etc.
6 Lectures 00:31:53-
Copy a range
00:03:01 -
Transpose a row into a column
Preview00:01:40 -
Copy styles to another workbook
00:07:29 -
Chart formatting
00:07:14 -
Paste special and copying worksheets
00:06:26 -
Track changes while Editing
00:06:03
-
-
Lists
13 Lectures 00:52:46-
Enter list data using a form
00:04:39 -
Searching through a data list
00:04:15 -
Import a word list into excel
00:04:36 -
Sort a list
00:01:58 -
Filter a list
00:02:37 -
Sort by multiple criteria
00:02:58 -
Find averages in a sorted group
00:05:07 -
Filter by multiple criteria
Preview00:03:07 -
Remove duplicate records from a list
00:03:03 -
Count filtered records
00:06:51 -
Filter by multiple criteria in the same column
00:06:03 -
Chart a filtered list
00:02:24 -
Look up information in a List
00:05:08
-
-
Data Patterns
13 Lectures 00:42:41-
Create a PivotTable
00:05:29 -
Modify a PivotTable and layout
00:02:53 -
Find the average of a field
00:03:57 -
Create a calculated field
00:01:55 -
Hide rows and columns in a PivotTable
00:01:59 -
AutoFormat a PivotTable
00:01:14 -
Create a PivotChart
00:03:01 -
Turning on the Data Analysis function so that statistical information can be run
00:02:27 -
Describe Data with Statistics
00:02:59 -
Discover associations within your data
00:03:30 -
Product Numbers
00:01:46 -
What-if analysis
00:05:11 -
Goal seek
00:06:20
-
-
Creating Charts
14 Lectures 00:55:22-
Create a Chart
00:05:42 -
Modify Chart details
00:04:02 -
Change the Chart Type
00:03:36 -
Add a Trendline to a Chart
00:06:19 -
Remove Chart Data
00:01:53 -
Add chart data
00:02:05 -
Missing chart data
00:04:14 -
Error bars
00:03:55 -
Pull a slice from a Pie Chart
00:03:04 -
Label slices of a Pie Chart
00:04:01 -
Histogram
00:06:21 -
Paste a chart into Word
00:03:26 -
Amending a chart in Word
00:02:04 -
Paste-link a Chart into Word
00:04:40
-
-
Worksheets
6 Lectures 00:20:33-
Format Numbers as Percentages
00:03:41 -
Format Currencies in Currency and Accounting Formats
00:03:24 -
Format Column Headings
00:05:07 -
Format Quickly with Format painter
Preview00:02:25 -
Insert a background image into your worksheet
00:02:48 -
Create a Transparent image
00:03:08
-
-
Saving and Printing Worksheets
8 Lectures 00:43:05-
Save a workbook as a Template
00:06:50 -
Save a workbook as an XML spreadsheet
00:05:02 -
Print multiple cell ranges on One Page
00:03:04 -
Page set up, header, footer, margins - 1 page printing
00:04:24 -
Print multiple areas of a workbook
00:07:56 -
Print multiple worksheets of a workbook
00:01:19 -
Repeat Headings of a Row or Column
00:07:11 -
Print functions to show calculations & comments
00:07:19
-
-
Extending Excel
6 Lectures 00:34:51-
Hyperlink a worksheet to another office document
00:02:48 -
Embed a chart within PowerPoint
00:05:46 -
Publish an Interactive Workbook
00:05:05 -
Importing data from a Web Site or Access Database
00:02:26 -
Import a Worksheet into Access
00:08:56 -
Use Excel Data To Create Labels In Word
00:09:50
-
-
Customizing Excel
5 Lectures 00:25:24-
Launch a specific Workbook when Excel Opens
00:03:38 -
Save Time by Creating a Custom View
00:03:20 -
Create a Custom Number Format
00:06:11 -
Changing Text to Columns
00:05:28 -
Create a Macro to Format Numbers
00:06:47
-

Lisa Newton
Entrepreneur, Infopreneur & Serial Author
Lisa Newton BA MSc AAT has a masters degree in Investment Management and a first class honours degree in Accounting with Marketing. She formed her first company in 2004 with £150 in the same month of graduating from City University, London UK. Lisa has never had a full time 9 to 5 job in her life. She's a serial entrepreneur and author who holds directorships in various industries including telecoms, property management, publishing, hair & beauty as well as finance. She has won various awards in Business including: Young Entrepreneur of the Year Award 2007, in 2008 Enterprising Business Award, in 2016 BE Mogul Award and has been nominated and shortlisted in numerous others. In 2011 and in 2012 one of her companies won Best Accounting Franchisor Award. In 2015 and 2017 one of her companies won Best Bookkeeping Business London (Accountancy International Awards).