Excel 2010 Superhero Course

Awesome skills and techniques to make you an Excel Superhero
4.9 (5 ratings) Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
132 students enrolled
$19
$70
73% off
Take This Course
  • Lectures 97
  • Length 8.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 11/2013 English

Course Description

Become an Excel superhero. Learn the skills to be able to tackle any Excel problem.

This course starts with the basics of formulas and managing spreadsheets and quickly moves to advanced Excel techniques required for creating powerful dynamic and visually appealing spreadsheets.

It covers;

  • Using PivotTables to summarise large amounts of data.
  • Over 30 different Excel functions to handle any problem including INDEX, AGGREGATE, VLOOKUP, LEN, MID, SUMIF, MATCH, and INDIRECT.
  • Visualising results with charts.
  • Cleaning and preparing data for analysis.
  • Conditional Formatting to highlight data comparisons, progress and problems.
  • Solving formula errors.
  • And much much more.

You will receive;

  • Support from the Computergaga team
  • Lifetime access
  • Exercises throughout to re-enforce the content
  • Fun and no-nonsense lessons

Don't delay. Superhero's take action. Grab a coffee and let's get started. Your journey towards becoming an Excel superhero starts today.

What are the requirements?

  • A basic understanding of Excel. This course begins from around mid-introduction level.

What am I going to get from this course?

  • Learn some incredible Excel skills and techniques
  • Learn the most popular Excel formulas in business (over 30 covered in the course)
  • Be prepared to handle any Excel problem

What is the target audience?

  • Those who want to be amazing at Excel and are willing to accept the responsibility of being an Excel superhero.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction
Course Intro
Preview
Article
Section 2: Working with Windows
05:14

In this lecture we look at the Freeze Panes feature to fix parts of your worksheet to ensure they are visible at all times. This is typically used for column headings so that you can see what data relates to as you scroll down the worksheet. This tutorial covers;

  • Freezing the column headings in a worksheet
  • Freezing rows and columns in a worksheet at the same time

Applying Freeze Panes is essential for large worksheets to make them easier to navigate and work with.

05:48

Splitting the panes of a worksheet allows you to reference different areas of the worksheet at the same time. The lecture covers;

  • Splitting panes to make comparing records in a large worksheet easier
  • Using a shortcut to split panes in one movement of the mouse
  • Using split panes as a predecessor to freezing panes for a more visual approach
05:09

If you need to work on multiple Excel files at the same time, you may benefit from making them all visible on screen at once. Excel provides features to easily do this and save it for long term use. This tutorial covers;

  • Arranging multiple windows on screen at the same time
  • Understanding the 4 different ways of viewing multiple workbooks on screen
  • How to return to the single window display
  • Saving the window arrangement as a workspace to use long term
Section 3: Sorting and Filtering Records
07:31

Lists in Excel can be very large. In fact Excel worksheets can contain as many as 1,048,576 rows of data. Managing a list of this size is very important.

Sorting enables you to easily change the order of a list. A list could be sorted in date order, or alphabetical ,or even by the order amount as largest to smallest.

This tutorial covers;

  • The fastest way to sort a list in Excel
  • Sorting lists by different data types such as date, number or text
  • A common problem when sorting records
  • Sorting a list of records by multiple levels
11:48

The primary way of managing and analysing a large list of data in Excel, is to filter the list. Filtering records in Excel is easy, yet incredibly powerful and versatile.

A list can be filtered to only show the necessary records ready for printing, exporting or to view aggregate totals.

This tutorial covers;

  • Filtering a list of records by a text column
  • Filtering a list of records by multiple options in a text column
  • Filtering a list by a date column
  • Filtering a list of records by a numerical column
  • How to instantly recognise a filtered list when opening a spreadsheet
  • Quickly removing all the filters from a table and resetting it to normal
  • Viewing totals for a filtered list such as the sum, count or average for a column
04:48

In addition to sorting and filtering records in a list by value. You can also sort and filter by the colour of the cell or font.

