Follow along with me as I show you some of the most common techniques and tips I use for cleaning up a data set in Excel before doing analysis and forecasting. In order to clean up and summarize data, I’ll show you some intermediate formulas like IFERROR, COUNTIF, and nesting formulas together. You’ll also learn some forecasting techniques using basic regression in Excel. Most of the skills in this class apply to Google Sheets as well.
About me
I have taught Excel to 25,000+ students on Skillshare, facilitated Excel workshops to 5,000+ MBA students around the U.S., and frequently create content about data analysis on <a href="https://www.thekeycuts.com/excel-blog/" rel="nofollow noreferrer noopener" target="_blank">my blog</a> and podcast (<a href="https://podcasts.apple.com/us/podcast/dear-analyst/id1455099942" rel="nofollow noreferrer noopener" target="_blank">Dear Analyst</a>). Connect or follow me on <a href="https://www.linkedin.com/in/thealchen/" rel="nofollow noreferrer noopener" target="_blank">LinkedIn</a>.
<img src="https://static.skillshare.com/uploads/discussion/tmp/fd9b59f4" class="zoomable rounded-image-wrapper" alt="fd9b59f4">
Prerequisites
This class moves fast. 
If you don't have a basic understanding of Excel including:
<ul><li>Navigating around Excel</li>
<li>Writing basic formulas (including VLOOKUP)</li>
<li>Creating basic charts</li>
</ul>Please take my fundamentals Excel class: <a href="https://skl.sh/2lsi6up" rel="nofollow noreferrer noopener" target="_blank">Excel For The Real World I: Gain the Basic Skills of Microsoft Excel</a> before taking this intermediate Excel class to make the most of your time and learning experience.
What you'll learn
You’ll be learning intermediate formula skills for cleaning and summarizing data in Excel. You’ll also learn how to do a basic regression in Excel to forecast data.
<ul><li>Named Ranges and OFFSET</li>
<li>Using date manipulation formulas like YEAR, MONTH, and DAY</li>
<li>Nested IF formulas</li>
<li>Using AND/OR in IF formulas</li>
<li>Handling formula errors with ISERROR, IFERROR</li>
<li>Summarizing and Aggregating data with COUNTIF, SUMIF, AVERAGEIF, AGGREGATE</li>
<li>Scatterplot chart with trendlines</li>
<li>Regression analysis on Instagram posts and product sales</li>
</ul>Who is the class for?
Knowledge workers who are in Excel or Google Sheets all day and want to be able to quickly clean and analyze data without the help of a data engineer or Excel “guru.” You know the fundamentals of Excel and want to go deeper in terms of building more complicated formulas, cleaning data so that it's ready for analyzing and building charts, and doing basic forecasting and regression analysis.
Why is the class is useful?
No matter what group you work in at your company, you will most likely have to pull data from a database or inherit a really large CSV, Excel, or Google Sheets file which you have to clean up and analyze. I’m not going to teach you all the formulas under the sun, but rather the techniques and formulas that matter for the workplace. This means cleaning and formatting data, checking it for errors, and summarizing it for your stakeholders. All the skills you learn in this class can be applied to Google Sheets as well.
Materials &amp; resources
The class Excel workbook is attached under the Resources to the right (Intermediate_ExcelForTheRealWorld_Workbook_Skillshare.xls).
I’ll be using Mac Excel for Office 365. You can use any of the following versions of Excel for this class:
<ul><li>Windows: Excel 2019, Excel 2016, Excel 2013  </li>
<li>Mac: Excel 2016, Excel 2011</li>
</ul><img src="https://static.skillshare.com/uploads/discussion/tmp/f29f575f" class="zoomable rounded-image-wrapper" alt="f29f575f">

Trailer

Class Overview + Project

Named Ranges & Offsets

Date-Related Formulas

Nested If Formulas

Using AND/OR In If Formulas

Handling Formulas Errors

Formulas For Aggregation

Scatterplots With Trendlines

Formulas For Regression

Congratulations & Conclusion

Intermediate_ExcelForTheRealWorld_Workbook_Skillshare.xls

Class_Project_Hotel_Bookings.xls

Intermediate Excel for the Real World: Cleaning Data for Analysis and Forecasting

Al Chen gave you 1 free month of Skillshare

Get 1 free month of Skillshare

Intermediate Excel for the Real World: Cleaning Data for Analysis and Forecasting

Creative Learning Made Easy

Company

Work With Us

Teach with Us

Shop

Mobile

English

English