My Excel Academy

Manually navigating across multiple worksheets / workbooks to try and summarize data can be a pain.

Did you know with the Consolidate feature, you're able to quickly summarize data from multiple worksheets or workbooks into a single worksheet or workbook?

Some examples of how I've used this feature in the past include:

1) Summarizing total sales (each month on a separate sheet) into a single worksheet
2) Binning items sold (reducing a list of 10000+ sales down to the 50 unique products sold, along with total quantities sold)
3) Combining budgets from various departments into a single master budget for the organization

If you haven't tried using the Consolidate feature, I bet it will surprise you how powerful it is.

Try it out and let me know your thoughts!

Are you looking to advance your career?

Many businesses cannot afford specific systems to manage their data, as solutions are expensive and are too narrow in scope.

This is exactly why over 80% of businesses use Microsoft Excel! It's dynamic and can be customized to meet specific requirements.

Not only is this beneficial from an operational perspective, but this way businesses can avoid constant IT costs as well.

Learning Excel is not only about learning the specific software, but also about learning problem solving and process management skills. Both of these are invaluable in any business setting, regardless what the task is.

Excel can be intimidating. However, if you try your best to learn something new every day or even practice skills you've recently learned - you'll be surprised how much progress you make with the software.

Stay tuned for more tips & tricks!

Do you prefer using tables when working with you data?

I'll be the first to admit that I don't always use tables in Excel. For smaller data sets that I know won't be expanding & for quick data analysis, I find I skip using an Excel table, even though it's good practice to!

For larger data sets, however, I never skip out on using tables.

Tables are great for maintaining the integrity of your data set, whether it be formatting, consistent formulas or even how tables automatically include new adjacent data into the table.

Tables are very versatile and are a cornerstone to keeping your data organized and presentable.

If you aren't already using them, try them out by selecting your data and hitting Ctrl + T!

Are you afraid of closing Excel at the end of the day because you don't want to re-open all of the files the following day?

This quick tip saves me so much time, especially with workbooks that have data connections in them or macros that automatically run when the workbook is opened!

You can open all of your workbooks at once and even step away from the computer as everything initializes.

No more twiddling your thumbs as you wait in between opening Excel files!

How often do you use conditional formatting and how do you use it?

I most often find myself using the colour scales option, which essentially creates a heat map of your data. It brings attention to outliers and helps better visualize any trends in your data.

Another great use is formatting specific cells based on a user-defined condition.

You can use icon sets to draw attention to values, change the font or even change the cell fill colour.

The options are almost endless with conditional formatting. If you don't presently use it in your daily Excel adventures, I'd highly recommend it as not only will it save you time in analyzing your data but it will also make your data more presentable to others!

Do you know what version of Excel you have installed? Is it 32-bit? Or 64-bit?

Up until more recently, this didn't make much of a difference as many computers didn't come with between 8 to 32GB of RAM.

Now that computers have so much additional RAM available, it only makes sense to make use of it with the 64-bit version of Excel!

So if you find your Excel runs slowly with large computations and your computer still has a lot of RAM available, you might be using the 32-bit version of the application.

Go from 🐌 to 🐆 with this quick tip!!!

A lot of people avoid using VLOOKUP because at first glance it looks complicated and they aren't sure they even know how it works.

These are good reasons! But hopefully I can help change your mindset around the function here.

Fundamentally, VLOOKUP allows you to pull data from a set of data, based on a matching (common) lookup value.

Let's say you have a data set with three columns: order numbers, descriptions and the price for each order. You have 10000 orders, so it's time-consuming to scroll through the entire list looking for specifics.

In this example, you could use the VLOOKUP function to get either the description or price (or both) immediately if you knew the order number that you were interested in.

To find the price for order 1078, the formula would look something like this:
=VLOOKUP(1078, A1:C10000, 3, FALSE)

'1078' represents the order number we're looking to match
'A1:C10000' represents the range of our data set
'3' represents the column number in our data range that we want to pull the associated value from
'FALSE' represents that we want an exact match (exact match to order 1078)

VLOOKUP may look intimidating at first, but I promise it's not nearly as difficult to use as it looks!

The fact that Excel provides such a large workspace for data entry truly is amazing.

However, just because you have the space, doesn't mean you should fill the space!

I speak from experience when I say that a worksheet overrun with data can be a pain to use.

Formulas start to take a long time to calculate, Excel starts to randomly freeze and any changes to the workbook are no longer instantaneous.

Maintaining good data management practices are crucial when dealing with larger sets of data. So don't try filling out each and every cell in an Excel worksheet!!!

How often do you use AutoFilter? I think I'm guilty of using it on almost every data set I work with, even if I don't really need it!

AutoFilter is one of the most intuitive ways to navigate and better understand your data, especially when you are dealing with a large data set.

With AutoFilter, you can sort your data alphabetically, numerically, by text or even by cell colour!

Pro tip! Try pairing use of AutoFilter with the SUBTOTAL function. This way whenever you filter your data, the subtotal function will only show the sum of the records that are visible!

If you've worked with dates in Excel before, you'd know it's probably one of the most frustrating experiences.

With 1) Excel confusing the day with the month, 2) Excel sometimes recognizing text as dates and other times not and 3) Excel not accepting dates earlier than 01JAN1900, sometimes you wonder if you're doing something wrong!

I have worked with dates a lot - even with clients in different countries that use different date formats - and I can tell you the frustration never goes away.

On the upside, Excel is very robust and there are workarounds for almost everything. Phewph!

Need to extract the day value in 14JUN1865? Use =LEFT(2, 'date'). Want to extract the year? Use =RIGHT(4, 'date').

Have you ever used pivot charts to present or analyze your data?

Once you become comfortable with pivot tables, pivot charts are a no brainer.

Since pivot charts are linked to pivot tables, you can use slicers and timelines to filter and spotlight specific data, which is great for data analysis and for getting additional insights into your data.

If you add new data to your data set - all you have to do is refresh your pivot table and the changes will be automatically reflected in your pivot chart. They're super useful when it comes to working with dynamic data sets!