May 30, 2004

Concatenate Concentrate

The CONCATENATE function is used to join text or numbers (known as Strings. It can join up to 30 strings although I suppose you could nest two or more CONCATENATE functions inside one another. I have more important things to do such as writing the remainder of this post.

I'll give you 2 examples.

Example 1 (Joining 2 names together)
In range B3:B12 I have a list of first names and in range C3:C12 I have a list of last names. Concatenating makes joining these names together a snap. In cell D3 I enter =CONCATENATE(B3," ",C3) and drag down to cell D12 where the formula will become =CONCATENATE(B12," ",C12)

Note the apostrophe marks, they are used to include spaces or text. Cell references (such as B3) or numbers do not use them. (I could also ampersands to concatenate as in =B3&" "&C3 but I've found after getting used to spelling CONCATENATE, it's easier for me to enter several strings into a formula quickly)



Example 2 (Making an email address)
To do this I am going to use a combination of 3 formulas, CONCATENATE, LOWER and LEFT. Let's start with LEFT.

LEFT
It seems to me that many email addresses use the first letter of the first name and then all of the last name combined before the @ mark. To get the first letter of Tom in cell B3 I use =LEFT(B3,1). For more letters I can just increase the number after B3, these letters will always be read from the left side, hence the name LEFT

LOWER
Another simple function, it is used to convert all text to Lower Case so that TSmith will appear as tsmith. (The UPPER function is used for Upper Case)

I should say that I don't really need to use this function as either upper and lower case is still okay for email addresses. I just want to make it easier to recognize an email address as opposed to normal text.

CONCATENATE
Refer to the above. Now lets look at the entire formula,

=LOWER(CONCATENATE(LEFT(B4,1),C4,"@andrewsexceltips.com"))

This will give me tsmith@andrewsexceltips.com (you'll need to use Hyperlinks to make them 'clickable' email addresses, use Ctrl and K to bring forth the dialog box)

Referencing
Now that we have a list of email addresses, let's make a directory using a Combo Box. Right click any toolbar and then tick the Forms toolbar.



Drag the Combo Box to your preferred location and then right click it to select the Format Control dialog box.



Your Input Range will be D3:D12. Push the little arrow button on the right side and drag across these cells, the $ marks will appear automatically to make this an 'absolute' reference (look at the Help files to understand if you are not sure what this means)

Next up is your Cell Link. I usually hide these under my Combo Boxes so they don't stand out. You can see that I am using cell D14.

Finally we have Drop Down Lines, I've increased to 10 from a default number of 8 so that my entire list of email addresses can be seen.

When you use the Combo Box, the Cell Link will change. If you select the fourth name from the top (Jane Williams), the value in cell D14 will become 4 (1 for first, 2 for second, 3 for third, etc)

All I need is some way to reference the actual addresses. I've chosen the INDEX function to do this.

=INDEX(F3:F12,D14)

The range F3:F12 contains the email addresses, D14 is the Cell Link that will give me the vertical (row) reference as above. (A horizontal reference is not necessary as we are just dealing with a single column)



Simple once you know how.  

Posted by andrewe at 16:45

May 27, 2004

Feedback

None other than Debra Dalgleish, MVP of Contextures was kind enough to point out some confusion regarding a previous post "Now You See it, Now You Don't" on May 13.

The point of the post was to illustrate how an area of a Worksheet can be used to have formulas in the cells themselves, with a clear AutoShape above to make a message appear using Conditional Formatting.

The post was confusing because I used a date related formula in a cell beneath and another date related formula in the Conditional Formatting. Let me say that there is no connection between the two formulas. What is more important is that the clear AutoShape to display the message at the right time, and the cell formula to be hidden using regular formatting (white Font Color)and Conditional Formatting (changing to black Font Color). The text on the clear AutoShape itself is white.

When the Conditional Formatting is activated, the cell font color (and cell background) will turn black to make the white text in the clear AutoShape appear like this.



Still confused? Send me an email at the link at the top right (under my handsome photo) and I'll do my best to explain.

Please feel free to contact me whenever you like. I'll try to help with any Excel related inquires (or direct you to people who can) and like I mentioned, feedback is always welcome!

Thanks again Debra!  
Posted by andrewe at 16:51

May 26, 2004

Keeping It Safe, Part 3

Here's yet another way to achieve a certain level of protection for your Workbooks. It comes in handy if you just want to help prevent accidental deletion of formulas or data without actually using the Protect Sheet (Protect Workbook) feature that I mentioned in my last post.

First, turn off Gridlines by selecting Tools, Options and then the View tab to untick the Gridlines checkbox.



Select a cell or range of cells, then use Copy (right click menu), and while pushing the Shift key, select the Edit menu and then Paste Picture.


You should end up with an 'invisible' shape like this.



Move it to your preferred location and drag the handles to change the size (Pushing the Alt key at the same time will help you align it with neighboring cells)

You're finished. You can also use the Camera button (You can find it by selecting Tools, Customize and then scrolling down the Tools menu of the Commands tab. Drag it across to any toolbar). Don't forget to change the Line Color to No Line.

One advantage of using the Camera is you can add text to the original cells from where you copied and it will appear on the 'invisible' shape. In fact any change you make will also change the 'invisible' shape automatically. One advantage of the Paste Picture method is that you can change Transparency by selecting the Fill Color you want, then right click to bring up the Format Picture dialog box, the select the Colors and Lines tab on the left side. Move the Transparency slider to the percentage you want (Transparency is only available for Excel XP and Excel 2003)



When using Protect Sheet (Protect Workbook), you find yourself unable to lock certain cells such as link cells for Combo Boxes etc. (Combo Boxes will not work if you lock the link cells) Using 'invisible' shapes you can shield these unlocked cells so that they cannot be selected with the mouse (you can still delete them by using the arrow keys so please be careful).

You might also think about making several invisible shapes and then Grouping them together(right click menu) to make an 'invisible template'. You'll find that you can still use AutoFilters if you postion your 'template' around them. Just move it out of the way when you want to edit the cells underneath and then repostion it while pushing the ALT key.  
Posted by andrewe at 17:00

May 25, 2004

Keeping It Safe, Part 2

Excel makes it possible to protect your files in a variety of ways. Apart from the methods I showed in my last tip, one of the best ways is Worksheet Protection (Workbook Protection is something else you can consider depending on your particular requirements). Select Tools, Protection and then Protect Sheet.



Depending on your version of Excel, there is quite a difference in the options that are available but the basics have not changed. First of all, allowing users (including yourself) to select locked or unlocked cells is something you must decide. Unlocked cells can be edited even when the Worksheet is protected, locked cells cannot. To unlock or lock cells, drag across your selection and right click to select the Format Cells dialog box. The far right Protection tab is what you want, tick or untick the Locked checkbox option accordingly.



Obviously, the safest protection option is not to allow any user to select anything and this can be a good thing depending on your situation. (Don't forget you can password protect sheets and workbooks too). However, sometimes you may want to keep some cells unlocked so that users can input or modify data so these cells should be unlocked before you protect the sheet.

Unfortunately Protection can also cause problems. In some versions of Excel, Protection does not allow you to use AutoFilters (you can enable filters with Excel XP or Excel 2003 scrolling down to the Use Autofilter option, but this feature won't work for anyone else on a network with earlier versions)

Debra Dalgleish, Excel MVP, has a good solution to this problem with earlier versions on her site Contextures (some very good tips to be found there) Here's a link to her tip on AutoFilter - Protected Sheet. Copy the VBA code and place it in your Workbook as per her instructions (Don't forget to change your Worksheet name accordingly)

Okay, that's it for now. Next time I'll have a very simple protection tip that may come in handy for "partial protection".  
Posted by andrewe at 16:55

May 23, 2004

Keeping It Safe, Part 1

You put a lot of work into your Workbooks. Are they as safe as they should be?

Save As...
When you save your work, there are some very good safety options available to you. Select Tools on the Save As dialog box then General Options.



Always create backup?
The choice is yours. Creating backup files will use up more disk space, but hopefully that should not be a problem. I always make a point to select this option, it's better to be safe than sorry. You might also consider saving under different filenames if you think that more than one backup file may be prudent or even create backups of your backup files.

Passwords
Two options, a Password to open the file and a Password to modify it. I wonder just how many people use the first option (and what kind of environment they must work in) but the second option is quite handy, it also offers an read only option for anyone who does not intend modifying the file. Just keep in mind that anyone can change something and then save under a different name or location. The file can also be deleted very easily. Use these options with caution.

Read-only recommended
A good safety option. This is what appears when you open the Workbook.



You can also add the Toggle Read Only button to a toolbar so this option can be selected after the workbook has already been opened. Open the Customize dialog box from the top Tools Menu, then the Commands tab. Scroll down the File menu until you see the button and drag it across to a toolbar.



It also indicates whether the file is in read only mode or not. Very useful if you are unsure of the file's status.  
Posted by andrewe at 17:03

May 21, 2004

Take What You Need

Copying and Pasting. These two features sure save a lot of time and can be found in all kinds of software (Shortcut keys are invariably Ctrl + C to copy and Ctrl + V to paste, use them for text, images and files). But there are special variants of Pasting that can make working with Excel even faster and easier.

The Paste Special Dialog box
The easiest way to open this dialog box is to right click where you want to paste (you can't use it unless there is something already copied to the clipboard). Alternatively you could try Alt, E and S if you prefer shortcut keys.



Apart from the All option which pastes everything, the other useful options will probably be Formulas, Values, Formats and Comments. Use them accordingly, they do what they say. (Note that for formulas used in Conditional Formatting, the Formats option should be used instead of the Formulas option)

Paste Special Buttons
Another way to paste just what you want to is to use the Paste Formats and Paste Values buttons. Open the Customize dialog box in the Tools Menu and select Edit from the Commands tab. Scroll down till you find the buttons and drag them across to a toolbar of your choice.



Hmm, but what about Paste Formulas and Past Comments? For some reason these buttons are not available, but no problem! You can download my macros called PasteFormulas and PasteComments and import them via your Visual Basic Editor. (I recommend putting them in your Personal Workbook)

Open up your Visual Basic Editor (Alt + F11) and go the File menu and then select Import File. (Make sure you click where you want to import them first. For example, pick the Modules folder in your Personal Workbook if you want to import there)

Now they are ready to use but you still need a way to activate them easily. Go back to the Customize dialog box in the Tools menu and scroll down on the Commands tab until you find Macros and then drag a Custom Button to a toolbar. (For editing tips see my Customizing Toolbars tip dated May 2) Right click the button and choose Assign Macro to open the selection of the Macros available in the dialog box.



There you have it. Note that the PasteFormulas and PasteComments work the same way as the regular Paste Formats and Paste Values buttons, you can use them to paste indefinitely while something is copied on the Clipboard.

Here's a picture of my own customized PasteFormulas Custom button.



Hope they come in handy :-)  
Posted by andrewe at 17:10

May 19, 2004

Getting Set Up, Part 2

To the Header/Footer tab. (if you don't know what I'm talking about, please read my previous post)

You can make your own custom headers and footers, or you can choose from one of the preset headers and footers available in the drop down lists.



If you choose to make a custom header or footer, there are many things options available to use with your text...change the style, add page numbers, add the file path, file name or tab name, add the date or time (very useful for tracking!), or even add a picture (I think this last feature is not available in some of the earlier versions of Excel).



The Page Break Preview
Last time I talked about sizing your work to suit by using the Scaling options on the Page tab of the Page Setup dialog box. This is another way of changing the size of your work, which is by adjusting the page breaks to wherever is convenient for you.

First, select Print Review. In can be found on the right side of each tab in the Page Setup dialog box, or you can also access it by pushing the button on the Standard toolbar that looks like a magnifying glass (usually found at the top). Once in Print Preview mode, push the Page Break Review button and you should get something like this, assuming you've already selected your print area.



Page breaks have a default setting of range A1:I55. By the dragging the blue lines one the edges, you can either shrink a page...



or change the boundaries shared with other pages. Bear in mind your work will also shrink or enlarge depending how your you are resizing your sheets.



Once finished, return to the Print Review and push Normal View. The job is done.  
Posted by andrewe at 12:04

May 18, 2004

Getting Set Up, Part 1

Nothing spoils the appearance of a spreadsheet more than a sloppy printing job. Here's how you should set yourself up... and not get arrested.

The Page Setup dialog box can be found in the File menu. If you use it often enough, you might well consider moving a button to a suitable toolbar by using the Customize dialog box in the Tools menu.

The Page Tab
Select your orientation, Portrait for printing vertically and Landscape for printing horizontally. Then think about the size of what you want to print. If it is not too large, you might get away with just using one sheet of paper. If this means that the text will be too small to read or what you print will only cover half the page, then it might be a good idea to use more than one page. In either case, use the Scaling features of the Page tab to select what best suits the job on hand.



The Margins Tab
Adjust the margins to suit and don't forget you can center your work by selecting the two checkboxes marked appropriately Horizontally and Vertically at the bottom of the dialog box.



The Sheet Tab
Yes, I know I skipped the Header/Footer tab, I'll cover that next time! This is more important, trust me! If you haven't already set up your print area, now is the time by the little red arrow in the Print area box. Watch it shrink, then drag over the area you actually want to print.



You'll also notice the selections 'Rows to repeat at top' and 'Columns to repeat at left'. These are very handy if you want to use the same titles for more than one sheet. For example, if you push the little red arrow on the 'Rows to repeat at top' and drag to select the row or rows your headers are in, they will automatically appear at the top of every page, 'just like what it says on the label'.  
Posted by andrewe at 02:14

May 15, 2004

Error! Part 2

Conditional Formatting. It can be used for many things. Let's have a look at how we can use it for error spotting.

In a certain range of cells, all numbers entered must be greater than or equal to 0 and smaller than or equal to 10. One possible formula for the Conditional Formatting in cell B1 for example is =OR(B1<0,B1>10)



Hmmm, let's make it a little more interesting and say that all entries must be multiples of 0.5. Here's my modified formula =OR(B1<0,B1>10,ROUND((B1/0.5),0)<>B1/0.5)



There you have it. Any cell that does not fit all of the above criteria will be highlighted like this.



Have a nice weekend.  
Posted by andrewe at 04:18

May 14, 2004

Error! Part 1

There are many ways to show a user that he or she has entered an incorrect value into a cell.

I like to show an example using Data Validation. Let's assume that a person's date of birth must be entered into cell A1 as an entry requirement for a club where membership is restricted to people 20 years old or more.

Here's one possible formula. (I added the ISNUMBER function just in case someone enters something besides a date. Don't forget that dates in Excel are just numbers formatted in a special way)

=AND(ISNUMBER(A1),(A1+1)>DATE(YEAR(TODAY())-20,MONTH(TODAY()),DAY(TODAY())))

Open the Validation dialog box from the Data menu at the top and select Custom for the Validation criteria. Then enter the above formula into the text box provided.



Next, select the Error Alert tab at the top and enter your custom Title and Error message.



Here what appears if you make an error.



Although my Error message doesn't really apply if someone has done something like entered text instead of a number, it should still get the point across.  
Posted by andrewe at 18:29

May 13, 2004

Now You See it, Now You Don't

Sometimes you'll find a certain cell or cells are on 'prime real-estate'. You might want to enter a formula in a particular cell, but at the same time you might want that same cell to display a message of some type under certain conditions.

Let's say I want a cell to display the message "Check Payments" 10 days or less before the end of every month. Here's a formula that will do that for me.

=IF(TODAY()>DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-10,"Check Payments","")



At the same time, I want to enter a different formula into the same cell, but because it's not possible to enter more than 1 formula, how can I overcome this this limitation? By using some Conditional Formatting and a clear AutoShape.

First of all, let's use part of the above formula and insert it in the Conditional Formatting dialog box with some modifications. (You'll notice there is no FALSE in the below formula, it isn't necessary in this case)

=IF(TODAY()>DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-10,TRUE)

Also, all I want is for the background color and font color to change at this time, the "Check Payments" message is entered at a later stage. (I've chosen a black background and black font because I intend to use white text for the clear AustoShape to the "Check Payments" message stand out)



Next, enter the actual formula you want in the cell. Lets say you want the current year, so try =YEAR(TODAY()).

After this, place a rectangular AutoShape over the cell, using the ALT key to align it automatically against the sides of the cell. Use the Fill Color menu to make it clear by selecting No Fill, then hide the lines with the Line Color menu by selecting No Line. After this enter the text "Check Payments" into the AutoShape.



After this make the text of the AutoShape white and you are finished. When the end of the month approaches, this is what you'll see. (The "Check Payments" message has appeared, and as you can see, the =YEAR(TODAY()) formula is still entered in the cell underneath)



Magic!  
Posted by andrewe at 03:06

May 12, 2004

Getting into Shape, Part 2

Even an artistically challenged person like myself can make reasonably complex drawings with Excel. (Am I being vain?)

One toolbar I always display is the Drawing toolbar. As you can see, I've configured mine with the Group, Ungroup, Bring Forward and Send Backward buttons.



To make a complex drawing, start with component shapes, choosing from the AutoShapes selection. Edit them as you like with features like the Fill Color or Line Color buttons on the toolbar or try right-clicking or double-clicking the shape itself to bring up the Format Object(AutoShape) dialog box. (I've selected Fill Effects, Gradient to give my shapes a metallic look)

Some component shapes



The Format Object (AutoShape) dialog box



Start dragging the shapes together to put them in place while using the Bring Forward and Send Backward buttons to get them in the right perspective, then click the Select Objects button (looks like a mouse pointer on the Drawing toolbar) Sweep around the shapes while pushing your left mouse button or click them one at a time while pushing the Ctrl key.

They should appear something like this.



Now push the Group button to join them together. If you not satisfied, you can use the Ungroup button so that you can move, resize, recolor them at will.



So what is this a drawing of? Actually I have no idea, but I'd say it looks like an automotive part, so let's keep it at that.  
Posted by andrewe at 01:31

May 10, 2004

Getting into Shape, Part 1

Here are 3 simple things you should know when using Excel's multitude of drawing features.

The Ctrl Key
Normally when you 'draw' a shape using Excel, it will expand from the top left corner. However, if you push the Ctrl key while drawing, it will expand from the center outwards. This might come in handy if your are drawing things like circles and want to place the center in a certain position with precision.

The Shift Key
Push this while drawing and it will expand with equal dimensions on either side. For example, an oval will become a circle, a rectangle will become a square. You can also use it to expand (contract) any shape to the same height:width ratio by pushing Shift while dragging the corner handles on the shape's sides.

The Alt Key
The Alt key will position your shape against the sides of the nearest cell or cells, either while drawing normally or expanding using the handles on the shape's sides.

Combinations
Combinations are also possible. Try using Ctrl and Shift or Shift and Alt.

A square aligned against surrounding cells that was drawn while pushing Shift and Alt.



A circle that was drawn while pushing Shift and Alt.



Have fun!  
Posted by andrewe at 20:57

May 09, 2004

Making it all Add Up, Part 2

Previously we looked at SUM and COUNT functions. You can also use SUMIF and COUNTIF to add or count based on criteria of your choice.

SUMIF
Let's say you want to add sales figures from ABC Co., Ltd. Look at cell A13, you can see I have used ranges A2:A11 and B2:B11 with the criteria "ABC Co., Ltd.".



You can also use cell references instead of writing text for the criteria. In this instance, I have used cell C2 to replace "ABC Co., Ltd.".



COUNTIF
COUNTIF works the same way as SUMIF but the second range (B2:B11) is omitted.



Simple Arrays
It's also possible to use an array formulas for SUMIF such as =SUM(IF(A2:A11=C2,B2:B11)). Enter the formula then push Ctrl, Shift and Enter at the same time to make it an array formula. (2 braces will appear automatically at either end of the formula)



You make your own combinations such as COUNTIF, AVERAGEIF, MINIF and MAXIF.



Sure beats doing it all the hard way!  
Posted by andrewe at 21:00

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.

SUM
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)



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



COUNTA
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)



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



AVERAGE, MIN, MAX
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.



SUBTOTAL
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.



1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

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 :)  
Posted by andrewe at 10:57

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 :-)  
Posted by andrewe at 21:17

