In a filtered range of data, we made few changes on the side. Then we want to replace the original values with the updated values.
What action appear on top of your mind? Copy and Paste of course.
But… if you had tried, you knew it… a regular Copy and Paste does NOT work. 😓
Let’s do something fun.
You are invited to leave your comments below. For what? For sharing with us any “weird” formula that you have ever seen in Excel. Real cases please. 😁
Wait… what do I mean “weird”?
Let’s define it in this way:
A weird formula is one that is written in a “strange” manner. It could be the uses of functions, or simply the way it is written.
Here’s my examples:
=SUM(VLOOKUP(A1,D1:F10,2,false))
=SUM(A1+A2+A3)
=SUM(A1*A2)
The above examples are real. I received a workbook where all formula are wrapped with SUM, for no particular reasons. All formula work perfectly fine without SUM.
So now is your turn. What weird formula you’d encountered? Please leave your comments. 👻
It is a common task that we want to input and maintain all data on one worksheet, and display summary on different worksheets. A typical layout will be similar to the screenshot shown above. One “Input” sheet, and then one sheet for displaying summary for each month. In this way, a total of 13 worksheets will be displayed on your workbook. Indeed, this was a question to me…
Can it be done?
Of course it can be done. BUT we could do it doesn’t mean we should do it.
My suggestion is to hold only ONE sheet to display the monthly summary on which users have the flexibility to select any month (or any period) by using Pivot Table Timeline.
Here’s the layout of the proposed solution:
You may download a sample file to follow along.
Note: The file is saved as .xlsm (macro-enable) format. Please enable content when prompted if you wish to see the auto-refresh action.
Continue reading
Got this question from my brother. He is tracking scores for game he plays with friends. The game can be played with up to four players. He wants a formula to calculate the last 10 scores of a player. He showed me the above layout and asked for the formula to SUM and AVERAGE the last 10 records of each player…… As he called me Excel guru, there is no reason I do not help him. 😁
For demonstration purpose, I change the last 10 records to 5. Indeed, I have assigned a variable (from 3-10) for user’s selection. The expected result is to obtain the SUM and AVERAGE of the highlighted cells, shown below:
And new records will come from time to time. That means the table will expand. The best way to deal with it is of course (not whole-column-referencing but) turning the data range into Excel Table.
You may download a sample file to following along.
Continue reading
Find and Replace is a very handy tool in Excel. It helps you to find a value quickly. You may then replace the value found with another value, either one by one or replace all at one batch.
To use the basic of Find and Replace, it requires no special reading as it is quite straight-forward. Perhaps the most practical tip to regular user is the shortcut keys:
However, did you know… you can also
If you are eager to learn more about Find and Replace, and hence boost your productivity with Excel, please continue to read this article. 😉
You may download a simple file to follow along.
Continue reading
As always, a picture tells thousand words. The above image explains clearly what we want to achieve, with Power Query in Excel of course.
This problem was bought to my attention when I watched the Dueling Excel Podcast #192 by MrExcel.com and ExcelIsFun channels. You may check it out here:
@MrExcel.com channel: https://www.youtube.com/watch?v=pcnWsCwEPbE
@ExcelIsFun channel: https://youtu.be/Z2Cy9IY7IBA
MrExcel solved the problem with VBA while ExcelIsFun solved it with LET functions. Super cool techniques. However it requires the latest version of Excel with Dynamic Arrays. What if you don’t have Dynamic Arrays? Power Query should be the best alternative. I was a bit surprised that they didn’t solve the problem with Power Query. So I couldn’t wait to try on my own and you know what… Yes, it can be solved with Power Query!
Continue readingDid you know… you can create such a beautiful dashboard using Excel?
Did you also know… this visually appealing dashboard was built in just more than 30 minutes?
No kidding! Check it out HERE and you will see.
Excel MVP – Mynda Treacy offers a wide range of Excel course online @MyOnlineTrainingHub where you can learn practical Excel skills at your own pace.
And from now through August 20, there will be 20% off for both Excel Dashboards and Power BI courses. So what you are waiting for? 😉
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 for us to combine all worksheets in a workbook. It could be a time consuming task without Power Query. With Power Query, it’s piece of cake. 😁
Wait? What about if you want to combine all visible worksheets from all Excel files in a folder? It requires a little more Magic from Power Query but it’s totally achievable without deep-diving into M code… just touching the surface would do. 😉
Let’s watch it in action:
(note: suggest you put the Q4 file aside for “refresh” later)
If you prefer reading to watching, please continue to read the step-by-step instructions below.
Continue readingAre you tired of seeing the (Text Import) Wizard 🧙♂️ every time you open txt file with Excel?
In this super short video, you will see probably the quickest way to open a txt file with Excel. Enjoy! 😉
Do you use stickers when you use communication apps like WhatsApp? I guess that’s almost a must-have features right now for communication apps.
Would it be nice if you can use stickers in Excel too?
Yes… you can, if you are using Excel Microsoft 365.
Go to View -> Insert -> Icons -> Stickers
Explore it and have fun!
Learn more here:
Note: This post is not about “security” issue 🤷♂️
Is the following message familiar to you?
I wonder… when you see this message, which button would you click? Please let me know by leaving comments below.
From my observation, most people would click “Don’t Update” when they are dealing with a workbook that was prepared by others.
Why is that?
Because most of the time they don’t have access to a “workbook” that is saved in someone else PC. Even the workbook (the external link) is saved in a shared folder, are you sure your counterparts have access right to that shared folder?
If a user cannot “update” the value the first (few) time they opened the file, he/she would have a tendency to click “Don’t Update” the next time they open the same file…. and worst still, he/she builds a habit of “Don’t Update” every time they see the message, which is super dangerous as he/she may not be working with the updated data that they need.
So, the question is:
Did you know… we can ZOOM IN or zoom out everything but ribbon in the Power Query Editor in both Excel and Power BI Desktop…
… by pressing
respectively.
In Excel, it is a common (but not necessarily a good) practice for people to add a thin blank column in a table to give a visual effect of divider. Like the screenshot below:
To insert a blank column in Excel is super easy… probably the reason of making such practice a common one, i guess. But when you try to do that in Power BI Desktop, you will find it super hard. After a little of Googling, you still have no clue how to do that.
Indeed I did not and do not recommend my colleague to do so when he asked me to replicate a report from Excel to Power BI. I said “NO” many many times. And then one day, he sent me a Power BI page that shows blank columns as divider (see below) and kindly asked me to replicate that for him…
Well… I know I know, I haven’t written new posts for a while. I was lazy in previous weeks; enjoying summer breezes. You know what, if I were not doing Excel on weekends, I would probably have been enjoying hiking or camping. 😎😎
Let me share some nice photos of Hong Kong.
Isn’t it nice?
Take a look at the challenge presented above. Is it possible to do it in Excel?
Be honest, without Power Query, I have no idea how to do it but manual work. 😁🤦♂️
Believe it or not, with Power Query, this challenge can be solved in just a few minutes.
Power Query skills involved:
Let’s watch it in action in my YouTube Channel: Continue reading
Following the techniques used in the previous post , we can perform many Excel magics for different scenarios. On top of my mind is to replace blank cells with zero.
The first task is to select all blank cells in the range. We can do so by using the following shorcuts:
Ctrl+A => Ctrl+G => Alt+S => k => Enter
(Note: select any cell in the range first) Continue reading
Guess what? We are going to do it with a sequence of keyboard shortcuts only. Believe it or not, it requires only 8 key strokes:
Ctrl+A => Ctrl+G => Alt+S => k => Enter => Ctrl+- (minus sign) => r => Enter
You may download a Sample File to follow along.
The keyboard shortcuts for different actions are listed below: Continue reading
Power Query is magically powerful, yet it is not well known to majority of Excel users. I think most Excel users are not even aware of its existence… So, let’s talk about how Power Query could solve a real-life problem commonly found at workplace. 😉
One day, I got an IM message from a colleague who attended a few of my in-house Excel Training courses… (I didn’t hold any Power Query session yet; so she has no idea of Power Query at all.) She asked:
If there is a quick way to combine (append) 100+ worksheets in a workbook.
I replied:
“Of course… only if you know how!” 😁 I was bad, I know.
I then asked her to share her workbook with all sensitive information removed.
Here’s the content of the worksheets (all data are fake… of course):
What she wanted to extract from the worksheets is the range highlighted above, with the metadata “Employee Name” and “Employee ID” being put together.
Here’s the layout of the expected outcome:
No empty columns; no empty rows… just a simple table!
You may be thinking… that’s easy… it can be done in just a minute. BUT remember this, there are more than 100 worksheets in the workbook and we need to do that to each worksheet.
Then we have to combine all transformed worksheets into a single table:
AND needless to say, we may have more worksheets (employees) in the workbook that we want to apply the same transformation and consolidation in the future.
How much time we need to do it manually?
I guess it could take a full day, or even more…
Disclosure: The author earns a small amount of commission (at no additional cost to you) for the product/service successfully sold through the links of [Affiliate].
A Power BI Creator Blog
Work smarter by Mastering Functions in Excel
Exploring open roads without breaking the bank
Work smarter by Mastering Functions in Excel
Your next stop in mastering Power Query and Power BI
Work smarter by Mastering Functions in Excel
Leading Consultants on Power BI and the Microsoft Data Platform
Helping lawyers make the most of Microsoft Excel
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
More geeky stuff from the author of www.excelguru.ca...
Work smarter by Mastering Functions in Excel
Peltier Tech Excel Charts and Programming Blog
Turn your data into opportunity
Q&A about Excel
Doug Glancy's Excel Site
A blog of Excel, Dashboards, Visual Basic for Applications, Data Analysis, Operations Research, and Visualizations.
Advanced Excel Techniques with XOR LX
Collection of Excel Know How and little Excel Tips and Tricks
Charles Williams on 'Making Excel go Faster'
Save Time and Money in Business
Work smarter by Mastering Functions in Excel
Work smarter by Mastering Functions in Excel
Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity
