Category Archives for Excel Tips & Tricks
Using Power Query to Connect Tables for Reporting | Excel.TV, Episode 55
An Excel Joke to Start Off…Did you hear about the database developer that walked into a bar? They saw two tables and tried to join them. And that’s what we’re doing this week! Last week, as you might recall, I posted an overly complicated solution to this problem based on formulas. This week, I’ve simplified it […]
Continue reading
Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV, Episode 54
CORRECTIONSeveral people pushed back on the video I release this morning and I want to thank them (and in particular Wim Gielis). This caused me to open my laptop on the train while on the way to a client’s office (so I could see if I was wrong … which I was!). When I realized […]
Continue reading
Excel Tutorial Concat Function in Excel with TEXTJOIN – Excel.TV Episode: 53
And we’re back…We took a brief hiatus for the holiday season here in the United States (Passover, Easter, and 4/20) but we’re back and better than ever. Say “Hello,” TEXTJOINThis week, I wanted to introduce you to my next best friend, TEXTJOIN. TEXTJOIN was added to Excel a few years back, and came about – I […]
Continue readingUNPIVOT 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 reading
Excel Pro Tip: Creating Custom Reports with Excel Slicers
When I teach how to develop dashboards in Excel, I’m often asked if the same visualizations and interactivity can be recreated for PowerPoint. My answer is always the same: use Excel as your PowerPoint (why? because the answer is also no). In other words, rather than pasting reports onto slides, use the spreadsheet instead as […]
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 reading