Work smarter by Mastering Functions in Excel My name is WONG, Ming Fung. I have keen interest in Excel. That’s why I registered wmfexcel as the site’s domain. The tagline “Work smarter by Mastering Functions in Excel” then follows to make it sound more appealing, hopefully.
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”.
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.
When rows are hidden by filter, the corresponding values in column D will become 0.
When this happens, we want
to reset the Subset sequence for Subset 2, i.e. values in C5:C6 from 2 to 0
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.
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
=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:
Tip: You may substitute SUBTOTAL formula in D2 into the formula, i.e.
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.
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!!!!”
“WOW, just WOW
Can’t type more now – am trying to create a dashboard. Thank you Mynda!”
“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!”
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.
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.
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
Check the data range is correct
Check “My table has headers”
B. Load the Table to Power Query
Go to Data Tab
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.
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
On the Home tab of Power Query Editor
Click the pull down menu of New Source
Another dialog box opened. From there,
Either browse to the folder path, or input directly
In just a second, you should see the following.
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.
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
FilesRequired – From the Table on the spreadsheet, i.e. the list of files you are expecting
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.
On the Home tab of Power Query Editor
Open the pull down menu of Merge Queries
Select Merge Queries as New
In the subsequent Merge dialog box:
Click the header “Files Required” under FilesRequired