Category Archives: Excel Tips

Do Not input “-” for zero. Use proper formatting in #Excel

Do you input a hyphen to represent zero? If you do, please stop doing so in the future. The proper way of displaying “-” for zero is to apply relevant cell format to it. E.g. Using Accounting format with no … Continue reading

Rate this:

Posted in Excel Basic, Excel Tips, Format | Tagged , , , | Leave a comment

Convert numbers of different digits into text of fixed digits in #Excel

The situation We have an extensive list of numbers in different digits, say from 10 to 13 digits. The problem is, they are supposed to be numbers in thirteen digits stored as text. (Of course, in our example, we work … Continue reading

Rate this:

Posted in Excel Tips, Formula | Tagged , | Leave a comment

7 Copy and Paste Tricks I wish I knew sooner in #Excel

Regardless of what we do with Excel, Copy and Paste is something we do on daily basis… or even hourly basis. 😁 You may be thinking, every one knows Copy and Paste. Yes, probably. But is every one using Copy … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Protect your cells in #Excel

Think about this… you have spent hours of work building your wonderful Excel template. Needless to say there are lots of formulas that took you hours of thorough thoughts and considerations, on top of the beautiful formats and layouts. You … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

[Video] – Swap cell contents with a simple mouse trick

It is super easy to move cells around by drag and drop. The standard drag and drop action however replaces contents in the destination cells. What if we want to preserve destination cells and just shift them up or right? … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

SUMIF vs. GETPIVOTDATA in terms of performance in #Excel

Did you experience a slow workbook? How slow? Let’s say it takes more than 2 minutes to just open the file. This is actually what a colleague asked me for help. When I opened the workbook, I saw LOTS of … Continue reading

Rate this:

Posted in Excel Tips | Tagged | 2 Comments

The very hidden gem of AutoFilter in #Excel

Don’t get me wrong. I am not talking about the Filter button sitting on the Data Tab of ribbon, although they look almost exactly the same. I am pretty sure that most people who use Excel five days a week … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

The proper way to filter values not equal to zero in #Excel

I didn’t think of blogging about this topic before. I thought that it is so straight-forward. Nevertheless, there is another way of doing so which is even more straight-forward BUT may give you unexpected results later on. And to my … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Reapply Filter in #Excel

Did you notice that there is a “Reapply” button next to Filter on the Data Tab of ribbon? And wondering what it does? πŸ€” Indeed, the “Reapply” button is dim until you have applied filter to at least one column. … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Data Cleansing is never easy, even with #Excel Power Query

Don’t make me wrong. I am not complaining Power Query at all. If you have followed me for a while, you should know that I am a big fan of Power Query indeed. It is simply powerful! With Power Query, … Continue reading

Rate this:

Posted in Excel Tips, General, Power Query | Tagged | Leave a comment

Filter: It’s easy and not easy

Applying filter in Excel is a very common task. We used to filter a dataset and focus on a subset of data that we are interested in. It is super easy to filter. The problem is, are you sure you … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Add emoji to your numbers 😁 in #Excel

Is it possible in #Excel? Of course, as you have just seen it! 😁 Indeed, it’s easier than you may have thought about. We can do it by custom format. First of all, select the cells that you want to … Continue reading

Rate this:

Posted in Excel Tips, Format | Tagged | 2 Comments

Quick Tip – Close all #Excel workbooks at once

Have you ever had more than 10 workbooks opened at the same time? Did you spend too much time to close them all one by one? If so, you will love this simple trick to close all workbooks simultaneously! The … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Dense Ranking with Power Query – Unexpected Behavior and Workarounds

This post is inspired by the article – Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub. The first part of this post is a summary of what I read from Philip’s post. While the second part is … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | 2 Comments

Hide and Seek in #Excel – PivotTable Fields

Have you ever encountered a situation like this? How are we supposed to work with a PivotTable without seeing the data fields? I was so lost when I saw this from a workbook on my friend’s notebook. He came to … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment

Replace values in filtered ranges using Copy and Paste trick in #Excel

Situation: 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… … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Understanding Find and Replace in #Excel

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 … Continue reading

Rate this:

Posted in Excel Tips | Tagged , , | Leave a comment

Quick Tip – Probably the quickest way to open txt file with #Excel

Are 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! πŸ˜‰

Rate this:

Posted in Excel Tips | Leave a comment

Hide and Seek – Scroll Bars and Worksheet Tabs in #Excel

Did you encounter this?Β  All the worksheet tabs and scroll bars are gone in your Excel workbook!!! 🎢🎢 Tell me… how am I supposed to work without you…🎢🎢   No worries!Β  They are not gone.Β  They are just hidden (by … Continue reading

Rate this:

Posted in Excel Tips | Tagged | Leave a comment

Lovely stickers in #Excel 365

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 … Continue reading

Rate this:

Posted in Excel Tips | Tagged , | Leave a comment