Rinse hidden apostrophes & other junk out of a spreadsheet
7DEC12 A recent spreadsheet was giving me hell. It was littered with hidden apostrophes, carriage returns and was a general pain. Manually correcting 1500 rows and 28 columns was going to be ridiculous. The cause was the importing of files that weren't Excel or CSV....
#3 of the Top 5 Excel Functions : AND OR (Virtual Insanity)
# 3 In our Top 5 Excel Functions, the Operators AND & OR Excel AND and OR functions are fairly straightforward. In this post, we see cover details and warnings. And we wrap up with a Thought Exercise rather than examples. So, let's hit it! If VLOOKUP were a song,...
#2 of the Top 5 Excel Functions: VLOOKUP
#2 of my Top 5 Excel Functions: VLOOKUP If VLOOKUP was a song, this would be it, I Like it Like That! VLOOKUP can turn a 2-day ordeal into a 5-minute task. Reasons to use VLOOKUP List of 400 addresses each needing to be assigned one of 9 Regions Master...
19DEC12: Excel formula workshop in Chicago
After teaching an Excel workshop, students requests a workshop on Excel formulas. We're doing it! I'll be teaching ... EXCEL FORMULA WORKSHOP Wednesday, 19 December 2012 $20 6:30pm to 8:30pm The Holiday Club 4000 N Sheridan Outstanding place to have a nice burger, a...
#1 of the Top 5 Functions: Excel IF Function
The Excel IF function is like a nice pair of black shoes: they're fundamental and fly under the radar. When asked about a list of Top Excel Formulas, I immediately thought of the almighty VLOOKUP. But Wait! My #1 is IF and #2 is VLOOKUP. IF is fundamental like black...
Control data in a cell: Wrap Text & Alt+Enter
QUESTION Too much text and not enough space Nicole wonders how to control data in a single cell. Specifically, how can you get all the text to show without getting clipped off or making the font really small AND without making the column wider? In this image we see...
Top 5 Excel Functions
Here's the answer to a question about THE Excel Functions To Know. The request was for the top 10 but 10 is too many. After 3 or 4 functions, they get difficult to rank because they fall into areas of infrequent use or exclusive use. Things like INDIRECT, INDEX, RAND,...
Excel Percents, IFERROR, Helper Columns & Tidiness
QUESTION Nicole wanted to clean up a spreadsheet. She already had an answer to the math: Problem: She was doing it in more than one step and was sure things could be more tidy. Yes. This has a fairly easy solution but small things like this can irritate an Excel user...
Excel, Music and Songwriting
QUESTION Brother Henning asked about the relationship between bass playing and Excel skill. Part of the answer is in a previous blogpost where I surmise that there's a relationship between Data Managers and people who play bass and drums. There's a mindset committed...
Corrupt Data: the time I screwed up
We can talk all the live-long day about keeping clean data but there will still be corrupt data and human blunders. What happens when it's our own damned fault? This topic is à propos after a recent chat with a Database Administrator about the realities of mistakes in...
Data Parsing in Excel: Converting a report to useful data
QUESTION Here's an example an example of Data Parsing in Excel. Phil sent in this challenge. He receives a report and it's not useful the way it is. Often, these reports are generated for an executive to read. That same report gets turned over to an Analyst to use to...
Excel Reporting Made Easy
Guest blog by Mynda Treacy of My Online Training Hub. If you spend hours updating Excel reports each week or month then I’m sorry, but you’re doing it wrong. It shouldn’t take that long. All you need are a few key rules for setting up your workbooks and in most...