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 […]
Tag: Excel
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 […]
Working with Fiscal Quarters (Excel)
Many businesses have their fiscal calendar that’s different from Gregorian calendar and for analysis/reporting purposes we need to easily translate between the two. In this blog, I’ll show how to do just and then do a summary of expenses by fiscal quarters from a dataset of Gregorian calendar dates. Then I’ll also demonstrate turning that […]
Classifying customers by age (Bin, Bucket, Category)
In this blog, I’ll share a method to map customers, based on their date of birth, to appropriate classification (for analytics), and appropriate price for services as per an established table/scheme. This will be done in Excel. Objective Imagine the business owner has a different pricing scheme based on customer categorization; a common practice. The […]
NFL 2019 regular season is done. What have we learned?
Now that the 2019 NFL regular season is in the books, we have complete data available about the teams’ performances. What can we glean from them? In this blog, I share some findings, answers, and the process of analytics behind discovery of the answers. I will focus on overall team performance data, not individual positions, […]
A Real Data Merge Challenge (Excel)
Instead of demonstrating what each of the thousands of functions does in Excel, it’s most helpful, I think to demonstrate how to solve real-life scenarios and the formulas that come in handy as secondary. Today, I present this. The Challenge: You are given 2 datasets of fruits and their prices. One set includes some items/fruits […]
A Christmas Tree in Excel (Happy Holidays!)
Merry Christmas and Happy Holidays, everyone! Here’s a quick and dirty animated tree decorated by me for Christmas in Excel (yes, I have a real one at home but not as wonky). It uses Excel’s basic conditional formatting and number generator functions to create this cute, blinking tree. With a little VBA code, you can […]
Claim: Most 13th of the months fall on Fridays anyway (+Test)
Someone claimed recently that most 13th of the months fell on Fridays in recent years. This claim came upon the heels of my blog on how to find Friday-13ths for any year in Excel. So, obviously I had to check it out for myself if the claim is true. And I want to check it […]
Weather report in Excel
Below is a 10-day weather report for Seattle in Excel using external data connections. The following is the live embedded sheet (however, online Excel does NOT support external data connections in the browser…yet). To get the data to refresh, just save the file locally or click Open in Desktop App button along the top of […]
Happy Friday (the 13th!). Have you ever wondered…?
If you’re anything like me, you’ve at least once wondered: How can I get Excel to show me all the Friday-13ths in any given year? Well, I have (don’t ask me why)…and here’s the solution. I found a couple of ideas on how to do this, but my method I believe, is the most straight-forward […]