Learn These Key Excel Skills

Learn these key Excel skills that most office workers should be able to do with little or no help. Having these skills will help you build better Excel workbooks, so you can save time and help avoid errors.

Data Validation - Drop Down List

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

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

More Data Validation Tutorials

After you know the basics for drop down lists, here are a couple more data validation tutorials for you:

--1) Dependent Drop Down List Data Validation -- Easy steps for making a dependent drop down list. These are conditional drop downs, based on the selection in another cell. For example, select a region first, and then a city from that region.

--2) Data Validation Tips and Troubleshooting -- How to fix Excel data validation problems. Drop down not working, blank selected, not showing all data. Videos, written steps, data validation troubleshooting

More Tutorials and Videos

Go to the Data Validation Topics page, to find more tutorials, workbooks, and videos.

Formulas - Sum With Criteria

The SUM function is usually the first formula that people use when they're learning Excel. After that, you can bring more power to your formulas, by using functions that let you include criteria. Instead of adding up all the numbers in a list, you could get a total for a specific region, or a single product.

This short video shows how to sum with multiple criteria, using the SUMIFS function

More Criteria Formula Tutorials

You can use criteria with other functions too, so here are a couple more tutorials for you:

--1) Count With Criteria -- Count the items in a list, based on one or more criteria. For example, how many orders were from the East region, with a quantity greater than 100?

--2) Lookup with Multiple Criteria -- How to do Excel multiple criteria lookup with the new FILTER function or by combining the INDEX and MATCH functions.

More Tutorials and Videos

Go to the Formula Topics page, to find more tutorials, workbooks, and videos.

Formulas - Use a Lookup Function

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.

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.

More Tutorials and Videos

Go to the Pivot Table Topics page, to find more tutorials, workbooks, and videos.

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.

More Tutorials and Videos

Go to the Chart Topics page, to find more tutorials, workbooks, and videos.

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.

More Tables and Filters Tutorials

-- 1) Excel also has an Advanced Filter feature, where you can use complex criteria, beyond what's possible in the AutoFilter drop downs. You can also use an Advanced Filter to send data to a different worksheet, without a macro!

--2) FILTER Function -- In Excel 365, you can use the new FILTER function to get data from a formatted table, based on one or more criteria.

More Tutorials and Videos

Go to the Filter Topics page, to find more tutorials, workbooks, and videos.

Formatting - 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 feature 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

More Tutorials and Videos

Go to the Formatting Topics page, to find more tutorials, workbooks, and videos.

Macros - Record and Edit

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.

More Tutorials and Videos

Go to the Macro Topics page, to find more tutorials, workbooks, and videos.

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

--1) How to Combine Cells in Excel - TEXT Function Examples -- How to combine text and number cells in Excel. TEXT function format dates, numbers. Text in formulas. TEXTJOIN example. Videos, written steps, workbooks

--2) How to Fix Excel Dates That Won't Change Format -- How to fix Excel dates that will not change format. Fix dates that end up in the wrong order when sorted. Use built-in Excel tool to fix this problem

--3) Excel Pivot Tables Count Unique Items -- How to count unique items (count distinct) in an Excel pivot table. Three methods, for different versions of Excel. Get the free workbook to follow along.

--4) How to Split or Reverse First Last Names in Excel -- How to reverse first and last names in Excel or split into separate cells. Use formula to reverse names from Smith, Mary to Mary Smith, with MID function