I’ve been away for a while doing project-related things. Now I’m back just in time for summer.
During the summer, blog readership will be down. So I’m saving my good Excel and Access tricks for later.
Right now, I’m starting a series called ‘Oldies but Goodies’. In this series, I’ll share some of the older Excel and Access tricks that have been around for years.
.
.
Today’s tip….Filling in a Series with the Fill Handle! Ha-cha-cha-cha!
» More: Fill in a Series with the Fill Handle
I just finished four days of DataPig workshops with Jon Peltier and Dick Kusleika. As always, everyone who attended had a blast and learned a ton of new tips and tricks. If you’ve never been to one, you’re seriously missing out.
.
Here’s an example of why I love these events.
» More: Forcing Carriage Return in Custom Date Format
For those you who have Excel 2010, you get the benefit of a new feature called Picture effects. With picture effects, you can take an ordinary picture and wrap them in fancy borders, colors, and other such visualizations.
.
So I thought, why not use these effects in my dashboards. What you see here are charts that I’ve captured with the Camera Tool then applied Picture Effects.
» More: Enhance Excel 2010 Dashboards with Camera Tool and Picture Effects
The boy is now doing a lot of math that involves multiplication, so he’s been reviewing the dreaded multiplication table.
It occurred to me that you could build a quick multiplication table in Excel.
» More: Building a Multiplication Heat Map
On Monday I posted a brilliant tip, showing how to replace the Microsoft Access splash screen. Steve T posted a comment lamenting that the trick did not work with Excel. While it’s true you can’t replace the Excel splash screen as easily as you can in Access, you can use a command line switch to suppress it.
Many of us have created Microsoft Access applications which are used every day in professional environments. And we all strive to have those applications to look as professional as we can get them. Given all the work we do to give our Access applications a professional look and feel, it is a bit annoying to see the Microsoft Access splash screen pop up each time we open the application.
I just got back from a whirlwind week of live training events. The most exciting one was the live Xcelsius training we did in Miami. We had a blast, as we always do at our live events. I was on my game – of course – and everyone walked away with coffers full of new techniques.
.
During my live training events, I have gotten into the habit of using a trick I learned from John (a Senior VP at Ernst &Young) a few months ago.
With this trick, I can increase the font of just the Formula bar. Like this:
Hi there! Remember me? I run a blog on Excel and Access. I’ve been away for a while doing some exciting stuff for lots of people. I can’t talk about any of it, but let’s just say it involves a plastic funnel and hotel soap.
.
Anyway, for my first post back, I thought it would be fun to do a light Pivot Table trick.
Did you know you can show more than one subtotal for a particular field in a pivot table?
In this example, I’ve got some information on the sales of bar equipment. Although we do see the sum of all sales at the bottom, let’s say we’d like to see a few more descriptive statistics (like avg, min, and max).
» More: Showing Multiple Pivot Table Subtotals for One Field
The other day, a co-worker of mine lost the ability to send an email from Excel. You know…Office button -> Send -> E-mail.
He used to be able to do this, but for some reason, things went all wonky and now he can’t. I guess sometimes Microsoft Office gets a bug up its butt and starts acting possessed.
.
The good news is that Microsoft comes with its very own Father Merrin who can exorcize Office, returning it to the happy little girl it usually is. Yes…Microsoft Office is a girl.
.
Microsoft Office comes with a built in tool called Diagnostics (or Detect and Repair). This tool is designed to (according to Microsoft): “solve some problems directly and might identify ways that you can solve other problems.”
» More: Exorcizing Microsoft Office
A while back, Jeff Weir emailed me and gave me this technique that will determine if Excel is working properly.
.
Enter ‘Male’ into cell A1. Enter ‘Female’ in cell A2. Finally enter the formula A1>A2.
You should naturally get the answer TRUE. If you don’t, there is something wrong with your version of Excel.
» More: How to Check if Excel is Working Properly
