Learn These Key Excel Skills

Learn these key Excel skills that most office workers should do with little or no help. Build better Excel workbooks, to save time and help avoid errors.

Sum and Count With Criteria After you learn how to sum and count items on an Excel worksheet, you're ready for the next-level skills - sum and count based on criteria. For example: Sum all sales for the West region Count the customers who ordered Pens in June Use the "IF" and "IFS" functions to do that. -- Sum with 1 criterion - SUMIF function -- Sum based on multiple criteria = SUMIFS function. -- Count with criteria - COUNTIF and COUNTIFS This short video shows how to sum with multiple criteria, using the SUMIFS function

 

Build a Pivot Table If you're collecting data in Excel, using named Excel tables, you can build a Pivot Table to quickly summarize that data. You'll see totals and subtotals, without creating any formulas. This video shows how to create a pivot table to summarize data in Excel. For detailed tutorial, go to the How to Plan and Set Up a Pivot Table page.

 

Absolute and Relative References

When you write a formula in Excel, you can use absolute, relative or mixed references. If you use the right type of reference, you can easily copy your formulas to other cells, and get the correct results.

• A relative reference (B2) will adjust the column letter, if you copy across, and will adjust the row letter, when you copy up or down.
• An absolute reference ($B$2) will not adjust the column or row when copied to other cells
• A mixed reference locks either the column ($B2) or the row (B$2), and the other part of the reference adjusts when copied to other cells

Create a Drop Down List

To make it easy for people to enter data, create a drop down list of options in a cell, using the data validation feature.

This video shows how to create a drop down list in a worksheet cell, with data validation. This helps ensure that only valid information in entered in these cells

Sort by Multiple Columns

It's easy to do a single-column sort in Excel, but sometimes you'll want to sort by two or more columns.

You can also sort data in a custom order, such as by month name, or weekday name.

This short video shows how to sort a list in a custom order, based on the weekday names.

Create a Lookup Formula

Another key skill in Excel is writing a lookup formula. To see some of your options, go to the page that compares the Excel lookup functions.

-- For a flexible and powerful lookup formula, combine the INDEX and MATCH functions.

-- For a simple, but less flexible lookup, use the VLOOKUP function.

This video shows how to look up a specific value in a table. This example uses the VLOOKUP function to return a price for the selected product.

Tables and Filters

If you have a list of data on worksheet, format it as a named Excel table. Named tables have useful built-in features, like sorting and filtering, to help organize and view your data.

This short video the steps for creating an Excel Table.

In addition to the Excel table's built-in AutoFilter feature, Excel also has an Advanced Filter feature. With an Advanced Filter, you can create complex criteria, beyond what's possible in the AutoFilter drop downs.

You can also use and Advance Filter to send data to a different worksheet. This video shows the steps for this handy feature.

Build an Order Form

Instead of using Excel files that other people built, learn how to design and build a simple workbook of your own, such as an order form.

Learn how to make an Excel Order form in this step-by-step video. On the Order form page, you can download the completed workbook, and the starting version (to follow along with the video)

Create a Chart

To help people understand your Excel data, learn to create a simple chart.

-- A pie chart is a good way to show how a few items contribute to an overall amount.

-- To compare amounts over time, use a column or line chart, or combine 2 chart types.

This video shows how to make a simple pie chart that shows the percentage of sales in each region.

Conditional Formatting

With conditional formatting, you can select one or more cells, and create rules (conditions) for when and how those cells are formatted. This skill can help you spot errors, alert you to upcoming dates, or catch numbers that are outside of the expected range of values..

This video shows the steps for adding conditional formatting, to highlight low and high numbers in a list

Write a Nested Formula

After you're able to write simple Excel formulas, go to the next skill level and combine two or more functions in a nested formula.

For example, use the Excel IF function twice in one formula, to test 2 things -- the price column and the quantity column..

Excel Protection

If you're setting up Excel workbooks that other people will use, it's important to know how Excel protection works, and what its limitsare. You can protect worksheets, the workbook structure, and Excel files (password to open).

Date and Time Formulas

A high percentage of Excel workbooks include dates and times, so learn how to use the Date functions in Excel, such as DATE, WEEKDAY and WEEKNUM.

And this video shows how to split the date and time into separate cells in a worksheet.

Also, there are lots of useful date and time arithmetic samples on Chip Pearson's website.

Record and Edit a Macro

To save time when working in Excel, learn how to record and modify a simple Excel macro,

For example, this video shows how to record and run a simple macro in Excel, to automate the task of formatting an Excel file every day.

Video Playlist: Excel Key Skills

Watch the videos in this playlist, to see the steps for some of the Excel Key Skills you should have.

Excel Courses

If you want to learn quickly and efficiently, check out these Excel courses that I recommend. Led by Excel experts, this list has training for different skill levels.

Excel Skills: Mynda Treacy's Excel Skills course covers all the basics in Excel, and will get you up and running quickly.

Expert Excel: After you've mastered the basics in Excel, enroll in Mynda Treacy's Excel Expert course. The lessons in this course will take you from Intermediate level, to Expert skills.

Pivot Tables: John Michaloudis leads the Xtreme Pivot Table Course -- with 200+ video tutorials, it covers everthing you need to know about pivot tables, and more!

Dashboards: Learn new skills that could help you advance at work. Mynda Treacy leads a highly-rated Excel Dashboard Course, and registration opens a few times each year. Sign up to be notified, the next time the course is available.

More Excel Tutorials

Formulas, Getting Started

Subtotal Feature

Running Total Video

SUBTOTAL Filtered List

SUBTOTAL Function

AVERAGE

COUNT / COUNTIF

______________________

Key Skills For Excel Users