This technique works awesomely well with Conditional Formatting (covered later in this course) as a way of identifying and then working with records that meet certain criteria.

4 questions

Open the Sorting and Filtering.xlsx file from the previous tutorials and sort and filter the records to answer the following questions.

Section 4: Getting Started with Formulas
09:17

Get started with learning to write formulas in Excel. Formulas are the foundation of Excel's incredible power. To become an Excel superhero, you must be confident in being able to learn and work with any formula.

This tutorial covers;

  • Writing formulas using basic arithmetic (addition, subtraction, multiplication and division)
  • Referencing cells and using constants in a formula
  • The rule of BODMAS that determines the order of calculation
05:29

When writing formulas it is common to need to calculate percentages. For example you may need to calculate a discount for a client, add VAT to a product price or calculate a 20% tax savings. This tutorial covers;

  • Calculate the % of a value
  • Increase a value by a certain percentage
  • Decrease a value by a certain percentage
  • Calculate what % one value is of another
08:19

Excel has over 430 functions in its library. A function is a built-in formula. Knowing how to write your formula is a very important step on the way to becoming an Excel superhero, but most of the time you will be working with the built-in functions of Excel.

This tutorial covers;

  • The 5 aggregate functions of Excel (SUM, COUNT, AVERAGE, MAX and MIN)
  • Understanding function syntax making you ready for any function
06:00

When referencing a cell from a formula you are using relative cell references. This ensures that when you copy the formula, the reference moves with you. Dollar signs are used to fix a cell reference, making it an absolute reference. This tutorial covers;

  • An example where absolute cell references are used
  • A shortcut to changing the cell reference type
  • An introduction to mixed references
09:42

Creating a defined name can provide an easier way of referencing cells in an Excel workbook, especially when referencing across multiple sheets. It provides a meaningful name that is unique on that workbook.

It can be recalled quickly when writing formulas and also used to select ranges quickly, making it awesome with larger lists.

This tutorial covers;

  • The benefits of using defined names
  • How to create a defined name
  • Naming conventions and a common mistake when naming ranges
  • Using a defined name to quickly select a range
  • How to view, edit and delete existing defined names
  • Using a defined name when writing a formula
05:54

The COUNTA and COUNTBLANK functions are two variations of the COUNT function covered earlier in this course. The COUNT function only counts numbers within a range. COUNTA is used to count all the non-empty cells with a range, and the COUNTBLANK is used to count the empty ones.

This tutorial covers;

  • Ways of finding a function when needed in a given situation
  • An example of the COUNTA function being used
  • An example of the COUNTBLANK function being used
08:47

The Excel function wizard can be used to assist you in writing a formula. It provides explanations for the parts of the function, results for the information your providing and will also make minor corrections to mistakes.

This tutorial covers;

  • The COUNTIF function
  • Using the function wizard to help find the correct function to use in a given situation
  • Using the function wizard to troubleshoot and correct a mistake in our formula
Exercise 1: Absolute and Mixed References
Article
Exercise 2: Writing Formulas
Article
Exercise 3: Using Functions
Article
Section 5: Using Logical Functions
09:12

The IF function is used to test a condition and then take an alternative action for whether the condition is met or not. For example, if a customer orders over a £500 then apply a 10% discount, otherwise do not.

It is a logical function of Excel, and is sometimes referred to as a conditional function or a decision making function.

This tutorial covers;

  • How to write the IF function in Excel
  • Understanding the functions syntax
  • An example applying a discount to a customer if they order over a certain quantity
  • An example showing testing a cell contain text before applying a discount
  • An example that tests to see if a date has passed, and then takes a different action whether it has or not
Article

The AND and OR logical functions of Excel are used to test multiple conditions. They are commonly used with various Excel features including the IF function to perform more advanced decision making.

Any Excel function can be nested within another for more powerful calculations.

This tutorial covers;

  • An explanation of the AND and OR logic and the syntax of the two functions
  • Examples of the AND and OR functions in use
  • An introduction to the art of nesting functions in Excel
