Excel Formulas and Shortcuts That Simplify Data Analysis

Microsoft Excel is a powerful tool for handling and analysing data at work. However, many of us only scratch the surface of what it can do. Let’s take a look…

Mastering Excel formulas and Excel shortcuts can help you to be more efficient and accurate in your data analysis. In this guide, we’ll explore essential data analysis tips, formulas, and shortcuts that will change the way you work with Excel for good!

Click here to open Excel for free on the web

• Click here to skip to Excel shortcuts
• Click here to skip to data analysis tips

Essential Excel Formulas for Data Analysis

So, we already know that Excel offers a wide range of formulas that can help you manipulate, clean, and analyse data efficiently.

Below are some of the most commonly used formulas for data analysis:

1. SUM and SUMIF

• SUM(): Adds up a range of numbers.
• SUMIF(range, criteria, [sum_range]): Adds values that meet a specified condition.

Example:

=SUM(A1:A10)

=SUMIF(A1:A10, “>50”, B1:B10)

2. AVERAGE and AVERAGEIF

• AVERAGE(): Finds the mean of a range of numbers.
• AVERAGEIF(range, criteria, [average_range]): Computes the average of values that match a condition.

Example:

=AVERAGE(A1:A10)
=AVERAGEIF(A1:A10, “>50”, B1:B10)

3. VLOOKUP and HLOOKUP

• VLOOKUP(): Searches for a value in the first column of a range and returns a corresponding value.
• HLOOKUP(): Works like VLOOKUP but searches in a row instead of a column.

Example:

=VLOOKUP(1001, A2:D10, 2, FALSE)
=HLOOKUP(“Product A”, A1:D4, 2, FALSE)

4. INDEX and MATCH

Unlike VLOOKUP, INDEX and MATCH provide more flexibility.

• INDEX(): Returns the value of a specific cell in a range.
• MATCH(): Finds the position of a value within a range.

Example:

=INDEX(B2:B10, MATCH(1001, A2:A10, 0))

5. COUNT, COUNTA, COUNTIF, and COUNTIFS

• COUNT(): Counts numerical values in a range.
• COUNTA(): Counts all non-empty cells.
• COUNTIF(): Counts cells that meet a condition.
• COUNTIFS(): Counts cells based on multiple conditions.

Example:

=COUNT(A1:A10)

=COUNTIF(A1:A10, “>50”)

=COUNTIFS(A1:A10, “>50”, B1:B10, “<100”)

6. TEXT and CONCATENATE

• TEXT(): Formats numbers and dates as text.
• CONCATENATE(): Joins multiple text strings.

Example:

=TEXT(A1, “MM/DD/YYYY”)

=CONCATENATE(A1, ” “, B1)

Time-Saving Excel Shortcuts

In addition to formulas, learning Excel shortcuts can also dramatically reduce the time you spend on data analysis. Leaving you more time to make coffees, watch cat videos and take midday naps! Here are some of the most useful ones:

Navigation Shortcuts

• Ctrl + Arrow Keys Jump to the edge of a data range.
• Ctrl + Home Move to the beginning of the worksheet.
• Ctrl + End Move to the last cell containing data.

Selection Shortcuts

• Ctrl + A Select all data.
• Shift + Space Select the entire row.
• Ctrl + Space Select the entire column.
• Ctrl + Shift + L Apply or remove filters.

Formatting Shortcuts

• Ctrl + B Bold text.
• Ctrl + I Italicize text.
• Ctrl + U Underline text.
• Ctrl + Shift + $ Apply currency formatting.

Data Entry Shortcuts

• Alt + Enter Insert a new line in a cell.
• Ctrl + D Copy data from the cell above.
• Ctrl + R Copy data from the cell to the left.
• Ctrl + ; Insert the current date.
• Ctrl + Shift + : Insert the current time.

Formula Shortcuts

• Ctrl + ` (backtick) Show all formulas.
• F2 Edit the active cell.
• F4 Repeat the last action.
• Shift + F3 Open the Insert Function dialog box.
• Ctrl + Shift + Enter Enter an array formula.

Advanced Data Analysis Tips

Welcome to the Pro area. If you want to take your Excel skills even further, here are some additional data analysis tips…

1. Use PivotTables for Summarisation

PivotTables are one of the most powerful tools for summarising large datasets.

• Insert a PivotTable: Select your data, then go to Insert > PivotTable.
• Drag and drop fields into Rows, Columns, and Values for quick insights.

2. Automate Tasks with Macros

If you frequently perform repetitive tasks, consider recording a macro.

• Go to Developer > Record Macro.
• Perform the desired actions.
• Stop recording and assign the macro to a shortcut.

3. Conditional Formatting for Quick Insights

Use conditional formatting to highlight key trends.

• Go to Home > Conditional Formatting.
• Apply rules like color scales, icon sets, or custom formulas.

4. Data Validation for Accuracy

Prevent data entry errors using Data Validation.

• Go to Data > Data Validation.
• Set criteria (e.g., restrict entries to numbers between 1 and 100).

5. Remove Duplicates

To clean your dataset:

• Go to Data > Remove Duplicates.
• Select the columns where duplicates should be checked.

Alright, that’s all for today!

Mastering Excel formulas and shortcuts is pretty much the bread-and-butter of efficient data analysis. The right data analysis tips can help you work faster, minimise errors, and gain better insights from your data. Whether you’re using basic functions like SUM and VLOOKUP or leveraging PivotTables and Macros, applying these techniques will make your work in Excel more productive and streamlined!

Got a question? We can answer it. Click here to get in touch.