Excelling in Excel Made Easy

Reading time 9 minutes

Hey there,

This week, we’ve packed our newsletter with essential Excel tips and a bit of nostalgia! Whether you're looking to boost your skills or keep your data secure, we've got you covered.

10 Essential Excel Tips to Help You Excel ????

MS Excel icon in 3D. My 3D work may be seen in the section titled "3D Render."

Excel is an essential tool for data analysis, and mastering key functions can significantly improve how you handle and interpret data. Whether you’re new to Excel or already well-versed, these seven functions will help you work more efficiently and uncover deeper insights from your data.

1. SUMIFS

SUMIFS goes beyond the basic SUM function by allowing you to add up values based on multiple conditions. This makes it perfect for complex data sets where you need to filter data by different criteria. For example, you might want to sum sales figures for a specific product in a certain region within a set time frame.

Example: =SUMIFS(C2:C20, A2:A20, "Product A", B2:B20, "Region 1")
This will total the values in column C where the corresponding rows in columns A and B match "Product A" and "Region 1".

2. FILTER

Introduced in Excel 365 and 2021, FILTER lets you extract data that meets specific conditions without manually sorting. This function is incredibly powerful for dynamically narrowing down datasets and is particularly useful for exploring large volumes of information quickly.

Example: =FILTER(A2:C20, B2:B20 = "UK")
This extracts all rows where column B contains "UK", giving you a focused view of your data.

3. AGGREGATE

AGGREGATE offers a more flexible alternative to standard summary functions like SUM or AVERAGE. It can perform calculations while ignoring errors or hidden rows, making it invaluable for large or messy datasets.

Example: =AGGREGATE(9, 6, C2:C20)
This sums up the values in column C, ignoring any errors or hidden rows (with 9 indicating the sum function and 6 ignoring errors).

4. INDEX and MATCH

The combination of INDEX and MATCH provides a versatile alternative to VLOOKUP, especially when your data isn’t neatly organised in columns or rows. INDEX retrieves a value based on its position in a range, while MATCH finds the position of a specific value within a row or column.

Example: =INDEX(C2:C20, MATCH("Product A", A2:A20, 0))
This retrieves the value from column C where "Product A" appears in column A.

5. TEXTJOIN

TEXTJOIN helps you combine text from multiple cells into one, separated by a delimiter you choose. It’s perfect for scenarios like joining names or addresses, streamlining your data presentation.

Example: =TEXTJOIN(", ", TRUE, A2:A5)
This joins the values in A2 through A5 with commas, skipping any empty cells.

6. SEQUENCE

SEQUENCE allows you to generate a list of sequential numbers automatically, which is especially useful when working with time-series data or when you need to create numbered lists without manually typing numbers.

Example: =SEQUENCE(10, 1, 1, 1)
This generates a list from 1 to 10.

7. XLOOKUP

XLOOKUP is a robust and flexible replacement for VLOOKUP and HLOOKUP. It works for both vertical and horizontal lookups and can search through multiple columns without worrying about the order. Plus, it includes built-in error handling.

Example: =XLOOKUP(D2, A2:A20, B2:B20, "Not Found")
This looks for the value in D2 within column A and returns the corresponding value from column B. If no match is found, it returns "Not Found" instead of an error.

8. UNIQUE

The UNIQUE function is a brilliant tool for anyone dealing with large datasets full of repetitive information. It allows you to quickly identify and extract unique values from a range, simplifying tasks like finding unique customer IDs or product categories.

Example: =UNIQUE(A2:A20)
This will return a list of unique values from column A, filtering out any duplicates.

9. SORT

SORT helps you arrange data in either ascending or descending order. It’s especially useful when you need to organise data dynamically, whether you're sorting a list of sales figures or alphabetising customer names. This function can be combined with other functions like UNIQUE or FILTER to create more advanced data handling processes.

Example: =SORT(A2:A20, 1, TRUE)
This will sort the data in column A in ascending order (the 1 refers to the column number, and TRUE specifies ascending order).

10. IFERROR

IFERROR is a lifesaver when dealing with formulas that can potentially return errors, such as when searching for non-existent values. Instead of displaying an error message like #N/A, IFERROR allows you to specify a custom message or action when an error occurs.

Example: =IFERROR(VLOOKUP(D2, A2:B20, 2, FALSE), "Value Not Found")
If the VLOOKUP function fails to find a match for the value in D2, instead of showing an error, it will return "Value Not Found". This keeps your worksheets clean and avoids confusion when errors occur.

Keeping Up to Speed With Excel Security Features

Keeping your data secure in Excel is critical, especially when working with sensitive or confidential information. Thankfully, Excel has several built-in features that make it easy to protect your files from unauthorised access or accidental edits. Here are some key tips to help you safeguard your Excel data:

1. Password Protect Your Workbook

Protect your entire workbook or specific sheets by adding a password. This ensures only authorised users can open or modify the file. Simply go to File > Info > Protect Workbook, and set a password to lock down access.

2. Limit Editing Permissions

If you want others to view your file but prevent any changes, you can set it to "read-only" or restrict specific actions like editing cells or altering the structure. This keeps your data intact while still allowing others to review it.

3. Encrypt Your Workbook

For an extra layer of security, encrypt your workbook. This means even if someone gains access to the file, they can’t see the contents without the password. Go to File > Info > Protect Workbook > Encrypt with Password to add this protection.

4. Track Changes

When collaborating with others, it’s smart to enable Track Changes so you can see any edits made to the workbook. This allows you to monitor changes and keep control of your data.

By applying these straightforward steps, you’ll ensure your Excel data stays safe, confidential, and accurate.

Learning Resources

Want to learn more?

Check these out:

Microsoft Excel Support - https://support.microsoft.com/en-us/excel
Excel Campus - https://www.excelcampus.com/
Chandoo - https://chandoo.org/
Mr Excel - https://www.mrexcel.com/
Excel Is Fun - https://www.youtube.com/user/ExcelIsFun

In The News This Week - Excel Is Nearly 40!

Nearly 40 years ago Bill Gate’s original spreadsheet was launched for accountants wanting a quicker way to crunch numbers. Excel 1.0 was released for Mac OS, 2 years before being available on Windows.

Check out how it looked over the years..