Posts Tagged ‘Excel’

« Older Entries
Newer Entries »

Easily Navigate Many Worksheets

November 14th, 2011

Last week, Debra over at Contextures posted an article that showed us lots of nifty ways to navigate a workbook that has many worksheets. Today, I’d like to add my two cents by sharing a small macro that will give you a fairly easy way to jump from sheet to sheet.

.

We all have them – those workbooks that have 10, 15, 20 or more tabs.

I personally have lots of training workbooks that have 15+ tabs.

When you’ve got a workbook that has this many sheets, it can be a pain to find the sheet you need.
» More: Easily Navigate Many Worksheets

7 Comments »

Posted in Excel Tips and Tricks, Excel VBA

Tags:

Use Fill Handle to Fill in the 1st and 15th of Each Month

November 7th, 2011

Here’s an easy tip from Excel MVP Bob Umlas – author of the new book, Excel Outside the Box.

.

Let’s say you want to fill in some rows with the 1st and the 15th of every month.
» More: Use Fill Handle to Fill in the 1st and 15th of Each Month

Leave your comment »

Posted in Excel Tips and Tricks

Tags:

Merging Two Staggered Columns

October 31st, 2011

Last week, I was working with some data when I encountered the need to merge two staggered columns.

That is to say, I needed to merge two columns where the data was blank in either one column or the other.

.

Here is an example.
» More: Merging Two Staggered Columns

8 Comments »

Posted in Excel Tips and Tricks

Tags:

Show AutoFilter Criteria in Status Bar

October 17th, 2011

One of my 12 fans (Lester) emailed to remind me that I have a blog. I almost forgot. So with a good kick in the pants, I’m back to blogging.

.
While I was away, Microsoft awarded me with the coveted Microsoft Excel MVP award for the fourth time. So I guess I better start earning that distinction here. Here we go.

.

Today, I’ll show you a nifty trick that allows you to actually see the AutoFilter criteria you have applied on an Excel table.

.

This is actually a pretty common request. When you’ve got an AutoFilter applied, it would often be nice to see how the table is actually AutoFiltered. Alas, Excel’s only accommodation is to give you a generic count of records on the status bar.

.

.

I’ve come up with a piece of code that will give you more details in the status bar. Like this:

.

.

Here is how you can implement this:

» More: Show AutoFilter Criteria in Status Bar

14 Comments »

Posted in Excel VBA

Tags:

Hack into a protected Excel 2007 or 2010 Workbook

September 26th, 2011

I’m back from the Power Analyst Bootcamp in Washington DC – and of course, it was a success. 37 of the finest analysts attended the 2-day event, where we shared our passion for Excel, deli meats, and spicy tricks and tips. In the end, everyone left with a bag full of new techniques that will make them better analysts.

.

While I was gone, there seems to have been a flurry of activity and questions in reference to hacking into a protected workbook. A couple of years ago, I posted a slick technique you can use to hack into a protected 2007 worksheet. Apparently, hacking in a protected workbook is also a highly desired talent. So today, I’ll walk through the steps to hack into a protected workbook.

.

Excel 2007 and 2010 files are essentially zipped packages that contain XML files. This means that if you take an xlsx file and change the extension to zip, you’ll be able to see all the xml documents that make up your Excel file. Not only that – you can change the content and properties of an Excel 2007 file simply by manipulating the XML documents that make it up.
» More: Hack into a protected Excel 2007 or 2010 Workbook

13 Comments »

Posted in Excel Tips and Tricks

Tags:

Use Pivot Slicers as Form Controls

August 30th, 2011

About a week ago, Bill Jelen sent me an email with a challenge to figure out the nifty trick he shows in this YouTube Video. Inspired by the video, I decided to use the trick in a simple dashboard model.

.

What I ended up with is this cool technique that allows me to replace my old Form Controls with Pivot Slicers. As I select each slicer, my dashboard responds as it would would if I were using a set of Form Controls. Keep in mind that the charts and other components in my dashboard are in no way connected to a pivot table.
» More: Use Pivot Slicers as Form Controls

3 Comments »

Posted in Excel Charts, Pivot Tables, Visualizations

Tags:

How to Compress xlsx Files to the Smallest Possible Size

August 18th, 2011
A colleague of mine (Tim) is both the smartest and cheapest guy I’ve met in a long time. Tim has a knack for analyzing the cost savings of things. So much so that it borders on amusing. For example, he buys his coffee creamer in bulk on-line because it’s “the best deal”. Bulk coffee creamer! Man…that’s commitment.
.
.
His knack for saving has apparently spilled over into disk space.
Tim showed me a trick he uses to compress Excel xlsx files to make them as small as possible. Today, I’ll share his trick with you (my 12 fans).
.
.
I’ve got this xlsx file that’s 20 MB big. I need to shrink the file down to a more acceptable size.
Normally, I would convert this file to an xls file which would make it much smaller. But this particular file has too many rows to convert to an xls.

» More: How to Compress xlsx Files to the Smallest Possible Size

14 Comments »

Posted in Excel Tips and Tricks

Tags:

Automatically Label First and Last Chart Points

August 16th, 2011

One of the best practices of dashboard building is to avoid overwhelming your customers with too much data at one time – especially in a chart, where they can lose sight of the primary message if focusing on inconsequential data. One of the common ways dashboard designers help focus the message of a chart is to limit the data labels to only the key points.

.

Once I have only two data labels, my eyes immediately take in the message of this chart (that we went from 11 to 53).
» More: Automatically Label First and Last Chart Points

6 Comments »

Posted in Excel Charts, Excel VBA, Visualizations

Tags:

Automatically Set Chart Series Colors to Match Source Cell Colors

August 10th, 2011

While working on a dashboarding project here, I accidently developed a charting macro that will automatically set the series colors in a chart to match their source cell’s colors.

.

I’m not sure how useful this actually could be, but it’s charming enough to share with my 12 fans.

.

Let’s say I created this chart – and I want to apply specific coloring to each series without having to painstakingly change all the color options in each series.

» More: Automatically Set Chart Series Colors to Match Source Cell Colors

21 Comments »

Posted in Excel Charts, Excel VBA, Visualizations

Tags:

Quickly Change Chart Series Positions

August 8th, 2011

Well, the summer is over for many of us. I’m back to my regular blogging schedule (whatever that means).

.

For my first post back, I’d like to show you a trick I learned from charting guru, Jon Peltier.

.

Ever needed to switch the position of a chart series (color and all)?

In this example, I want to show the series in red first. I’ve already created my chart, so I just want an easy way to switch the positions of the columns.
» More: Quickly Change Chart Series Positions

4 Comments »

Posted in Excel Charts, Visualizations

Tags: