Category Archives for "Excel Tips"

2 What is Intersect Operator in Excel and How to Use it

Watch Video – Using Intersect Operator in Excel Intersect Operator in Excel can be used to find the intersecting value(s) of two lists/ranges. This an unusual operator as it is represented by a space character (yes that’s right). If you use a space character in between two ranges, then it becomes the Intersect operator in Excel. […]

Lookup and Return Values in an Entire Row/Column in Excel

VLOOKUP is one of the most used functions in Excel. It looks for a value in a range and returns a corresponding value in a specified column number. Now I came across a problem where I had to lookup entire row and return the values in all the columns from that row (instead of returning a […]

1 Volatile Formulas Detected in Excel – Keep Your Distance

Last week, I came across an excel problem in a forum. I immediately sprung into action and created a long formula that started with OFFSET(). Within a few hours, it was shot down by other excel experts as it contained volatile formulas. I immediately recognized the cardinal sin I had committed. So with this confession, […]

17 How to Create a Dependent Drop Down List in Excel

Watch Video – Creating a Dependent Drop Down List in Excel An Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards. It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down. This could be […]

2 Avoid Nested IF Function in Excel…VLOOKUP to Rescue

A lot of times people ask me to help them in rectifying an error in their nested IF function. And sometimes their formula is bigger than this blog post (seriously! not kidding). However, in some of the cases, this long formula can be cut short by using the VLOOKUP function. Avoiding Nested IF Function Consider […]

21 How to Create a Scroll Bar in Excel – Step by Step Tutorial

Watch Video – Creating a Scroll Bar in Excel A Scroll Bar in Excel is what you need when you have a huge dataset and you don’t want it to hijack your entire screen’s real estate. It’s a great tool to use in an Excel Dashboard where you have to show a lot of data […]

7 Conditional Formatting to Create 100% Stacked Bar Chart in Excel

A colleague asked me if there was a way to create a dynamic in-cell 100% stacked bar chart in Excel for three product sales. As usually is the case, there were thousands of rows of data. There is no way she could have used Excel in-built charts as that would have taken her ages to […]