Analyst Builder

Excel for Data Analytics

Overview

In this Excel for Data Analytics Course, you will acquire comprehensive expertise in Excel, encompassing its functions, formulas, data analysis tools, and more! The course is enriched with practical exercises and real-world scenarios, enabling you to master the art of manipulating and analyzing data efficiently. A key feature of this course is the interactive, guided projects that provide practical experience with real-world data analysis challenges, enhancing your portfolio. Upon completing this Excel for Data Analysis course, you will be well-equipped to confidently include Excel expertise on your resume and apply your skills effectively in your professional role.

Thank you so much for joining this course! Please join the discord here: Analyst Builder Discord We are counting on your feedback to help make this course and platform the best it can be!

Here you can offer feedback on the platform, tell us about any bugs or issues you may encounter, and talk to others who are using the platform!

Follow us on other social media platforms too!

Twitter: https://twitter.com/Analyst_Builder

LinkedIn: https://www.linkedin.com/company/analyst-builder/

Thank you for your support and we hope you love the course! 😀

Getting Started

Ensure that you have Microsoft Excel installed locally on your device. There may be compatibility issues with different formulas, functions and other items if you are using Google Sheets, Excel Online or Sharepoint Excel versions.

Content

Your individual workbooks should match the completed version of the file where applicable.

1. Introduction to Excel Video Content

   • What is Excel
   • Installing Excel
   • UI Walkthrough

2. Excel Basics

   • Data Types
   • Adding, Deleting, and Editing Data
   • Sorting and Filtering

3. Formulas and Functions

   • Formulas and Functions Introduction
   • [Basic Formulas and Functions](./Formulas and Functions/Basic Formulas and Functions.xlsx)
   • Conditional Functions
   • Min, Max, and Average
   • Logical Operators
   • IS Functions
   • Rounding Functions
   • String Functions

4. Statistics

   • Mean, Median, and Mode
   • Distributions
   • Standard Deviation
   • Percentiles

5. Conditional Formatting

   • Highlight Cells Rules
   • Top and Bottom Rules
   • Bars, Scales, and Sets

6. XLOOKUP vs VLOOKUP

   • Lookups Introduction
   • VLookups
   • HLookups
   • XLookups
   • Index Match
   • Lookup Practice

7. Pivot Tables

   • Pivot Table Introduction
   • Creating our First Pivot Table
   • Formatting
   • Working with Dates
   • Calculated Fields
   • Sorting and Filtering
   • Practice Challenge

8. Visualizations

   • Bars and Column Charts
   • Line and Combo Charts
   • Scatter Plots and Bubble Charts
   • Pie and Donut Charts
   • Geographical Map Charts
   • Visualization Challenge

Projects

US Debt Tracker

Scenario

You were hired by a small debt agency in Washington DC that specializes in analyzing and forecasting public and private debt. The US Governement has requested an analysis of their public and governemental debt to answer some specific questions below.

Using the Questions and Data Dictionary Provided, answer the questions using charts/graphs and narratives.

Data Dictionary

Field	Definition
Debt Held by the Public	Portion of the US public debt held by individuals, corporations, foreign governments, and other entities outside the US government.
Intragovernmental Holdings	Portion of the US public debt held by other US government agencies.
Total Public Debt Outstanding	Sum of the debt held by the public and intragovernmental holdings.

Questions

1. What was the Yearly Debt Percentage Increase for each year compared to the previous year?
2. Which months historically have seen the highest/lowest increases in Total debt?
3. What is the projected growth of the publicly held debt in the next few years?

This project has an example solution provided.

NYC Transportation

Scenario

You've been hired by the New York Division of Transportation as a Data Analyst with the primary goal of improving the efficiency and reliability of the city's bus transportation system. The city has been experiencing a significant number of bus breakdowns and delays, which has been causing inconvenience to commuters and straining the city's public transportation resources.

Your task is to analyze the provided data to identify patterns and factors that contribute to these breakdowns and delays.

Questions

1. What are the most common reasons for delays and breakdowns?
2. How do delay times vary by bus company and borough?
3. Is there a correlation between specific days of the week and the frequency of breakdowns or delays?