or

WMF Excel by Mf - 4d ago

Last Friday was a public holiday in Hong Kong.  Yes, a long weekend!  Guess what I dd over the long weekend?  I’ve created a playlist – “Excel Magic in One Minute” on my YouTube Channel.

The name says it all – it’s about bite-size Excel tricks that you can consume in ONE MINUTE.

I have to admit that it takes me much less time to create one-minute video, especially without voice over.  Having said that, that is not the reason for creating the one-minute series.

After having had delivered in-house Excel training for months, I realized that lots of participants were not aware of many “simple” tricks such as Auto Fill Options, Auto Sum, Remove duplicates etc., that could save them hours of time.  They were impressed by how quickly I could perform some tasks with just a few clicks or keystrokes.  And one day, a colleague said to me:

“Something that is easy to you may not be easy to others”.

So true!

And that’s the reason for me to create this one-minute Excel video series.  Thanks Angela for suggesting the name of the playlist – Excel Magic in One Minute.

We all know:

“Little drops make the mighty ocean.”

I believe this idea applies to learning Excel too.  When we have acquired enough bite-size Excel skills, we will turn into an Excel guru unconsciously one day.

So far, I’ve put eight videos to the playlist and of course there will be more to come.  Please subscribe to my Channel to stay tuned.  If you like my video, please give a thumb up and share with your friends.  Let’s spread the magic together.

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
WMF Excel by Mf - 1w ago
…that works even when filter is applied.

The situation:

We have a table that we would like to apply color banding based on groups.  We can achieve this by inserting a helper column to identify the sequence of each group, with the following formula:

In C2, input

=IF(A1=A2,C1,SUM(C1,1)) ‘copy down

and then apply conditional formatting based on the sequence.

Select the range A2:C29, then apply this formula to conditional formatting:

=ISEVEN(\$C2) ‘note the usage of \$

Here’s the result!  It works great… until you apply a filter on subset.

Why is that?

Because we are referring to a static (even) number for the color banding.  The number won’t response to the filter.  After the filter, only subsets of even number will be highlighted.  @_@

The desired result

We want the color banding to be responsive to filter, like the screen cast below:

With some twists to the formula for the helper column, this can be done magically.

The Challenges

We need to overcome two challenges here

1. To identify if a row is visible or not (by using SUBTOTAL)
2. To get the correct sequence for visible groups only (by using MAX)

Solution to challenge 1

This can be solved by a surprisingly short formula

In D2, input

=SUBTOTAL(103,A2) ‘copy down

This formula returns a result of either 1 or 0 for visible and invisible row respectively.

Solution to Challenge 2

When rows are hidden by filter, the corresponding values in column D will become 0.

When this happens, we want

1. to reset the Subset sequence for Subset 2, i.e. values in C5:C6 from 2 to 0
2. to change the Subset sequence for Subset 3, i.e. values in C7:C10 from 3 to 2 (this is the most tricky and challenging part)

Challenge 2.1 – Reset the subset sequence for hidden rows

We can solve this by multiplying C2 with D2

In C2, input the formula:

=IF(A1=A2,C1,SUM(C1,1))*D2   ‘copy down

=

So now, when a subset is filtered out, the subset sequence becomes 0.  The rows will also been highlighted if we used ISEVEN for the conditional formatting… but it doesn’t really matter as we won’t see it (when it is hidden).

Challenge 2.2 – Adjust the subset sequence for the next visible group

Let’s exam what the original formula does.

=IF(A1=A2,C1,SUM(C1,1))

The blue portion of the formula SUM(C1,1) tells Excel to add 1 to the number above the current cell when it detects a change in “Subset” (when A1=A2 is False).  It works fine when we do not filter the data.

However, when a filter is applied, the formula doesn’t work properly.  It returns 1 to all subsets that are next to the hidden subsets, because the sequence for hidden subset is reset to 0.

To achieve this, we need to twist the blue portion of the formula

from

=SUM(C1,1)

to

=SUM(MAX(\$C\$1:\$C1),1) ‘Note the usage of \$

