Intermediate Excel for Working with Data

Categories: Excel

Wishlist

Course Prerequisite(s)

Please note that this course has the following prerequisites which must be completed before it can be accessed
Excel Essentials for Beginners

To optimizing data
Creating and applying data validation, as well as working with formulas in data validation
How to check for errors, trace precedents and dependents, resolve circular references, and finally, protect your worksheets and workbooks from further harm
Gathering, transforming, and analyzing data
Chart & graph by PivotTable
What-if analysis
Data analysis, visualization, and create a dashboard
Creating and using Add-ins
Create custom conditional formatting rules based on formulas
And many more!

Course Content

Optimizing Data
In this topic, you will learn about more complex sorting data in Excel by a single column or by multiple columns. Learn to filter Excel data for specific words and dates, and apply multiple filters to a single data table. Creating named ranges, and the benefits of doing so, creating Excel tables, and their advantages. And learn to record basic Excel macros to automate your actions (instead of doing them manually) .

Sorting & Filtering
Naming Ranges
Excel Tables
Recording Macros

Validating
This topic takes you through creating and applying data validation and working with formulas in data validation. This is followed by basic and advanced conditional formatting. You will learn how to use Data Validation to ensure that users enter valid data in input cells. Use to restrict users' ability to enter invalid data in cells by providing a drop-down list of valid options. And learn how to apply built-in number styles and create your own custom style packages.

Formula
In this topic, you will learn to work with formula auditing tools including tracing precedent and dependent cells and formula evaluation. Learn to use SUMIF and COUNTIF to add cells only when certain conditions are met. Learn to work with the EOMONTH and EDATE functions that easily shift dates from one month or year to another. Learn about creating and updating external links, and about the potential dangers of external links in Excel. Learn to use VLOOKUP to find an approximate match and return it or the corresponding value from another column. Learn to use VLOOKUP to find an exact match and return it or the corresponding value from another column. Learn to work with INDEX, MATCH, and HLOOKUP, as alternatives to the VLOOKUP function. And finally, on this topic we will introduce the new XLOOKUP function in Microsoft 365. bye bye VLOOKUP, HLOOKUP, and INDEX/MATCH.

Gathering, Transforming, and Analysis data
In this topic, you will learn about how to work with Power Query (Power Query is the best tool for consolidating your data so it’s vertically contiguous (without blank rows or other garbage) - for Tables, PivotTables, and Charts). Learn to Import data from a text file into an Excel table using Power Query, appending tables is the process of stacking them vertically to create long tables that can be used to feed business intelligence solutions and PivotTables. Learn to create and modify basic PivotTables (for quickly summarizing and highlighting data). Learn to control formatting and other options in PivotTables, and create and modify basic calculated fields for PivotTables. And finally, you will learn to perform what-if analysis in Excel using the Scenario Manager, Data Tables, and Goal Seek.

Presenting and Reporting
In this topic, you will learn about: Adding and working with Excel’s outlining tools to quickly hide irrelevant sections of your worksheet. Work with the Format Cells dialog to apply text rotation and borders, and to center data across multiple cells. Learn to build your own custom number formats to display dates, values, and variances how you want to see them, create top/bottom and highlight cell rules, as well as to apply icon sets and data bars, create custom conditional formatting rules based on formulas, build a chart effectively by understanding what? so what? and now what?, learn how to create and manage Custom Views in a worksheet, learn about page setup options, fitting print jobs on a page, headers and footer, and other ways to customize print jobs, and learn about the setup and weaknesses of Excel’s security systems.

Add this certificate to your CV profile to demonstrate your skills & increase your chances of getting noticed.

selected template

Student Ratings & Reviews

No Review Yet

No Review Yet

A course by

Socheata Chamroeun

Data Analyst

Som Brathna Sout

Data Engineer/ML engineer

Want to receive push notifications for all major on-site activities?

✕