16:34

As awesome as the The IF function is, it can only test one condition, and perform one action if the test is true and another if the test is false. If we need the function to perform a more advanced condition, or perform multiple actions we will need to embed other functions for these tasks. This is a technique known as nesting functions.

This tutorial covers;

  • Nesting the AVERAGE function within the IF function
  • Viewing and changing formula error checking options
  • Adding the AND function to the formula to test multiple conditions
  • Adding the OR function to the formula for more complex conditional testing
  • How you can test parts of a formula in isolation. Brilliant technique for troubleshooting formula errors and for understanding how a formula works
Exercise 1: Using the IF Function
Article
Exercise 2: Nesting Functions
Article
Section 6: Using Lookup Functions
12:18

The VLOOKUP function is a lookup and reference function. It searches for a record of data in a list and returns information from that record. For example, it could search a list of orders and return the value of an order that you ask for.

VLOOKUP stands for a vertical lookup. It gets this name because it looks down columns.

This tutorial covers;

  • An explanation of the VLOOKUP function and when you would use it
  • Learning the syntax of the VLOOKUP function
  • Using the VLOOKUP function to return the price and units in stock for a particular product in a list
  • A recap on the use of an absolute cell reference
  • A demonstration of the 'Range Lookup' argument
06:16

The VLOOKUP function is commonly used to find unique entries such as an order, employee or service and return information about that entry.

However, it can also be used to find data within a range. To do this the final argument of the VLOOKUP function known as 'Range Lookup' needs to be omitted or set to True.

This tutorial covers;

  • A detailed explanation of the 'Range Lookup' argument
  • An example of the VLOOKUP function returning exam grades by setting range lookup to True
  • Using the Function Wizard for assistance in understanding a function
05:47

The HLOOKUP function is used to search for a record of data along a row and return information about that record from a different row. For example, it could search a list of employees and return the start date of the one you search for.

This tutorial covers;

  • An explanation of the HLOOKUP function and when you would use it
  • Learning the syntax of the HLOOKUP function
  • Using the HLOOKUP function to return the price and units in stock for a particular product in a list
08:55

The MATCH function can be used to return the column index number for the VLOOKUP function. By nesting the MATCH function within the column index number of VLOOKUP, the function can automatically find the necessary column number, making the function more flexible and durable.

This tutorial covers;

  • An understanding of the MATCH function
  • The benefits of using it with VLOOKUP
  • How to use MATCH to return the column index number
08:17

The LOOKUP function is more versatile than VLOOKUP. It looks in and returns from ranges you select. It can be used to return data from a column to the left of the column we are searching in.

This tutorial covers;

  • The differences between LOOKUP and VLOOKUP
  • Understanding the LOOKUP syntax
  • Using the LOOKUP function to return data from a list
09:06

The INDEX and MATCH functions together provide an extremely flexible lookup formula. Look for a record or entry in any column of a table and return related information from a different column of that table. This formula solves some of the limitations of VLOOKUP.

This tutorial covers;

  • The purpose of the INDEX and MATCH functions individually
  • Combining the two functions together for a flexible lookup formula
  • Returning information about an order from a list of orders on a different worksheet
  • Using MATCH to find the record and find the column containing the data to return to create an extremely flexible formula that users can interact with
09:04

The #N/A error message is returned when functions such as VLOOKUP and MATCH cannot find what you asked them too. This message can be hidden if required, or an alternative action taken such as looking on a different sheet for it, or displaying different text such as “Not Found”.

This tutorial covers;

  • Using the ISNA function to detect the presence of a #N/A error
  • Combining this with the IF function to take the appropriate action
  • Displaying text in the cell instead of the error
  • Making the lookup cell empty is nobody has asked to look for anything
Exercise 1: Lookup Functions
Article
Section 7: Protecting and Sharing Excel Files
03:51

If a workbook is saved to a shared drive you may want to prevent unauthorised access to the file. Encrypt the file with a password to ensure that only authorised personnel may open the file.

04:18

