How to keep your data clean with Advanced Excel

I had a fun job working as an administrative assistant at a local college for a few years. Part of my job was to process expense reports.

To streamline things, I created a reimbursement form for the professors to fill out after their European trips. However, since this was a language department, most of the professors liked to explain things in lots of words. While why they had to order that specific item at the Deux Magots was an interesting enough story, I just needed to know it was lunch and how much it cost!

I needed to prevent them from trying to write a novel in a cell. How did I do that?

EXCEL ADVANCED

Next Class Offered

Session One: Wednesday April 27 6:00pm-9:00pm

Session Two: Wednesday May 4 6:00pm-9:00pm

Special Course Rate $100.

Visualizing Data Makes Decisions Easier in Advanced Excel

Setting up charts correctly and according to exact specifications is an important part of many papers and studies for my various clients.

However, in my personal case, I find that many people with whom I interact (<ahem!>Husband</ahem!>) respond better to visual representations of data than to the figures themselves.

You know you want to learn advanced charting, and the finer details of formatting, as well as combination charts.

EXCEL ADVANCED

Next Class Offered

Session One: Wednesday April 27 6:00pm-9:00pm

Session Two: Wednesday May 4 6:00pm-9:00pm

Special Course Rate $100.

Pivot Charts are Easy and Shouldn’t Belong in Advanced Excel

People who just want the data in an easy-to-understand format are in love with PivotTables and Pivot Charts.

There’s also this idea that they’re difficult and esoteric, which is why most curricula (including this one) put them in an advanced course.

Thing is, once you catch on to a few basic principles, they’re childishly easy.

Wanna learn how?

