Why I Made This… I grew tired of searching for a well-formatted web page where I could easily grab a list of countries and drop them into Excel (mostly to make drop-down lists). So I took matters into my own hands and have painstakingly listed out all the countries in the world, their three-letter abbreviations, and their capital cities. All this data is listed with the intent of copying them from the list and pasting them into Excel. That’s it! No reformatting, no weird spacing, no removing hyperlinks, notta! And yes, you are most certainly welcome :) Table of Contents - All The World Countr ..read more

Stacked Bar Charts Missing Total Labels In Excel Unfortunately, Microsoft does not have the ability to insert data labels at the top of vertical or horizontal stacked bar charts. It’s very interesting Microsoft decided not to incorporate this capability as it is a seemingly basic charting need while using stacked bar charts. While resolving this missing feature has been asked of Microsoft (please upvote!) by the customer base, there does not appear to be any momentum yet to add it to Microsoft Excel by the developers. Luckily, we can use some creativity to add total chart series and make it lo ..read more

Waterfall/Bridge charts are commonly used in the finance world to show up and down variances between two periods of time. Typically, these comparisons walk you through the ups and downs of “Prior Year vs. Current Year” or “Forecast vs. Budget”. If you are unfamiliar with these sorts of charts, you can read my article explaining what exactly a Waterfall Chart is used for. In Excel, there are two ways to build a Waterfall Chart. You can either: Use the built-in Waterfall Chart type Build your own using a stacked bar chart In this article, we will be exploring Excel’s built-in bridge chart ..read more

VBA Coding With Shape Objects In this comprehensive guide, you will be learning all the ways you can create and manipulate shapes with VBA macros. Shapes are objects you can insert into your spreadsheet through the Insert Tab via the Shapes gallery button. These objects can add visualizations to your dashboards, store text, or even serve as buttons to launch macro code. Here is an outline of the topics covered in this guide: VBA Shape Coding Topics - Create A New Shape - Adding Shape Text - Modifying Colors - Change Shape Type - Create A Macro Button - Looping With Shapes Creating A New Shap ..read more

Deleting Rows With For Each Loop Misses Rows When I first attempted to delete rows using VBA code I thought it was going to be a piece of cake. I went into the VB Editor and typed out a simple For Each Loop in order to delete each row that contained the word ‘Product C’. With only a few lines of code, I was ready to test it out. Sub Delete_Product_C() Dim Cell As Range 'Loop through range and delete [Product C] rows     For Each Cell In ActiveSheet.UsedRange.Columns(1).Cells         If Cell.Value = "Product C" Then Cell.EntireR ..read more

I’ve had quite a few IT administrators request that I provide an MSI Installer file (often referred to as a Windows Installer or Microsoft Installer) to assist them in deploying an Excel add-in their company has purchased to their end-users. With my limited knowledge of anything coding or IT-related outside of VBA, I had always been intimidated to build such a program. Luckily, with a bit of research, there are many programs out there that build these MSI Installer programs for you with a few clicks of the mouse. While many of these programs can be quite costly, they may be financially worth ..read more

Can You Return All Matches With XLOOKUP? Currently, you cannot return multiple lookup results with Excel’s XLOOKUP function. You can learn exactly what XLOOKUP can and can’t do in my XLOOKUP function overview guide. Luckily, there is hope! You can utilize Excel’s FILTER function to get the same effect. The below example shows a single Excel formula that lookups up a person’s name from a data table and returns all the results associated with that person. In this article, you will learn how to write an Excel formula that will emulate an XLOOKUP function returning all match results based on a pa ..read more

Versions Of Microsoft Excel For XLOOKUP Function Excel 2021 (PC and Mac) Excel for Microsoft 365 (PC and Mac Versions) Excel Online Excel for Tablets (iPad and Android) Excel for Mobile (iPhone and Android) What Was the First Excel Version With XLOOKUP In It? The XLOOKUP function was publically released to the monthly channel of Microsoft 365 (subscription-based) on March 31, 2020. Prior to this date, the XLOOKUP function was pushed to the Beta version of Excel on August 28, 2019. The function remained in beta for approximately 7 months while a small population of Excel users teste ..read more

Spreadsheet Sample Data in Excel & CSV Formats I have put this page together to provide everyone with data that you would come across in the REAL WORLD. Whether you are looking for some Pivot Table practice data or data that you can flow through an Excel dashboard you are building, this article will hopefully provide you with a good starting point. All the example data is free for you to use any way you’d like. I have saved the data in both an Excel format (.xlsx) and a comma-separated values format (.csv). What Can This Data Be Useful For? Feeding Dashboards Manipulating in Power Quer ..read more

Table of Contents • Begins With Search • Ends With Wildcard Search • Contains Search • Does Not Contain Search • Download Example Excel File Filter Function and Partial Matching (Wildcard) Criteria Unfortunately, as of this writing, the Filter function does not contain an input to perform partial match (wildcard) search criteria. This means you cannot execute common filtering commands such as: Begins With Ends With Contains Does Not Contain While the Filter function appears to be lacking some significant functionality, you can luckily combine other Excel functions to achieve the d ..read more