A workbook can be protected to lock the structure and/or windows of a workbook.

Protecting the structure locks sheet related activity such as users unhiding, renaming, deleting or inserting sheets. Protecting the windows prevents users from using features such as Freeze Panes and Arrange All.

This tutorial covers;

  • Hiding a worksheet
  • Applying workbook protection to prevent users from unhiding the worksheet
08:22

Worksheet protection can be used to protect different elements of a spreadsheet. It can be used to protect your formulas and prevent users from unhiding columns among other things.

This tutorial covers;

  • Unlocking the cells of a spreadsheet you do not want to protect
  • Setting the cells containing formulas to hidden
  • Protecting the worksheet with a password
  • A demonstration of the improved usability of a protected worksheet
05:17

Excel spreadsheets have exclusive access by default. This means that only one person at a time can modify them. Sharing a workbook will allow multiple people to edit a spreadsheet at the same time.

This tutorial covers;

  • Sharing a workbook so more than one user can open it at the same time
  • Viewing what users have that spreadsheet open
  • Modifying the settings for workbook sharing
  • Setting a workbook back to exclusive access
Section 8: Using Conditional Formatting
07:25

Apply Conditional Formatting to your spreadsheets to format cells based on a condition. For example, you could format cells red if a deadline is missed, a task is due to start, or a target is achieved.

This tutorial covers;

  • The different types of rules you can create using Conditional Formatting
  • Formatting the cells that contain a date in the past
  • Adding a second conditional format to format the cells if the date contains is within the next 2 weeks
06:56

Data bars and icon sets are very visual indicators that can be applied to bring our attention to values that meet certain criteria.

This tutorial covers;

  • Applying data bars to a range to display a nice visual comparison of the values
  • Using colour scales on a range of cells to visualise the different between the values in that range
  • Creating a conditional format rule from scratch
  • Apply an icon set to display a green tick on all cells where the value is 65% or higher
08:18

If you are going to be using conditional formatting on a spreadsheet you will need to be able to manage the rules. Excel makes it easy to be able to move, edit or delete conditional formatting rules that exist on a spreadsheet.

This tutorial covers;

  • Viewing all the conditional formatting rules on a worksheet
  • Editing an existing rule
  • The importance of the order that conditional formatting rules run
  • Clearing rules that are no longer required from the selected cells, or the entire sheet
  • Using the Format Painter to copy conditional formatting rules between cells
04:09

Applying conditional formatting to individual cells is fantastic, but if your data was quite wide, formatting the entire row will make it more visible.

To format the entire row we will need to create the rule a little bit different to the way we have been doing.

This tutorial covers;

  • The benefits of formatting the entire row
  • Using a formula for a conditional formatting rule
  • Using a mixed reference in the formula
05:11

By using a formula for your conditional formatting rules, you can create some extremely powerful formatting rules. By writing formulas you can create rules that Excel has not provided for you already.

This tutorial covers;

  • Creating a conditional formatting rule using a formula
  • Using the AND function
  • Formatting the entire row
Exercise 1: Conditional Formatting
Article
Exercise 2: Highlight Unmatched Items
Article
Section 9: Validating Data Entry
03:06

If certain text entries need to be a specific number of characters, you can apply validation to a prevent entries that are not that particular text length.

This tutorial looks at validating data entry to be exactly 5 characters in length.

02:58

You can validate date entries in Excel to ensure that they are within a certain range or only a date that occurs this week etc.

This tutorial covers;

  • Setting a data validation rule to prevent the entry of future dates
  • Using the TODAY function for validating date entries
04:15

Drop down lists provide an easy and user friendly way for people to enter text data. This ensures that the data is valid. Entries that do not appear in the list will not be allowed.

This tutorial covers;

  • Creating a data validation list by entering the values yourself
  • Creating a data validation list by referring to a range of cells on another sheet
  • Using the drop down list to enter data
05:11

Messages can be created to assist the user with their data entry. They can be used to warn the user of problems and explain common data entry issues.