(

Excel Advanced

This will be a six-week course starting:

Thursday, January 6, 2022

7:00-8:00 pm EST

Tables and Structured Referencing is Beautiful in Intermediate Excel

I was working on some data once, calculating by a lot of criteria whether or not one of 8,000 computers was supposed to get an operating system upgrade, or if we just needed to buy a new computer.  I had a formula to evaluate it based on some 10 or 12 criteria.

Then I hit this one computer and in looking at it, knew that the evaluation in my formula turned up the wrong result.

Oh dear.  (Only I didn’t say “Oh, dear.”)

I was looking at thousands of computers.  What if I got some others wrong?  (Hint:  Yes, the result was wrong in about 150 cases).

I rewrote the formula, tested it against some known results, and yes!  Turned out correctly.

So, I had to copy that formula down an 8,000-row column, didn’t I?

No, I blasted well didn’t.  That’s where the beauty of tables in Excel comes in.

Want to learn that?

Excel Intermediate

Excel Intermediate is a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

Named Ranges in Intermediate Excel Make Calculations Easier

I wish I could brag about how named ranges help me professionally.  They do, but I don’t have a cute story about it.  However, it is easier for me to set up tables and formulas when I think about what each cell’s contents represent rather than the actual cell references.  Seriously, Z3432?  Who cares?  I want to know what it means.

Named ranges also come in handy when you start working with the table objects in Excel.  This concept makes structured referencing that much easier.

Wanna learn how?

Excel Intermediate

Excel Intermediate is a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

Excel Intermediate and Problems with Zip Codes

I was once hired to set up an email list for a wildlife rehabilitation center in New England.

Excel list? Check.

Mail Merge for printing the labels? Check.

Quick job. Easy turn-around. No problem

*grin*

Until…

I printed out thousands of (okay I stopped the job after a couple of sheets…) of Northern New England zip codes with only FOUR digits instead of five.

How did that happen? The zip codes were correct in the spreadsheet!

In session two, we’ll be learning how to avoid errors like this easily as well as other custom masks, advanced formatting themes, merging, and transposition tricks that make the spreadsheet not only useful but easily readable.

Sign up and learn!

Excel Intermediate

This will be a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

.

Multivalued Nested IFs and Other Advanced Excel Fun

“We have 8,000 computers to upgrade the operating system. Some of them aren’t licensed for it and will just need to be replaced. Some of them haven’t touched the network and we need to find them. Others? Well, we need to group them by department to upgrade. Oh, since a lot of them are laptops that won’t be on a network fast enough to push the new OS to them, we’re going to need to chase them down and bring them in to User Support to upgrade.”

Could I have kept track of this list and evaluated it using Python or something?

Well, no. I didn’t know Python at the time!

What about SQL? Well, yeah, but no one in the office was using that tool for such a small list.

Can Excel handle it?

At only 8,000 entries? You bet it can!

I created a table with a single-line 13-value nested formula that would evaluate the many criteria needed to track the OS upgrade process.

I even made a dashboard from it for a boss who was really into visual trackers.*

You can learn what I did and how I did it in Session One. This is flat-out my favorite session of Excel to teach. Don’t laugh at me for loving this. I can’t help it.

But maybe you’ll have fun with this, too. C’mon, you know you wanna…

Excel Advanced

This will be a six-week course starting:

Thursday, January 6, 2022

7:00-8:00 pm EST

* PivotTables and PivotCharts won’t be until Session Four

Fifty Pages of One Column and How Excel Classes Can Help You

“I need you to hurry up and get these reports printed for a manager’s meeting!” my boss said as he rushed by one morning a couple of decades ago.

He was new to the office and didn’t know about some traffic patterns or bottlenecks that could happen and much later than I expected. He’d been working on the reports the night before – large, complex spreadsheets. He wanted some handouts.

I liked the guy and wanted to help him impress his new team, so of course, I was happy to rush and get what he wanted printed and copied.

Except…

When I went to print out the report, I got this horrible layout and fifty pages of single-column figures with no way to reference what they meant!

Gulp! I neglected to preview what I was going to print before I sent it to the printer. Anyone want to guess how many pages of a single column spat out uselessly before I caught it?

Session One of Intermediate Excel will teach you how to solve this problem. Want to know what I did?

Sign up for the class.

Oh yeah, this session will also teach 3-D formulas, watch windows, and linking multiple worksheets. I just don’t have any stories about that other than the fact I use spreadsheets with them every working day.

Excel Intermediate

This will be a six-week course starting:

Tuesday, January 4, 2022

7:00-8:00 pm EST

Why I Hate Tips and Tricks in Software Instruction

I’m about to play the hypocrite a little here.

A vast majority of my blog posts here on Figart Consulting are little nuggets of information about software. Why? Because it’s easy. I can write a 500-1000-word piece on some little bit of productivity software knowledge that might be useful to you. It might save you a half an hour when you’re trying to accomplish something.

And it’s still not really as useful as it could be.

There’s a reason for that, and it’s why I hate these little classes in software applications. You know the ones, “How to manage your Inbox” or “How to Make Pivot Tables in Excel.” I’ve taught classes like that. Shoot, I’ve written classes like that. I don’t like them.*

Why are they taught? For the same reason I write the articles. They’re easy – both for the teacher and the student.

What Tips ‘n Tricks Are Good For

It’s not that these little classes are unilaterally useless. They’re not. It’s that they’re truly only worthwhile to a peculiar subset of the population. The Gee, I Didn’t Know That! Nifty Tip is really only good for someone who knows the software in the first place. When I say “knows,” I do not mean someone who can look at the interface and puzzle it out. I don’t even mean someone who can make the software do a single task or figure out something.

When I say “knows,” I am speaking of someone who understands the underlying logic behind the software, and how its features might integrate to perform multiple complex tasks. The reality? Most people don’t know the software they use very well. (And if you argue that you shouldn’t have to understand its underlying logic to use it, I have to ask is your task truly complex enough to require software?)

If you do understand the software you’re using, yeah, those little tips and tricks will be quite useful. It’ll save you some time and you’ll be able to apply them in a useful way.

Where Tips ‘n Tricks Fall Down

I’ve taught a lot of classes in Excel. The class I want desperately to teach, and have never had the opportunity to do so, is the How to Design a Spreadsheet class. There are reasons for this. Not only is course development expensive, this is not conducive to the one-day training most employers prefer. For it to work well, I’d have to teach it more like a college class – several days over a course of many weeks – including homework and a project.

This comes from an hour on the phone doing tech support that was really turning into a development class when a user inherits a workbook that no longer works properly because changes need to be made in how data is tracked.

This comes from helping someone update a presentation using the tools available in PowerPoint, only to find that they do not know how to structure an effective talk.

This comes from re-formatting articles that cross my desk for editing purposes because the user doesn’t understand format and text flow in a document.

None of these problems can be fixed by Tips ‘n Tricks, but by understanding what the software is supposed to accomplish and understanding what task you’re trying to accomplish. Too much of what we do with computers really does have a flavor of, “Don’t bend that wrench, get a bigger hammer!”

Don’t get me wrong. I know why we don’t do it. Learning the principles behind anything is hard.

We Need to Teach Principles Based Productivity

Even though principles-based learning is hard at first, we need to teach it. The sad reality is that most people are taught to be able to do well on standardized tests. In the real world, though, you do not have standard problems to solve. I don’t care what your job happens to be, if it’s complex enough to sit down in front of a computer to it (and that includes being a cashier), you need to be able to analyze what you see in front of you, and plan not only a solution, but a way to avoid the problem in the future.

This means teaching people to sketch out what they want on a whiteboard before they sit down to create a spreadsheet. This means making an outline before starting a document. This means brainstorming a talk, maybe even writing an outline, before creating presentation slides.

To the computer professional, the expression, “Never skip the design phase” might be common. The thing is, that skill is necessary for more than just the programmers. You need to know where you are going. You need to know what you want to accomplish. You need to know what your tools can do. Without that, tips-n-tricks are pretty useless.

* This is not a secret to anyone who has asked me to write such a class, by the way. They’ve gotten this earful more than once.