May 08, 2004

Making it all Add Up, Part 1
Excel comes with many functions to help you add up and count without ever reaching for a calculator. Let's have a look at some of the main ones.

This is a simple but very useful function. Use it to add up numbers quickly and easily. Here's a couple of ways you can use it.

A range of cells in a column (rows are fine too).

Multiple ranges (they can also be separate, just drag any cells you want while pushing the Ctrl Key)

This works in a similar way to SUM, but as the name suggests, it counts rather than adds.

This counts text entries only. This includes numbers and error messages such as #VALUE!. It does not count blank cells. (If you look closely, you will notice that I removed a few cells from the selection)

This is the opposite of COUNTA. It only counts blank cells.

These 3 functions also do as the name suggests. They give you the average, maximum and minimum of a range of cells. Use them in the same way as the SUM and COUNT functions.

Status Bar
This is not a function but is very useful when you just want a quick reference. (If you can't see it at the bottom left of the worksheet, go to the Tools Menu at the top, then select Options, View and click the Status Bar checkbox)

Select the function you want to use, then drag your mouse over a range of cell or cells. For multiple ranges , drag while pushing the Ctrl key.

This is yet another useful function. You can see I have entered a '9' before the range of cells A2:A11. This '9' is recognized as the SUM function as per the list below.

10 VAR

You will notice there are a few new formulas that I have not covered yet. PRODUCT is used for simple multiplication. STDEV, STDEVP, VAR, VARP are statistical functions and refer to standard deviation and variance. They are good for things such as quality control or demographics, but as they are slightly more difficult, I think I might cover them some other time :)

May 03, 2004

Customizing Toolbars, Part 2

In addition to customizing buttons, you can also customize the toolbars themselves.

Turning them on or off is quite easy. Right click any toolbar and select or deselect the toolbars you want to show or hide.

You can also make your own toolbars. Select Tools, Customize and then click on the Toolbars tab.

Push New (right hand corner). A small window should appear for you to input the new toolbar's name. I've decided to call my toolbar 'Quick Lines'.

When finished, switch back to the Commands tab and select the buttons you like. As the name implies, I have dragged across some line buttons to my toolbar. Place the toolbar anywhere you want, top, bottom, left or right.

How about menus? While the Customize dialog box is open, you can drag menus or submenus to wherever you like. If you look closely, you will see that some menus and commands (buttons) have a small line under one of their letters. This is to help navigate quickly by just using your keyboard.

For example, if you push Alt, 'I', 'N' and 'D' you will notice that the Define Name box appears. The shortcut keys used were Insert, Name and Define which become accessible when you push the Alt key. If you remember the shortcut keys that you use regularly, you will find that your work is completed a lot faster.

And in the absence of pre-defined shortcut keys? You can make your own.

With the Customize dialog box open, right click any button you want to change. In the menu that appears, you should see 'Name:' with the name of the menu or button. Add an ampersand (&) just in front of where you want to place the shortcut. For example, S&ave will give you Save.

That's it for the time being. Believe it or not, I'm on vacation and might take a few days off. In fact, tomorrow I will be meeting some fellow Excel people and hope to learn some new tricks. Of course, I will be sure to pass them onto you too...eventually :)

May 02, 2004

Customizing Toolbars, Part 1

Today I would like to look at adding and customizing buttons on toolbars.

You’ll often find yourself saving workbooks, so why not make a custom button to not only save workbooks, but save time as well?

Here goes. First click the Tools Menu and then Customize. The Customize dialog box will appear on the screen. (Make sure you have the Commands tab selected)

Then I choose File in the Categories section (left side) and scroll down to Save As in the Commands section (right side).

Drag across to your place of preference. You can see that I’ve placed the Save As button between the Open button and the regular Save button.

You might be comfortable with the button without changing anything, but further customization is possible. Let’s say I prefer an image rather than just text.

First, I right click the button itself, then I select Text Only (In Menus). The text disappears but unfortunately this particular button has no image. I right click again and then Change Button Image and choose an image I like (I like to use the pencil image for Save As)

All I have to do now is push Close on the Customize dialog box that first appeared and I’m finished.

Note: If you do not like any of the images presented in the Change Button Image selection, you can make your own images by right clicking the image and selecting Edit Button Image or you can also use the Copy Button Image and Paste Button Image to copy and paste any other button image that you might like instead. Just to make sure you’ve already opened the Customize dialog box first.

Last but not least: Here is a great download called Button Faces from John Walkenbach's The Spreadsheet Page. You'll find nearly 200 custom toolbar button faces that you can use to copy and paste as I showed you above. Don't forget to check out the other great downloads at this link.

This page is powered by Blogger. Isn't yours?