This tutorial covers;

  • Setting up input messages to inform the user of how the data should be entered
  • Setting up error alerts to prevent incorrect data entry and explain where they may have gone wrong
  • Change the style of the error alert to warn the user, but not prevent them from entering the data
06:11

You can create a drop down list that is dependent upon the selection made in another list. By doing this a large list can be shortened by only displaying the relevant options.

This tutorial covers;

  • Creating a drop down list of products which is dependent on what product category was chosen
  • Using the INDIRECT function
  • Using defined names in a workbook
Exercise 1: Validating Entry of Dates
Article
Exercise 2: Validating Entry of Values
Article
Section 10: Analysing Data
07:34

The subtotals feature of Excel enables you to insert subtotal rows at particular points within a list. A selection of different aggregate functions can be used for the subtotal rows including sum, count and average.

This tutorial covers;

  • Applying subtotals to a list to sum values by each salesperson in the list
  • Using the outline buttons to view the totals at different levels of the outline
  • Applying multiple subtotals
04:48

The SUMIF function will enable you to sum all the values in a list dependent on a condition. For example, sum all the orders for a specific product, or for a particular month.

This is possibly the most popular analysis function of Excel. For counting and averaging, the COUNTIF and AVERAGEIF functions can be used respectively.

This tutorial covers;

  • Writing a SUMIF function to sum all the order amounts where the order was by a certain salesperson
  • Using a drop down list to make the formula interactive
04:58

The SUMIFS and COUNTIFS functions can be used to sum and count values based on multiple conditions.

This tutorial covers;

  • Understanding the SUMIFS and COUNTIFS functions syntax
  • Using them to sum or count the order amounts for orders by a particular salesperson, and for a particular product category
05:29

The SUBTOTAL function can be used to perform a function on a filtered list. The SUBTOTAL function will ignore the hidden rows on filtered lists. Functions that SUBTOTAL can perform include sum, count, average and more.

This tutorial covers;

  • Using the SUMIFS and COUNTIFS functions to find the total and count of orders in a list
  • Filtering the list of orders to see the functions working
Exercise 1: Applying Subtotals
Article
Exercise 2: Using SUMIFS
Article
Section 11: Working with Charts
09:00

Column charts are used to compare values across different categories. They are probably the most common type of chart.

This tutorial covers;

  • Creating a column chart to compare profit between different months
  • Formatting some of the common elements of a column chart
  • Adding data labels to display the values on the chart
  • Removing elements from a column chart
06:33

Pie charts are used to display contributions to a total. They can only handle one series of data and the values must be positive.

This tutorial covers;

  • Creating a pie chart to display the sales of 5 different categories of product
  • Editing elements of a pie chart such as the title and the legend
  • Working with data labels in a pie chart
  • Separate one of the data points (pie segments) from the chart
05:01

A combination chart enables you to display two different chart types in one chart. They can be used when your charts source data contains different data types. A secondary axis can be created allowing the two different data types to coexist on one chart.

This tutorial covers;

  • Creating a column chart for subject grades formatted as a percentage, and attendance formatted as a large integer value
  • Plotting the attendance data series on a secondary axis
  • Changing the chart type for the attendance data series
  • Modifying the scale of the axis of the combination chart to provide more detail
03:58

If you find yourself creating the same chart regularly, it will benefit you to save a chart template.

The chart template enables you to save the design elements of a chart. Next time you need it, just select your data and choose your chart template.

This tutorial covers;

  • Saving a chart template for future use
  • Creating a new chart using your saved chart template
05:35

Dynamic data labels allow you to use data from cells that you specify for your data labels. This means that you can display any data you wish for your data labels, and not just the standard chart options.

This tutorial covers;

  • Creating a line graph to display profit over a period of time
  • Setting your data labels to display the values from cells on your spreadsheet
  • Using the data labels to display the % difference of the profit between each month
12:21

Make a chart interactive so that users can select what information they want to see in the chart. This can help save space on a spreadsheet as you would not need many separate charts.

