Archive for the ‘MS Excel’ Category

Printing Formula Errors

Did you know that you have options as to how Excel prints formula errors for you?

I mean, if you’ve got a worksheet you’re still editing, it’s possible that you’re still working out all the “bugs.” So, maybe you’ve got a few formulas displaying the “Oh-So-Wonderful” error messages.

You know you’ve got to fix them, but maybe you don’t want the error messages cluttering up the printout… Continue reading

Print the Headings Too

Do you print Excel worksheets for proofing reasons?

Do you find yourself frustrated that the row numbers and column letter headings aren’t on the paper? Would it make your job easier if they were?

If you’re looking for this type of assistance from Excel, there’s good news for you today! With one simple checkbox, you can get the printout exactly as you need it.

We’re looking… Continue reading

MS Excel Charts Done Your Way

Okay, so you’ve made an absolutely beautiful Excel chart, but after some consideration, you’re seeing a few things that you’d like to change.

Maybe you’d prefer a different font for the text.

Maybe you need a smaller font on some of the labeling so Excel will display all of the category names.

Maybe you have money amounts graphed and all of the “.00″ on… Continue reading

Fix It With A Click, Click

Use MS Excel charts frequently? Ever find yourself in a situation where you’ve gone completely through the charting process, had a beautiful graph and realized that you spelled a word wrong?

What now?

Do you have to start all over?

Oh, I hope not! There must be a quick and easy solution somewhere.

Breathe easy, there is!

Mistakes in an Excel chart are often… Continue reading

Lost in Worksheets

Have you ever found yourself feeling lost while trying to move a worksheet inside a huge workbook?

I mean, you’ve got 20 or 30 worksheets and you’re trying to click on the sheet tab and drag it to its new home. So, now you’re just patiently waiting for the tabs to scroll by while you continue to hold that mouse button.

Oops, you just flew by the… Continue reading

Moving Right Next Door

As I’m sure we all know, you can rearrange worksheets in an MS Excel file with a simple click-hold-and-drag of the sheet tab.

But, did you know that you can also move worksheets from one workbook to another using the same method?

No?

Well, good news, you can, and it’s really as easy as it sounds.