May 02, 2004

Customizing Toolbars, Part 1

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

Youll 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 Ive 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. Lets 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 Im 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 youve 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.  
Posted by andrewe at 17:07

May 01, 2004

Quick Navigation, Part 2

Last time we looked at Hyperlinks. Heres something extra I always do for really big files.

I always find the 2 places I most want to go are to the top of the file and the bottom of the file. So Ive made 2 buttons like below using the oval AutoShape(You can use any shape etc you want to. Ive also tried to make them more attractive by changing the color and also adding a shadow effect.



Believe it or not, I think Ill start at the bottom. Scroll down to the very bottom of the file. In the case of the below picture, Ive selected cell B102 then entered FileBottom in the Name Box.



Scroll back to the Scroll Down button and right click it to select Hyperlink. Select the Place in This Document tab and then the name you entered, FileBottom. From now on pushing the Scroll Down button will get to the bottom of things real fast.

Now, lets try something else. Open your Visual Basic Editor and paste the following code. (Try pushing Ctrl + F11)

Sub ScrollUp()
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub

Go back to the Excel File and right click the Scroll Up button to select Assign Macro and choose the ScrollUp subroutine youve just pasted. You can now use the Scroll Up button to get to the top in a hurry.

One more thing, youll notice when you scroll down the bottom that the Scroll Up and Scroll Down buttons are not visible.

No problem. Click the Window Menu and then Freeze Panes. I recommend that you do this just under the titles of the file. This will enable you to use the buttons as well see the titles no matter how far you scroll down.



Of course, you can change any of these settings to suit whats best for you. For example, you could adapt the ScrollUp macro to go somewhere else besides the top left corner of the book by changing the values of either ActiveWindow.ScrollRow = 1 or ActiveWindow.ScrollColumn = 1.

Note: As an alternative to using a macro, use another hyperlink to one of the cells just below where the line from Freeze Panes appears.  
Posted by andrewe at 12:27