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.
Posts Tagged ‘Tips and Tricks’
Replacing the Microsoft Access Splash Screen
May 2nd, 2011Changing Font Size of Just the Formula Bar
April 25th, 2011I 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:
Showing Multiple Pivot Table Subtotals for One Field
April 11th, 2011Hi 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
Exorcizing Microsoft Office
March 16th, 2011The 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
How to Check if Excel is Working Properly
March 14th, 2011A 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
The Zoom on Roll Option
February 21st, 2011Back when this blog was just a few weeks old (fresh and pure like a little baby Jesus), I posted a trick enabling you to zoom into a spreadsheet with a double-click. That trick was supposed to be a
“down and dirty technique you can use when you find you’re constantly zooming in and out, alternating between scanning large sections of data and reading specific cells.”
.
Well John T. wrote me an email, informing me of an Excel option I didn’t know about. In the Advanced section of the Excel Options dialog box, you’ll see an option called Zoom on Roll with IntelliMouse.
Changing Sort Order of Access Crosstab Headings
February 14th, 2011By default, Access Crosstab queries sort their column headings in alphabetical order. This is becomes a real drag when you’re struggling to show months in month order instead of alphabetical order.
.
This screenshot shows the mess that comes from running a Crosstab with month columns. According to Access, April is the first month of the year.
» More: Changing Sort Order of Access Crosstab Headings
Safely Move Formulas without Absolute Reference
January 10th, 2011I can’t believe how busy the first ten days of this year have been. I’ve been tucked away in my office trying to finish off a dashboarding project. I’ve finally been able to come up for air and to update the old blog. So here it goes.
.
We all know you can make the formulas in your worksheet “Absolute Reference”. This locks the formulas so you can move them around without changing the references. But you don’t always have the luxury of applying absolute references to all the formulas you need to move – maybe your formulas have mixed references or maybe absolute references don’t fit your model. In these cases, you can use a simple trick that allows you to safely move your formulas around without having to apply absolute references.
.
In this example, I need to sort the formulas in column J so that the time periods are sorted by the sort key. Note that these formulas are relative reference formulas.
If I apply the sort as is, the formula references will get all wacky, causing the formulas to become incorrect.
.
To safely move the formulas, I perform a Find and Replace – replacing all equal (=) signs with the pound or hash (#).
.
This essentially converts the formulas into hard-coded text strings which can be moved around.
.
At this point, I can safely apply my sort.
.
Once I’m done moving things around, I can perform another Find and Replace to find all pound or hash (#) signs and replace them with equal (=) signs.
Create a Keyboard Hot Key to Start Excel or Access
December 28th, 2010Are you constantly opening Excel and Access?
Why use your mouse to click on the Excel or Access icon?
Why not create a keyboard hot key?
. » More: Create a Keyboard Hot Key to Start Excel or Access
Emailing from Excel Using the Hyperlink Function
December 9th, 2010Did you know that you can use the HYPERLINK function to create emails from Excel?
It’s true. Today, I’ll show you that the HYPERLINK function is not just for getting to your E-Harmony account.
.
Excel’s HYPERLINK function has only two arguments: the URL you are trying to hit, and the friendly name. So to get to the E-Harmony site, you could use something like:
=HYPERLINK(“http://www.eharmony.com”, “Woman Shopping”)
» More: Emailing from Excel Using the Hyperlink Function
