In this blog, I demonstrate how to enumerate all the system audio files that comes with Windows, put the list in an Excel file. Then use that list to play each one with a click of a button. First, let’s look at generating the list of audio files in our system folder. They’re in windows\media […]
Month: January 2020
Three sure ways to find Outliers in your data
I often see questions about how to find outliers in a dataset of any size the effective way. From what I’ve seen, there are many suggestions/responses and most of them are INCORRECT unfortunately. Here, I’ll show you 3 accurate and proven ways to find outliers in any data. Let’s start with a diverse dataset: I’ll […]
Creating Word Cloud accurately and easily in Excel
There are plenty of designers, templates, and online services that let you create Word Clouds (aka Tag Cloud), but do you know how to create one yourself in a short notice, without paying anyone anything? If you have Excel and some creativity, then you can create a Word Cloud just as good as any! It’s […]
There’s no formula for that! Grab a snippet of data-set.
Often I find myself working with a subset of the full dataset for experimentation, trials, and exemplification. For instance, my dataset may have 33,000 records but I want to work with about 100 records first in a separate sheet. Sure, I could (as most do) select the desired columns and continue to scroll down to […]
In-cell character-based charts
We all know about the extensive charting capabilities in Excel. In this blog, I show a quick and easy way to create charts within-cells (not on a separate canvas!), next to data. Much like sparklines but without using that feature. It’s simple, fun, and effective. Let’s try 2 examples. First, we’ll use some movie ratings […]
Clean & glean from raw transaction data
You’ve just been delivered some raw transactional data from a new salon owner, who’s very optimistic about her new business but also cautious. She wants to understand her customers, patterns during the day and week so she can adjust her prices, staffing, scheduling, stocking, and more. Your objective: Based on the raw transaction records dump, […]
Forecasting for your team
Here I touch on a quick but effective way to do forecasting based on historical data…this without the help of any chart. Scenario You have data on your customers’ spends at your shopping mall based on how many miles they drive to get there. The dataset is below. Your objective is to find out how […]
Creating a GEO learning app in Excel and PowerBI (part2/2)
This is a two-part blog series. To follow along, see part 1 followed by part 2 (this). If you haven’t see a quick animation of the interface, it’s available in part 1. Previously, I demonstrated how to get the dataset ready and create some basic charts in Excel about the states of USA. Now, we […]
Creating a GEO learning app in Excel and PowerBI (part1/2)
In this blog, I’ll show you some very effective and efficient ways to get vital information about all states of the USA, and eventually create visuals, then interactive visuals, then an interactive application online. All from starting with the basic names of states. I wish every kid and adults would have this type of application […]
Automatically Creating Datasets (Excel)
Often we have to start with a sample dataset for experimentation or verification even if the dataset isn’t based on real-life data. In many cases, people start manually typing their own data to create the sample, or search online for some sample datasets. There’s a better way! All data can be automatically generated within Excel […]