This tutorial covers;

  • Creating an interactive chart so that users can select what is displayed from 3 different options
  • How to show the Developer tab on the Ribbon
  • Adding a combo box form control to a worksheet
  • Setting the combo box parameters
  • Using the INDEX function and ROW functions
  • Creating a line graph
  • Moving a chart between worksheets
04:13

A Sparkline is a small chart that fits inside a single cell. It’s a great technique for reporting dashboards as it enables us to visualise data whilst preserving space on a worksheet.

This tutorial covers;

  • Inserting a Sparkline to compare data across different months
  • Formatting the Sparkline
  • Changing the Sparkline chart type
Section 12: Formatting a Range as a Table
08:10

Formatting a range of cells as a table creates dynamic and structured references. References to data stored in a table are more meaningful than that stored in standard cell references. This makes your formulas and other techniques more readable.

This tutorial covers;

  • The benefits of using tables
  • Formatting a range as a table
  • Naming a table to make referencing it easier
  • Exploring some formatting options for the table
  • Quickly selecting tabled data
06:18

When you reference data stored in a table, Excel uses structured references. These are more meaningful, easier to use and more durable than standard cell references such as A2. The benefits are increased if you need to reference between the sheets of a workbook.

This tutorial covers;

  • Writing a formula referencing tabled data to calculate VAT on an order
  • Learning how to writestructured references
  • Using the SUMIF function to demonstrate how easy tables can make cell referencing
02:05

Tables are a brilliant feature of Excel, especially for use with formulas. However there are a few features that do not work with tables directly. Subtotals and Data Validation are two such features.

This tutorial covers converting tabled data back to a range.

Section 13: Working with PivotTables
06:44

PivotTables make it easy to summarise large lists of data and drill to details. They make complicated lists of data readable and make an awesome reporting tool.

This tutorial covers;

  • Creating a PivotTable from a list formatted as a table
  • Displaying different fields in the PivotTable
02:40

You can sort PivotTable data to order it the way you would like. Sorting PivotTable data is similar to way we sorted lists at the start of this course, but with a little difference.

This tutorial covers;

  • Sorting the fields of a PivotTable
  • Sorting a PivotTable by the different levels of its outline
04:11

By default, PivotTables sum the values that you ask it to calculate. They also remove the formatting of the values. Fortunately we can change the formatting and the function that our PivotTable uses.

This tutorial covers;

  • Changing the function used to calculate the values in the PivotTable
  • Formatting the values of the PivotTable
  • The difference between formatting the field and the cells
04:42

You can group any numerical field in the PivotTable. Grouping fields enables you to summarise data at the level that you want.

The most popular use of this is to group date fields into specific time periods such as years, quarters or months.

This tutorial covers;

  • Displaying a date field in the PivotTable
  • Grouping a date field into years and months
  • Using the column labels area of the PivotTable
  • Using new fields created by the PivotTable as a result of our grouping
04:59

PivotTables are typically used to summarise your values using an aggregate function such as sum, count or average.

However you can ask a PivotTable to show your values as a percentage or as the difference of another field or previous value. There are lots of possibilities for how you can present your results.

This tutorial covers;

  • Showing values as a percentage of the column total
  • Showing values as the percentage difference from the previous value
  • Showing values as a running total
04:15

PivotTables have two types of filtering. They allow you to filter by each field, and by the table.

This tutorial covers;

  • Filtering by the fields included within the PivotTable
  • Applying the report filter
  • Filtering a PivotTable by multiple criteria for powerful reporting
04:32

Although PivotTables are linked to the data used to create them, they do not update automatically when data is changed. PivotTables need to be refreshed to update them.

This tutorial covers;

  • Refreshing a PivotTable, or all the PivotTables in the workbook
  • A demonstration of refreshing a PivotTable to include new orders added to the bottom of the data source
  • Setting the option to refresh a PivotTable when opening the file
  • Viewing the option to enable show details
04:01

Create a PivotChart to visualise the data that has been produced by the PivotTable.

