The Growing Importance of Data Analysis
Before diving into the functions, it‘s worth understanding why data analysis has become so crucial for businesses today.
Put simply, data analysis is the process of assessing large amounts of information to uncover trends, metrics, correlations and insights to guide business decisions.
With the expotential growth in data from customers, operations, IoT sensors and more, businesses now capture vast amounts of rich data. Just how much data?
- Global data generation is projected to grow to 175 zettabytes by 2025 [1].
- On average, the amount of business data doubles every 1.2 years [2].
However, as the popular saying goes – "We‘re drowning in data but starved for insights." Raw data holds little value unless analysed to reveal performance drivers, opportunities and threats.
No wonder data analyst roles have grown by 350% since 2012 [3] and the trend continues as companies aim to be data-driven.
Data Analytics Roles Over Time (Forbes, IBM)
Which brings us to Excel – arguably the most popular tool for slicing and dicing data to uncover insights.
Excel – The Data Analysis Swiss Army Knife
Familiarity, flexibility and ease-of-use has made Excel the go-to data analysis tool for over 750 million users worldwide. Excel‘s breadth of features equip it for extensive analytics tasks:
Data Transformation
Using over 400 calculation and transformation functions, Excel can clean, normalize and reshape messy raw data for analysis.
Exploration & Visualization
Visually represent your data trends using interactive charts, graphs and pivot tables filtered by parameters.
Statistical Analysis
Excel includes over 200 advanced statistical functions like regression, hypothesis testing, ANOVA etc enabling analytical insights.
Dashboarding
Combine data models, visualizations and macros into powerful, automated interactive Excel dashboards.
Equipped with this Swiss Army knife, analysts can tackle diverse data analysis needs allowing quicker and more informed decisions across functions.
While Excel includes hundreds of features, getting started with a core set of functions can go a long way in unlocking its analytics superpowers.
13 Must-Know Excel Formulae for Data Analysis
Here are the top Excel functions and formulae that every analyst should have in their toolkit with details on their usage to extract, transform, visualize and drive insights from data.
I. Extract Insights Faster with XLOOKUP, SORT and FILTER
XLOOKUP – Flexible Data Lookup
The XLOOKUP function provides powerful lookup capabilities that outclass the traditional VLOOKUP function. With XLOOKUP, one can find matching records across columns and rows, with sorted or unsorted data while returning adjacent data.
For example, here‘s a dataset containing customer details:
| MemberID | First Name | Last Name | City | Points |
|---|---|---|---|---|
| 1 | John | Wilkinson | London | 1824 |
| 2 | Stacy | Bow | Paris | 8462 |
| 3 | Mark | Zucker | New York | 6843 |
To lookup member 2‘s city, the XLOOKUP formula would be:
=XLOOKUP(2,MemberID,City)Returns "Paris"
Benefits of XLOOKUP:
- Lookup based on any column, not just first one
- Results adjacent data like City, Points etc
- Handles sorted/unsorted data
- Faster processing than VLOOKUP
This makes it invaluable for quick data access without requiring prior sorting or too many nested formulae.
SORT – Arrange Data in Sequence
Before analyzing datasets, one key data preparation step is to arrange values in either ascending or descending sequence.
Excel‘s SORT function allows analysts to avoid slow manual sorting by hand and quickly reorder any dataset with just a few clicks.
For example, to arrange the customer data by loyalty points:
- Select any cell in dataset
- Under Data tab hit Sort & Filter > Sort Smallest to Largest
This dynamically sorts all records by points:
| MemberID | First Name | Last Name | City | Points |
|---|---|---|---|---|
| 1 | John | Wilkinson | London | 1824 |
| 2 | Stacy | Bow | Paris | 8462 |
| 3 | Mark | Zucker | New York | 6843 |
Add secondary or tertiary sort orders to handle ties. Sorting enables you to uncover patterns, outliers and clusters early in the analysis process.
FILTER – Isolate Subsets of Data
While sorting rearranges complete datasets, Filter helps isolate subsets of your data matching given conditions.
For instance, to only see members from Europe cities, under Data tab apply:
- City – Equals – Paris
This will filter and only display:
| MemberID | First Name | Last Name | City | Points |
|---|---|---|---|---|
| 2 | Stacy | Bow | Paris | 8462 |
Filter out the noise to narrow your analysis scope to any sub-population. Adjust, add or remove filters to expand/shrink groups analyzed.
II. Transform Raw Data into Actionable Insights with Pivot Tables
Pivot tables enable analysts to quickly summarize thousands of rows worth of data into insightful cross-tabulated reports. By combining variables across multiple columns and aggregating using sums, counts, averages or more, Excel renders a multidimensional view to identify trends.
Let‘s say we have the sales data of items sold by different sales executives over previous years:
Raw Data:
| Fiscal Year | Sales Agent | Laptops Sold | Desktops Sold | Printers Sold |
|---|---|---|---|---|
| 2019 | Ravi | 10 | 3 | 2 |
| 2019 | Sarah | 5 | 4 | 6 |
| 2020 | Ravi | 8 | 2 | 3 |
| 2020 | Sarah | 7 | 5 | 9 |
| 2021 | Ravi | 12 | 4 | 1 |
| 2021 | Sarah | 9 | 8 | 12 |
Pivot Table Steps
To analyze this by fiscal year and product:
- Select data → Insert Pivot Table
- Drag fields – Rows: Years, Columns: Products, Values: Units Sold
Pivot Table Output
| Fiscal Year | Desktops Sold | Laptops Sold | Printers Sold |
|---|---|---|---|
| 2019 | 7 | 15 | 8 |
| 2020 | 7 | 15 | 12 |
| 2021 | 12 | 21 | 13 |
This summarization by year and product allows analysts to quickly analyze performance trends. Show totals, add % of columns, drill downs to interactively dissect your data.
III. Map Relationships Between Data with Functions like COUNTIFS(), SUMIFS() and AVERAGEIFS()
When trying to understand connections between different datapoints, Excel‘s ‘IFS’ family of functions come in handy by applying criteria to isolate subsets of data across multiple columns or sheets.
For instance, COUNTIFS() counts records while SUMIFS() sums numbers that meet your specified conditions. This allows deriving crosstab cuts of the data.
Use Case 1: Conditional Counts with COUNTIFS()
Let‘s analyze sales executive performance against targets using COUNTIFS():
| Sales Agent | Units Sold | Year | Min Target |
|---|---|---|---|
| Ravi | 10 | 2019 | 8 |
| Sarah | 5 | 2019 | 8 |
| Ravi | 8 | 2020 | 12 |
| Sarah | 7 | 2020 | 12 |
Count those achieving targets in 2019:
=COUNTIFS(Year, 2019, Units Sold, ">="&Min Target)Gives 1 i.e Only Ravi met the goal
Thus, COUNTIFS bridges values across multiple columns to give conditional counts useful for performance reporting.
Use Case 2: Summing Conditionally Across Data Ranges with SUMIFS()
Alternatively, numerically aggregate subset sums using SUMIFS().
For sales data, we can sum revenue meeting certain customer, product and regional filters:
=SUMIFS(Revenue_Range, Customer_Range, "Enterprise", Product, "Cloud", Region, "Europe") Uncover category contributions, drill-down by different groupings or add more filters for insightful sums powered by SUMIFS().
Use Case 3: Average Metrics Across Subgroups with AVERAGEIFS()
Extend conditional logic across calculations of averages, variances, counts and more using AVERAGEIFS().
For example, analyze regional average order values:
= AVERAGEIFS(Sales, Region, "West")Pin-point observations against filters through versatile summary statistics thanks to Excel‘s IF functions tailored for data analysis.
IV. Codify Business Logic with IF(), IFS() and SWITCH()
Beyond just calculations, Excel also allows codifying complex business rules and logic as a part of your data analysis workflow. For example:
=IF(Sales>100,"High","Low") Categorizes sales by custom groups using Excel‘s classic IF() function. More advanced multi-branch logic is possible using IFS() and SWITCH():
=IFS(Sales>=10000, "Platinum", Sales>=5000, "Gold", Sales>=2000, "Silver", TRUE, "Bronze")Such rules lead to automated tagging, segmentation and enriched datasets ready for analytical modelling. Define hierarchies, provide scores or ratings and bucket continuous variables to spot patterns that text/number data may not reveal directly.
V. Visualize Data Relationships with Charts, Graphs and Conditional Formatting
As they say, "A picture is worth a thousand data tables". Excel provides stunning high-resolution charts, graphs and visualization capabilities for better data understanding in seconds.
Beyond standard charts, features like Conditional Formatting apply color scales, data bars, icons and more automatically based on rules to reveal data insights.
These help quickly highlight clusters, correlations, outliers for attention when sifting through data. Integrate interactive charts and graphs into dashboard views using Excel macros to connect insights across different analytical modules.
VI. Prepare Datasets for Analysis with Text Manipulation Functions
Real-world raw data tends to be unstructured, messy and full of inconsistencies. Taming such data requires indispensable text manipulation functions like:
CONCAT() – Combine Text Fields
=CONCAT(First_Name, " ", Last_Name) Jointly processes separate strings
TRIM() – Remove Extra Spaces
=TRIM(Product_Name)Strips whitespace padding text
CLEAN() – Remove Non-Printable Chars
=CLEAN(Description) Deletes unsupported symbols
VALUE() – Convert Text to Numbers
=VALUE("3.14") + 5Transforms numeric text for calculations
So combine text cleansing functions to convert raw inputs into analysis-ready data variables. This allows you to work with exported real-world data in Excel comfortably.
Limitations of Excel Analytics
While extremely versatile, Excel does have some limitations especially when working with Big Data:
- File size limits of 1 GB per sheet, helping store only 1 million rows/16K columns per tab
- Slows down while computing functions across very large datasets
- Doesn‘t scale across collaboration teams working concurrently
- Not optimal for statistical modelling requiring Python/R
- Dashboard publishing outside the workbook is complex
So for sizable enterprise datasets requiring cleansing, modelling, collaboration and publishing – solutions like Microsoft Power BI offer a more robust analytics platform while retaining Excel‘s ease-of-use.
But Excel remains a formidable starting point for most data analysis needs for SMBs and teams before graduating to advanced platforms.
Now Explore Data Insights Using Excel!
Equipped with some of the most versatile Excel functions, you can now start your analytics journey to uncover trends and insights hidden within company data. Transform scattered bits of data into decision-driving analysis and unlock Excel‘s true potential for your firm!
References
[1] Statista – https://bit.ly/3IccyFq[2] Backblaze – https://bit.ly/3Y20dCX
[3] IBM & Forbes – https://bit.ly/3Y8Sgny
