- You are here:
- Home »
- Blog »
- Excel Tips & Tricks
Category Archives for Excel Tips & Tricks
UNPIVOT WITH POWER QUERY
Isn’t it just frustrating to receive a copy-paste summary table that requires analysis? Yes, we’d rather have the data source, which we can turn into a pivot table and analyze to our hearts content. Well, the world isn’t perfect, but Ken Puls has a trick up his sleeve that can potentially save us from tons […]
Continue readingUsing the Immediate Window – Excel VBA Tips
We all know that VBA allows us to create Macros in Microsoft Excel which does complex tasks in an easy way. You can create a Macro for tasks which you are supposed to do repeatedly. But, while writing VBA code we come across some issues or errors and finding them would be hard. So, we […]
Continue readingHow to Create Dynamic Drop Down Lists Using Excel Tables
Updating spreadsheets can be a pain, especially when you have to change all the Data Validation dropdown menus manually. Of course, you may use a complicated mix of INDIRECT and ROW formulas to achieve a dynamic dropdown list. But there is a much better solution. And that is exactly what Zack Barresse is here to […]
Continue readingProtect Cells in Excel by Locking Scrollbar
We all know that for some problems, Protect Sheet tool under the REVIEW tab doesn’t cut it. Well, Jordan has found another way to restrict end-user access and it has its own advantages. Let’s get to it. 1 – The Basics – Restricting Users The way this technique works is by restricting users to a […]
Continue readingIntersection Operator with Named Ranges – Excel Tips and Tricks
Hello All! We all are aware that VLOOKUP allows us to find the intersecting value of ranges or lists. It takes the value of one column and finds the corresponding value in the same row of another column. For example, VLOOKUP helps to find the telephone number of a person from the telephone directory. But, […]
Continue readingInquire – Workbook Analysis in Excel
Troubleshooting Excel workbooks of others can be mind-numbing, if not excruciating. There are various tools that VBA experts have developed over the years to help with this process. But there is one great tool that is rarely mentioned. Developed by Microsoft itself, INQUIRE is available in the Office Professional Plus and Office 365 Professional Plus […]
Continue readingRemove Auto Syntax Popup – VBA Excel Coding Tips
We all love Excel and looks like there is no need any introduction for what is Visual Basic programming. As we all know VB programming allows us to automate tasks and we can do a lot more in Excel with it. But, have you observed the pop-up shown when you make any syntax error while […]
Continue readingHow to Change Comment Formatting – Excel VBA Coding Tips
Excel MVP Jordan is continuing the Excel VBA Coding tips. While coding comments play an important role. Comments explain what certain piece of code does so that it would be easy when you share the code with others. So, Jordan unfolds the new coding tip related to comment. He explains the feature which adds color […]
Continue readingUsing SumProduct in Inventory Management Spreadsheets
In many situations, particularly when it comes to inventory management, there is a need to do SUMPRODUCT with conditions. We can definitely implement it using many hidden calculations, but isn’t it elegance that we strive for? Join Oz du Soleil to learn about an amazing trick that will change how you think about Excel formulas. […]
Continue readingHow to Change Excel Serial Date Format Using Text to Columns
Microsoft Excel allows us to show any complex data in an easy way. You could only see some format changes when you extract the data from the database. For example, say you have the data related to employee related to joining date. Then, when you extract this data from the database, then you would see […]
Continue reading
This Excel Keyboard Shortcut Hack – Save 10 Hrs Every Week
Imagine a situation where in you applied some border, changed font color, did something with alignment, added a background color or even added some formulas to that cell or group of cell, How to get rid of every thing that you applied to that cell. One thing which 90% of the People would do is […]
Continue readingCustomize Excel Ribbon – Change Case of Text
We all know that Excel Ribbon has tabs like Home, Data, review, Insert and many more. This allows us to perform some tasks like formatting the text, inserting bullets and more. Without this Excel ribbon nothing can be done. But, if you observe it, text on all tabs of ribbon is in camel case. Do you […]
Continue readingNPV Calculator and IRR Formula in Excel
Excel makes it possible for simple buy / not-buy or do / not-do financial decisions very easy to arrive at. The underpinning concept behind making such decisions is that a dollar tomorrow is worth lesser than a dollar today. That’s right, it’s because of the additional return you can get by “investing” a dollar today. […]
Continue readingCommand + T To Cycle Through Excel Cell References On A Mac
As frequent Excel users, we know the importance of absolute and relative references. On Windows operating system, one can use the F4 key to turn cycle through absolute and relative references, and everything in between. But there are a lot of users who use Excel on MAC and are clueless about how to access the […]
Continue readingData Parsing in Excel Using LEN and SUBSTITUTE function – HOT Tips
Analyzing and parsing data in Excel is really a tedious task. If it is not done smartly, then we end up in doing lot of mess. For example, say you have list of email addresses in Excel and you want to extract the extensions of those email addresses. Most of us think of using Text […]
Continue reading