PivotCharts are just like normal charts. They are based on a PivotTable so therefore chart more complicated data. The other key difference is that a user can interact with them directly to produce different reports.

This tutorial covers;

  • Creating a column chart from a PivotTable
  • Changing some basic formatting options on the PivotChart
  • Filter the data in the PivotChart
  • Moving a PivotChart onto a different sheet
05:04

A Slicer is a fast, visual and interactive way to filter the data in a PivotTable. A Slicer can be connected to more than one PivotTables, so that a click of a button can filter multiple PivotTables across the workbook.

This tutorial covers;

  • Naming PivotTables
  • Inserting and formatting a Slicer
  • Connecting a Slicer to multiple PivotTables
Exercise 1: Using PivotTables
Article
Exercise 2: Using Slicers
Article
Section 14: Importing Data into Excel
06:06

Data can easily be imported from a text file into Excel. A text file is typically created by exporting data from some other system.

The columns of a text file are typically separated by a delimiter character. Excel will use this character to know how to structure the data across columns.

This tutorial covers;

  • Importing data from a text file
  • Using the Text Wizard
05:30

Import data from a table or query in an Access database into Excel.

After importing the data, a connection is established. This means that there is a link between the spreadsheet and the Access table or query. The connection can be refreshed to update the information in the spreadsheet.

This tutorial covers;

  • Importing data from a query in the Northwind database
  • Managing the existing connection with the query
  • Naming the table of imported data
  • Refreshing the connection to the query
07:02

As more and more information is stored online, it is useful to know how to import data from a webpage.

Depending on the structure of the webpage, you may be able to import a particular part of that page. A connection is established and can be refreshed to update the information in the spreadsheet.

This tutorial covers;

  • Importing data from a webpage on the Internet
  • Modifying the properties of the data connection
  • Managing all existing data connections in a spreadsheet
  • A neat shortcut for maximising windows
Exercise 1: Import from Access
Article
Exercise 2: Import from a Text File
Article
Section 15: Cleaning and Preparing Data
03:46

The Find and Replace feature is a very powerful tool for making large scale alterations to content or formulas on a spreadsheet.

This tutorial covers;

  • Understanding what the Find and Replace tool can do for you
  • Replace the “.” with a “/” in a list of dates to re-format them
  • Some trusty keyboard shortcuts
03:28

Text to Columns can be used to split a cell’s content into multiple columns. This tool can help you organise poorly structured data that you have been sent, or imported.

This tutorial covers;

  • Inserting columns to prepare for new data
  • Using the Text to Columns feature to split an employee’s first name and last name into separate columns
  • Re-visiting the Text Wizard
03:14

The CONCATENATE function can be used to join together text from multiple ranges.

This tutorial covers;

  • Using CONCATENATE to join an employee’s first name and last name together from two different cells
  • Inserting your own text strings into the function
10:49

Excel provides over 25 text functions to help you work with text entries in a spreadsheet. Three functions called LEFT, MID and RIGHT can be used to extract text characters from a cell, and insert them into a different column.

This tutorial covers;

  • Using the LEFT function to extract characters from the beginning of a cell
  • The TRIM function to remove any spaces from the start and end of a cell
  • Using the RIGHT function to extract characters from the end of a cell
  • Using the MID function to extract characters from the middle of a cell
  • A function called LEN that returns the number of characters in a cell (length)
  • Determining the starting position and/or number of characters for the MID function by being clever in our formulas

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Alan Murray, Founder of Computergaga

Alan Murray is a Microsoft Office trainer and consultant. He has been training and consulting for the past 15 years for businesses around the world.

He is the founder of Computergaga and regularly writes on the Computergaga blog to share tips, tutorials and templates.

Alan uses a fun and relaxed style of training that gets to the point, and uses real world practical examples uncovered from his experience of training and developing software for businesses.

Files are provided to follow along and exercises used to recap on topics covered. Alan is always eager to help and will be there for you when needed. He will do his best to answer any question or query related to his courses within 48 hours.

Ready to start learning?
Take This Course