First, open both workbooks. (The one with the worksheet… Continue reading

Excel Dates Done Your Way

Okay, so you’re frustrated with the standard Excel date formats and you’re looking for a way to meet your needs.

Well, don’t be frustrated any longer!

Today, I’ve got just the information you need to make Excel do things your way.

First, you need to select the cells that should be in the date format.

Now, go to the Format menu, Cells choice. (Both Ctrl

Dating in Excel

I’m sure by now you’ve realized that when you type a date into MS Excel, the program seems to magically know what it is and treats it as such.

Cool, less work for us, right?

But, have you ever wished the date was in a different format?

I mean, maybe you want just the month and day (5/26) for example. Or, maybe you’re looking to have something… Continue reading

Clearly, We’ve Got Some Options

So, you’re working in MS Excel with some data and you’ve got some changes to make.

If you needed to delete some data, you would simply highlight the cells and hit the Delete key.

But, let’s suppose you don’t want the data deleted, but instead you want the formatting removed. What do you do then?

You could spend time reformatting the cells or undoing whatever… Continue reading

MS Excel Custom Views

Do you work in huge MS Excel files and constantly find yourself scrolling this way and that, trying to get to the right place?

And then again to another location?

And so on.

If you have enough of these locations you need to go to, it quickly becomes impossible to remember cell locations. That means the “Go To” feature is out!

In addition to all that… Continue reading

Super Repeating Data Shortcut

When working in MS Excel, do you ever find yourself repeatedly entering the same formula or data?

What do you do?

Do you type it again and again and again into each cell?

Maybe you save some time by entering it once then copying and pasting it everywhere else?

That’s not a bad way to do it, but what if you had a faster way?

Let’s… Continue reading

The Leading Zero

Are you constantly frustrated with MS Excel, because it keeps removing that leading zero from your inventory number? (Or any leading zero on any piece of data?)

Yeah, me too.

The problem is that Excel automatically sees the data as a number and leading zeros have no value in numbers, so it gets rid of them.

It’s convenient when you’re actually dealing with numbers, but a pain… Continue reading

And the Subtotal Is…

Have you ever found yourself working on an Excel worksheet desperately trying to group similar data, insert a row or write a formula for the group?

How was it?

What was that you said?

Did I hear you mutter under your breath something about it being a complete pain?

Yep, that’s what I figured I’d hear. (I thought the same thing too).

So, the question becomes… Continue reading

Excel Sorting Fun

Often in MS Excel, we have a database of information that needs to be ordered in a certain way. (Whether it is alphabetical, by date or whatever; we just need to get it in order).

Since today’s tip requires you to know how to sort, I thought it might be a good idea to cover the basics first.

The first step to sorting data is to tell… Continue reading

Mouse Free Borders

Do you use the Format Cells window to create custom cell borders in MS Excel?

Do you prefer the keyboard to the mouse?

Maybe, in an effort to combine these two things, you’d be interested in a list of keyboard shortcuts to apply and remove individual cell borders?

If it sounds like a good idea to you, then read on.

Obviously, our tip starts after you’ve highlighted

Randomness in MS Excel

Ever need random numbers in Excel for data analysis?

Or, maybe you just need random numbers to fill cells while you work out formatting issues. You certainly don’t want to sit and make up data for this purpose. That takes way too much time.

You just know there is and I can definitely say that you’re correct. There is a much better way to fill those… Continue reading

MS Excel AutoFilter Part 2 – Top 10 and Custom Filters

As promised on Tuesday, we will finish our discussion of Excel’s AutoFilter function. Specifically, we need to cover two items in the AutoFilter pull down lists: “Top 10″ and “Custom.”

So, let’s get right to it.

To begin, I think we should discuss Top 10.

This is pretty much what the name implies. It’s a listing of the top… Continue reading

I Thought Filters Were For Things Like Fish Tanks and Coffee Makers…

Have you ever found yourself lost while working with a huge list or set of data in MS Excel?

Did you find that you were continually scrolling through data looking for a particular value?

I bet more than once you caught yourself wishing for a way to just see what you want and get rid of the rest.… Continue reading

Changing the View, Not the Selection

I’m sure just about everyone has used the arrow keys in MS Excel to move through their worksheet.

But, sometimes you want to quickly zip out to another part of the worksheet to see something without moving the cell selection and losing your current place. What do you do then?

I’m betting that many of you take your hands off the keyboard, use the… Continue reading

Format Painter Made More Useful

Do you use the Format Painter in MS Excel to transfer cell formatting from one cell to another?

It’s a good use of the Format Painter function. It’s certainly much easier than starting from scratch with all of the formatting each time. I mean, if you already have cells established with the necessary formatting, why do the work twice?

So, I propose to you one… Continue reading

Quick and Spiffy Tables in MS Excel

Do you usually use MS Excel for table work? Looking for something to help give you a quick and easy visual “edge” when it comes to the presentation of your tables?

If any of that sounds like a good idea, you might be happy to know that Excel has an AutoFormat function just for such a purpose.

In other words, you can quickly… Continue reading

MS Excel Default Font Fun

Do you constantly find yourself changing the font size or type in Excel worksheets?

Is it getting annoying to repeat yourself time after time after time?

I bet a few of you even tried looking in the Format menu for something equivalent to the Font choice found in MS Word.

Didn’t find it, did you?

Nope, that’s because it’s not there.

You can find font… Continue reading

Worksheet Or Workbook?

Did you know that when you do a Find / Replace in MS Excel, you can control how much of the file is searched?

Yep, that’s right. You can choose to work on a single worksheet or on the entire workbook. It’s all up to you. You just have to know where to look for the setting.

To begin, you need to run the Find / Replace… Continue reading

Single Worksheet Templates

I’m sure you’ve used templates in MS Excel that were entire workbooks, but have you ever wanted just a single sheet for a template?

You know, instead of opening a workbook template, just insert a sheet into another workbook?

Sounds like a good idea to me! After all, I’m willing to bet that someone out there uses a template to copy and paste a single sheet into… Continue reading

File Open With Startup

Do you have a workbook that must be opened first thing everyday when you start up MS Excel?

Wouldn’t it be nice if Excel would simply open that file for you when you first start the program?

If you’re thinking that this would save you time each day, then maybe this is the tip you’ve been waiting for.

There’s no special setting in Excel and nothing… Continue reading

Printing the Grid

While going along the same lines as yesterday’s MS Office tip pertaining to displaying gridlines, I think it might be a good idea to discuss printing gridlines.

I mean, it seems inevitable. Whatever your system is trying to print (gridlines or no gridlines), you’ll want to do the exact opposite.

The question is where are the controls to these things? You just have to know that cell… Continue reading