By using =SUM(MAX(\$C\$1:\$C1),1), we instruct Excel to add 1 to the maximum number above the current cell.  It looks through from the very first cell on the top to the cell that is just above the current cell.  As such, the 0 resulted from hidden rows is ignored.

And we will get the correct sequence after filtering.

Challenge 2.2 is solved with this formula:

=IF(A1=A2,C1,SUM(MAX(\$C\$1:\$C1),1))*D2

Tip: You may substitute SUBTOTAL formula in D2 into the formula, i.e.

=IF(A1=A2,C1,SUM(MAX(\$C\$1:\$C1),1))*SUBTOTAL(103,A2)

so that you won’t need the extra helper column for identifying if a row is visible

Wrap up

Putting everything together, we need the following formula for the helper column:

=IF(A1=A2,C1,SUM(MAX(\$C\$1:\$C1),1))*SUBTOTAL(103,A2)

Then we apply conditional formatting to the range A2:C29 by the following formula:

=ISEVEN(\$C2)

Make sense?

Limitation

Indeed this solution is not working in all scenarios.  If you have multiple levels of hierarchical data, it works well only when you reference to the lowest level of data.

If you set the formula in helper column based on value on column C, but you apply filter to Column D, you may break the conditional format.

Try it out with the Sample File.

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
WMF Excel by Mf - 1M ago

If you’ve ever felt like:

“My Excel reports take way too long to update each month/week”

“I spend hours collating and cleaning data, updating formulas and charts and then no one reads my reports anyway, what’s the point?”

“If I could just impress the boss I’d get that pay rise/promotion I deserve”

“I need to get my Excel skills up to date so I can stand out from the crowd of other job applicants”

“I’m not sure how to approach setting up my Excel workbook the right way, so it’s easy to build and maintain”

Then you should spend an hour, or even three hours, to watch the free Excel Dashboard Webinars by Microsoft Excel MVP, Mynda Treacy.

Webinar 1 – Excel Dashboard for Excel 2007/2010/2013/2016/2019/Office 365

The first webinar will teach you how to use Excel to build this interactive dashboard below, no additional software or add-ins required, just plain old Excel and some data.

Webinar 2 – Excel Dashboard for Excel 2010/2013/2016/2019/Office 365 using Power Query and Power Pivot

Excel is evolving; in the last couple of years Microsoft have added new tools like Power Query and Power Pivot, to name a couple.

Embracing these new tools will not only enable you to get your work done more quickly, but also since less than 1% of Excel users know these tools you’re going to have a huge competitive edge in the workplace.

It’s easy to ignore these developments and continue doing things the way you always have, but that won’t get you ahead in your career, but you know that right?

If you have Excel 2010, 2013, 2016, 2019 or Office 365* then this webinar will showcase how you can use Excel’s Power Query tool to get data from multiple sources, mash it up with Power Pivot, analyse it in PivotTables to create this interactive Excel Dashboard.

*Power Query is available with all versions of Excel 2010, 2013, 2016, 2019 and Office 365. Power Pivot is available with the desktop version of Excel 2010 and Excel 2013/2016 Office Professional, Office 2019, Office 365, or in the standalone versions of Excel 2013/2016. Power Query and Power Pivot are not available for Mac.

What people are saying about the webinars

Over 35,000 people have attended Mynda’s Excel Dashboard webinars and this is what some of them had to say:

“Thank you! This webinar has already saved tremendous time and effort. I tried some of the techniques you illustrated for a cumbersome report update that I can see will cut our preparation time by 2/3’s or more. ….awesome! Many thanks!!!!”

Cynthia Tashjian

“WOW, just WOW
Can’t type more now – am trying to create a dashboard. Thank you Mynda!”

Susan

“The webinar was fantastic! I really enjoyed every moment. As you said, it was fast paced, but I am so excited to go back and review the presentation. We have been trying to get a dashboard started for my department and this really motivated me to take the lead. Thank you so much!”

Kathryn Puskar

Dashboard Course Format

If you’ve seen Mynda’s free Dashboard Webinars and you’re ready to take your dashboard skills to the next level then check out Mynda’s Online Excel Dashboard course.

It will teach you how to build amazing interactive dashboards that impress your boss, get you noticed and help you stand out from the crowd.  The best part, aside from the praise from your boss and colleagues ;-), is you’ll also learn loads of tips you can use in your everyday Excel work to get things done more efficiently.

I highly recommend the course, but don’t take my word for it. You can read further comments from past students and find out more here.

Early Bird Discount

If you register by Thursday, May 16 it’s 20% off.

So, do yourself a favour and check out the course.

Invite a Friend

Feel free to share this to your friends and colleagues and invite them to attend Mynda’s free Dashboard webinar too. They’ll thank you for it.

Disclosure: I make a small commission (at no additional costs to you) for students who join Mynda’s course via my site, but as you know I don’t just recommend anything and everything. It has to be of outstanding quality and value, and something I can genuinely recommend. After all, if it doesn’t live up to what I’ve promised you’ll think poorly of me too and I don’t want that. Oh, and just watching the course videos won’t transform your career, you have to actually put it into practice, as if reading a cookbook won’t make you a chef.

• Show original
• .
• Share
• .
• Favorite
• .
• Email
• .
WMF Excel by Mf - 1M ago

It is a common task to compare two lists in Excel.  This can be achieved with MATCH function.  But what if you want to compare a list of filenames stored in your spreadsheet, with the files you’ve got in a folder?

Will you do it manually, by eyeballing? @_@

The first challenge is to get the list of files you have in the folder.  The second challenge is that fact that you will receive files and put them into the folder from time to time.  In other words, the folder content is changing.  You absolutely want a dynamic solution that helps you to monitor status at ease, so that you know which files are missing.

Here comes Power Query to rescue.

Note: All the screenshots are coming from Excel 365.  If you are using Excel 2010/2013, Power Query has its own tab on the ribbon.  Moreover, you need to download and install Power Query for Excel as add-ins.  You may download Power Query for Excel in the following link:

If you are using Excel 2016 or later, Power Query is already built in and resides in Data tab, but renamed as Get and Transform.

The following is the step by step approach to solve the problem.

A. Convert the list of files in an Excel Table

Select a cell of the data range, then press Ctrl+T to create an Excel Table

1. Check the data range is correct
2. Check “My table has headers”
3. OK

B. Load the Table to Power Query
1. Go to Data Tab
2. Click “From Table/Range

Now you should have the Power Query Editor opened.

Note: The Power Query Editor is where all the magic happens.  Nevertheless, the intention of this post is not to explain in details all the magical stuff in Power Query.  Hence please follow the steps and focus on the solution to the problem stated.

1. Rename the query.  As a good practice, always rename your query to something meaningful.  In our case, it’s renamed as “FilesRequired“.  That is the files we are expecting in the designated folder.

C. Load the list of files in the designated folder to Power Query
1. On the Home tab of Power Query Editor
2. Click the pull down menu of New Source
3. Select Folder

Another dialog box opened.  From there,

1. Either browse to the folder path, or input directly
2. OK

In just a second, you should see the following.

1. Simply click Transform Data

Note: If you are using Power Query for Excel 2010/2013, the button “Transform Data” is  labelled as “Edit”.

Now you should have the second query loaded to Power Query Editor.  This query showcases attributes of all files in the designated folder.

1. Rename the query to “FilesReceived

A quick recap here:

By now, you should have two queries loaded to Power Query Editor.  And the queries are

1. FilesRequired – From the Table on the spreadsheet, i.e. the list of files you are expecting
2. FilesReceived – From the Folder, i.e. the files you’ve got in the designated folder
D. Merge the two queries with Left Anti Join

Now we are going to compare the Query FilesRequired to FilesReceived, in order to identify the files that are available in FilesRequired, but not yet appeared in FilesReceived.  This can be achieved by merging the two queries.

1. On the Home tab of Power Query Editor
2. Open the pull down menu of Merge Queries
3. Select Merge Queries as New

In the subsequent Merge dialog box:

1. Select FilesRequired
3. Click the header “Files Required” under FilesRequired
5. Select Left Anti (rows only in first)