Excel

Pro Excel secrets and magic. The kind of industry knowledge to put junior finance guys out of business.

Roll On Through: Create an Iterative List Without Any Real Coding

This isn't standard programming...THIS IS EXCEL!

Hi there dear reader, so, I know that in the previous article that I penned, I stated that I'd be showing you how to create buttons that you can interact with (and I know you know that because you're reading these in order...right?) but I recently had to create something that was so cool, I had to write about

Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Sep 13
Read

Dynamic Tension! Creating and Using Dynamic Named Ranges

Auto refresh data in pivot tables

First things first, 10 points to anyone who understands what the title is referring to (that's either Charles Atlas' workout philosophy, or one of core tenants of Bokononism in Kurt Vonnegut's standout Cat's Cradle).

Now, with my obscure reference quota filled for the day, let's assume that you've been working as an analyst for some time now. As stated in

Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Aug 31
Read

Importing Excel Datetimes Into Pandas II

Pandas and Excel Pt. 2

What if, like during my data import task a few months back, the dates & times are in separate columns?  This gives us a few new issues.  Let's import that Excel file!

import pandas as pd
import xlrd
import datetime

df = pd.read_excel("hasDatesAndTimes.xlsx", sheet_name="Sheet1")

book = xlrd.open_workbook("hasDatesAndTimes.xlsx&
Pandas Matthew Alhonte avatarMatthew Alhonte Matthew Alhonte avatar
mattJan 15
Aug 20
Read

I Owe My Job to Mr. Robot

Entering the Dataverse

What's up data gang? If you've been reading along throughout this journey, you'll realize that all of my posts have something in common (other than Excel...smartass), they all assume that you're already working with data...even if it's just cursory exposure. Well, every journey of a thousand worksheets begins with a single lookup, and hopefully this story will help

Data Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Aug 14
Read

Importing Excel Datetimes Into Pandas

Pandas & Excel, Part 1

Different file formats are different!  For all kinds of reasons!

A few months back, I had to import some Excel files into a database. In this process I learned so much about the delightfully unique way Excel stores dates & times!  

The basic datetime will be a decimal number, like 43324.909907407404.  The number before the decimal is the day,

Pandas Matthew Alhonte avatarMatthew Alhonte Matthew Alhonte avatar
mattJan 15
Aug 13
Read

Active-X Gonna Give it to Ya

Named Ranges and the Active Xs who Love Them

Hi there dear reader, we've now reached a crossroads in your ability to use Excel as a means of data analysis. If you've been following along, you've learned how to use basically every tool that an Excel analyst would need to have in the front of their minds. For those just joining us, these tools are as follows:

  • Pivot Tables
Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Aug 10
Read

Your Invitation to the Pivot Party

Adventures in Excel

I know its been a while, but you'd be surprised how little time an energy you have when you're building a plane while you're flying it...or perhaps, if you've been reading along with this series, you won't.

Before jumping right into the deep end and showing you some lesser known (but supremely useful) tricks to save even more time

Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Jul 21
Read

Slicing Excel: The Way of The Knife

Adventures in Excel

If you've been following along with the posts in this series, and you've been putting some of the scenarios into practice, you've now crossed a crucial juncture in your never ending quest to master the bloated beast that is Excel. To put things in perspective, if you've conquered all the quests that I've set you upon, you should now be

Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Jul 05
Read

Yes...If(And))

Adventures in Excel

If you've been following along on our proverbial Hogwarts for budding Excel wizardry, you would know that we recently crossed an important Rubicon upon which all Excel, and indeed all computer programming languages are built: The mighty IF statement. Remember, the IF statement allows the wielder to fork reality at their whim, to bend the code on a lark, to

Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Jun 20
Read

Getting Iffy With it

Adventures in Excel

If you've been following along, we discussed in the last several posts of this series how, if you're not working in a very "tech forward" organization (like my two compatriots on his site), but you have the same title, you're probably obtaining your data from another department (or it might be a sentient sponge, or a gang of squirrels with

Excel Max Mileaf avatarMax Mileaf Max Mileaf avatar
snacksJan 15
Jun 10
Read