In a prior post (How to unhide or delete name ranges in Excel?), we talked about how to unhide and use the name range interface (Ctrl+F3) to select and delete name ranges. But what if you want to use VBA to delete name ranges quickly or with certain conditions? It is not uncommon to see older worksheets ending…
Sometimes you want to apply the “IFERROR” in Excel to trap errors and avoid “ugly” output, such as below: Let’s say for a report to your manager, you may have cleaner output. Instead of showing “#DIV/0”, you want to show “n/a” as the data point is not available. The best way is to use the…
A data table will work even if it is on a separate worksheet as the input cells. If you want a step by step solution, skip to the section below. You can download an example in Excel here: https://excelhelphq.com/wp-content/uploads/2014/08/Datatable-Example.xlsx Strategy The strategy is to have your original excel sheet’s input cells to change only when the…
Click here to download the Three Variable Data Table Example. Introduction The key to making a three-variable data-table (or any higher number of variables, such as 4, 5, etc.) is to use the offset function to populate a set of values into the base calculation. (The data-table’s constraint of only having two variables remain unchanged.) Setting Up…
Click here to download the Monthly Model Template with all the necessary start & end dates, month, quarter, and “days in period” calculations. This model is the perfect template to help you set up a monthly model for financial or operational data. You can easily extend this template by copying the column into empty cells…
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) The VLookup function can be very handy, but a few common errors throw a lot of users off. Here’s the top 5 sources of errors that I have frequently encountered over the years. Trust me, quite a few users spend hours trying to find out why the vlookup value does not…
In a previous article, I created a step-by-step guide on how to create a clustered stacked column bar chart from scratch. For the sake convenience, click here to download the Cluster Stack Chart Template. The template will come in handy. This tutorial also shows you how to customize to fit your exact needs. Terminology Here’s a few…
This one’s a real time saver. While writing tutorials, I used to do a screenshot, and paste image into an image editor to get all the column and row headings into an image. This requires a lot of work, such as moving and cropping the image. Now, I can just copy from Excel and paste…
This tutorial shows you how to use the waterfall chart template file Excel that supports negative values and columns representing no change. Download here to download: Waterfall Chart Template How to use the template? The template is ready to use right out of the box. All you need to do is change the input cells Enter…
This tutorial will show you how to create a clustered stacked column bar chart – step-by-step, so there is no way you will get confused. If you like this tutorial and find it useful, have questions or comments, please feel free to leave me a comment! I would love to hear your feedback! Download here…