Data is everywhere around you! So how can you start learning data analytics with excel?

Data analytics with excel: A visualization of data as bars moving on a screen

After collecting accurate data, you can find the root causes of issues, predict trends, and make smart decisions. This process is called data analytics and many employees in the workforce use Excel to help them analyze the data they collect for their work.

Why Is Data Analysis Important?

Let's say you want to work for a research university or data-driven company as a data analyst or data scientist. Learning how to analyze data with Excel would make you a more competitive candidate for the job.

data analytics dashboard Photo by Luke Chesser on Unsplash

In fact, by 2025, almost all employees across many sectors and fields will leverage data to optimize their work. That's why many employers are boosting their teams’ data literacy and bringing new blood with advanced data skills, such as data analytics with excel, to the teams.

What Type of Data To Get and How?

Data are individual facts, statistics, or items of information, often numeric.

Quantitative Data

  • numerical

  • can be measured

    Flaticon Icon

Qualitative Data

  • categorical and descriptive

  • can't be measured

    Flaticon Icon

Quantitative data is usually preferred for data analytics in Excel. You can gather quantitative data through polls, surveys, observations, experiments, and interviews.

Formulas for Data Analytics with Excel

Flaticon Icon Use formulas to find the sum, average, minimum, and maximum of the collected data.

Amelia uses Excel formulas to manage her clothing store.

Flaticon Icon

  • She uses the SUM formula to calculate the total sales as follows:

    1. Select the empty cell B7 (where the total amount will appear)

    2. Enter a SUM function by typing =SUM(B2:B5)

    3. Press Enter to get the total sales

  • She uses the AVERAGE formula to calculate the average sales as follows:

    1. Select the empty cell B7 (where the average amount will appear)

    2. Enter an AVERAGE function by typing =AVERAGE(B2:B5)

    3. Press Enter to get the average sales

  • She uses the MIN formula to find the minimum sales as follows:

    1. Select the empty cell B7 (where the minimum amount will appear)

    2. Enter a MIN function by typing =MIN(B2:B5)

    3. Press Enter to get the Min sales

  • She uses the MAX formula to find the maximum sales as follows:

  1. Select the empty cell B7 (where the maximum amount will appear)

  2. Enter a MAX function by typing =MAX(B2:B5)

  3. Press Enter to get the Max sales

Cell References in Excel

What is a cell reference?

A cell reference is a combination of a column letter and a row number that identifies a cell on a spreadsheet.

Cell Reference Example: A1 is a cell reference on a spreadsheet, located at the intersection of Column A and Row 1

There are two types of cell references:

  • Relative references

  • Absolute references

What's the difference?

  • Relative references change when a formula is copied to another cell.

  • Absolute references remain unchanged wherever they're copied.

Watch this video to learn how to use these two types of references.

Amelia, the clothing store owner, uses cell references in Excel when calculating item costs after tax. Flaticon Icon

Row 2 Column B: Cost = $40; Column C: Cost with tax =B2 (1+$D$2); Column D: Sales Tax Rate = 13% (fixed rate)

Visualization: Data Analytics with Excel

Create charts to visualize your data for reports or presentations.

A graphic showing different types of Excel charts

Take a closer look at the most common chart types:

A pie chart shows the proportion of items to the total. data analytics with excel

A line chart shows continuous data over time. data analytics with excel

A bar chart typically displays values along the horizontal axis and categories along the vertical axis. data analytics with excel

A column chart typically displays values along the vertical axis and categories along the horizontal axis. data analytics with excel

Creating charts in Excel is so easy!

  1. Select all the rows and columns you want to tabulate in your charts (A1:B7 for the chart below)

  2. Select Insert > Recommended Charts

  3. Select a chart from the drop-down menu

  4. Adjust the size of the chart

Quiz

Bella works for a retail store. Her manager asked her to report total retail sales over the past 10 years. She's going to visualize data with a chart in Excel in the meeting. What chart would be the best option?

Take Action

Practice data analytics with excel

Practice mastering Data Analytics with Excel!

Try these steps to get started:

License:

Your feedback matters to us.

This Byte helped me better understand the topic.

Get support to take action on this Byte