June 11, 2006

Launching Files from the Taskbar

There are times when I want to open certain files in a hurry.

The solution I use is to open them from the task bar (you can open other kinds of files and programs from the taskbar also) This will open Excel if not already open as well as make the files very easy to find.

First of all, the Quick Launch toolbar should be showing. If you want to activate it or if you are not sure, right click the taskbar next to the Start button at the bottom of the screen and select Toolbars. Check Quick Launch if it is not check already (or uncheck it and check it again to see which part of the Taskbar disappears and then reappears) You may have to reset the width of the Quick Launch toolbar, uncheck Lock the Taskbar if necessary (right click the taskbar near the Start button again to see the menu), adjust the toolbar width and recheck Lock the Taskbar if desired. Then again, you may want to leave this step until the end though.

Now open Explorer by either right clicking the Start button and selecting it from the menu, or by simultaneously pushing the Windows key and E. Select the file you want and drag it to the Quick Launch toolbar and you are ready to go. Push the new icon that appears and the file will now open

But there is another thing I like to do, especially in the case of multiple Excel files (or other program files like Word) and that is to change the icon. It makes individula files so much easier to identify. Right click the file's icon on the Quick Launch toolbar, select Property, then Change Icon. You will see a set of standard icons associated with the software you are using, but if you find these are not enough, there should be a Browse button that will enable you choose icons from inside your computer. Still not enough, do a web search for something like "windows icons" and you will find plenty :-)

Don't forget you can also change the name of the tooltip that appears when you hover the mouse cursor over the Quick Launch icon, by right clicking it and selecting Rename.

Here is a pic of a file with an icon I selected as above. Launching it from the taskbar saves a lot of time if I get an urgent phone call.

Note: You can also open files from the Desktop or Start Menu. I prefer using the taskbar because it is always visible and takes less clicking. A drawback is there is a lot less space available on the taskbar, one has to be a bit more picky about which files to add.  

Posted by andrewe at 23:31

May 22, 2005

Simple Graphs

I don't have much call to make graphs but I like to experiment at times. Here's a couple of ways to make some very simple graphs that don't use Excel's regular graph features.

With a Formula
As you can see in the picture below, I've used the REPT function here with some relative referencing to cell A1 and dragged down. (The font used was Wingdings to make "n" a square shape)

The formula for the horizontal bars is


The vertical bars were done the same way, but I merged cells first to get some more space, then used Format, Alignment then Orientation of 90 degrees.

With Conditional Formatting
Actually I've seen the above method before so I decided to try something a little different with Conditional Formatting. As you can see the formula used is quite easy.

And in this case, I've added numbers 1 to 10 in white font to match the background color. Conditional Formatting is then used to change the font to black and also to add borders (lines) if the following condition is True.

Of course I don't imagine that units from 1 to 10 will be used that often but it shouldn't be too hard to adjust your formulas to suit.

Update: Jon Peltier kindly sent me a file demonstrating 2 conditional formats to get something that looks more like a regular column chart, (borders at the sides only except for the top, figures also shown only at the top).

I've uploaded his file so you can refer to it here.

Thanks very much Jon!  
Posted by andrewe at 20:58

March 06, 2005

Freeform Lines Look Cool 2

Here's some more things you can do with freeform lines.

In the below picture you can see two rectangles and two freeform lines. The freeform lines are identical, one was copied from the other.

Now I've used a Fill Color of Gray-40% and No Line for the Line Color of the two rectangles and the top freeform line.

Then I match the bottom (copy) freeform line color to the sheet color and turn off Gridlines (Tools, Options, View, Gridlines), using Order from the right click menu to bring this shape to the front. A little maneuvering and the result is...

Freefrom Lines and 3-D Style
This time I've taken a different approach. The shape below is one continuous freeform line, the corners were plotted by using the above-mentioned Gridlines as a reference. (Hold the mouse button down for jagged lines, release for straight lines, one click to start a new direction, two clicks to finish).

Next I start to trace along the jagged line to create the other half of the picture. (Just a rough copy is fine)

Now for some 3-D styling. I've selected Metal for the Surface, Gray-25% for the 3-D Color and Light Turquoise for the Fill Color. (Keep in mind that colors look darker in 3-D). The result looks quite realistic.

Here's something else. I've drawn another two freeform lines...

Made them 3-D... (yuck so far)

Then used Matte for the Surface, and changed the Line Colors (not the Fill Color) to get a nice ribbon effect.

Don't have quite the right shape? Use Edit Points from the right click menu to help.  
Posted by andrewe at 22:51

March 03, 2005

Freeform Lines Look Cool

Here's something I've started to use a lot recently... using Freeform lines to get that "hand-drawn" effect.

The Freeform button
You can access the Freeform button, from AutoShapes, Lines or you might find it worth your time to drag a button to a suitable toolbar if you plan on using it often. (Select Tools, Customize, Commands, AutoShapes and scroll down to Freeform)

Old and New Excel Versions
This is where it gets tricky. If you draw an enclosed shape using a Freeform line, the shape then becomes opaque and I seem to remember that the Transparency features of older Excel versions are not as good as the more recent versions. The best you can choose in this case is Semitransparent, which is grainy and quite hard to see through.

There is a simple solution. Draw an almost enclosed shape (use the white tags to adjust the size and/or increase the zoom size to make things easier). Right-click the shape and choose Close Path. You now have an totally enclosed shape that is perfectly transparent. (Or you could just double click an already opaque enclosed shape, then select No Fill for the Fill Color)

Let's try a few modifications by double-clicking the shape to bring up the Format AutoShape dialog box. As you can see, I'm lucky to have a newer Excel version and I've selected Turquoise as a Fill Color and set Transparency to 60 percent. At the same time, I've increased the Line Weight to 2.5 pt.

And here's how it looks.

Or you can select No Line for the Line Color and leave an interesting "stain" on your spreadsheet.

Leave a few of these on your files and people will start asking how you made them (at least they did where I work ;-) ) Don't forget you also have the option to print them or not. Look at the bottom of this post.  
Posted by andrewe at 23:17

February 13, 2005

Showing the Print Area

There are times when it's useful to show the Print Area. I like to use lines around the Print Area that don't actually show up when printing.

Here's a picture of the Print Area shown in blue.

I select cells just outside the Print Area and use Format Cells, Border from the right-click menu to add lines on all four sides around the outside.

Heavier lines on two adjoining sides gives a nice 3-D effect.

If you've done things right the lines won't show up when printing as mentioned at the top.  
Posted by andrewe at 19:10

December 15, 2004

See Through Shapes

Ever want to show part of a shape as "see through"? Look at this picture to see what I mean.

It's a good effect so here is a way to do it.

First make the shape you want to appear "behind". Then make the shape you want to appear "in front". Go back to the first shape, select it and make a copy.

The next part is rather sneaky. Change the Line Style of the copy to 1/4 pt (the thinnest available) and the change Dash Style to "Dash". Then change the Fill Color to No Fill.

Go back the first and second shapes and change the Line Style to about 1 1/2 pt (5th down) and also change the Line Color to Gray - 80%. This is match the copy as it seems to become lighter when the Line Style is changed.

Place the shape with the dashed line directly above it's original shape. (If necessary, use the Order buttons on the right-click menu to make it appear in the right position)

You should now have something like the top picture. Try moving the "in front" shape around. The dashed line shape should "change" to match.


You can also get 3-D Style, (Style 4) for transparent shapes but not with dashed lines. So how did I get the shape on the left?

2 squares and 2 parallelograms, with dashed lines and no Fill Color. Who says a cube needs to be made up of 6 sides? ;-)  
Posted by andrewe at 22:12

December 12, 2004

Names vs. Numbers

Have you found yourself in this situation? Rather than type full names, you decide to use to start off using reference numbers, but after a while there are so many numbers that it becomes impossible to remember them all.

This might be a solution. Make a list of the names using Data Validation as below.

This should give you a pop up list that appears each time you select the cell. (Copy and paste as required)

Now you have the names but if you still need the numbers, perhaps you can get the number by using the MATCH function.


In this case, MATCH returns the position that name shown in cell E2 appears in the range C13:C22. ("0" is the same as False, both will look for an exact match)

I've also added IF to return nothing if cell E2 is blank and since the start number is 1234, just adjusted the MATCH part of the formula to suit.

Note: If MATCH doesn't work because of the way your numbers are set up, consider using VLOOKUP or SUMPRODUCT instead.  
Posted by andrewe at 22:27

December 05, 2004

Text and Shapes

One thing about shapes in Excel is the difficulty of aligning text.

For example, here we have a picture of text being formatted inside an shape.

It looks fine until the shape is rotated like this.

How to overcome? There are 2 ways that I like. If you want the text at the same angle as the shape, use WordArt like below to get the desired results.

If you don't have so much text, you may prefer not to rotate it, just the shape itself. Even so, depending on the type of shape that you use, it can be difficult to get it centered exactly.

In this case, my suggestion is to use another shape, but formatted with No Fill and No Line.

This should enable you to position it with a minimum of fuss.  
Posted by andrewe at 19:57

November 03, 2004


I was asked the other day about using Greek symbols in Excel. (Hi Valerie!) Greek symbols are particularly useful as they pop up all the time in Mathematics and stuff.

There are a few ways to do this. One of the easiest ways is to select Insert, Symbol to bring up the Symbol dialog box.

Unfortunately earlier versions of Excel don't have this feature, but I used to access it on Word instead and then copy and paste symbols into Excel. (Sometimes dumb is smart)

Another way is to use the ALT key and then type in combinations of 4 digits with the numeric keypad. This method runs into problems if you are using a laptop :-(

Yet another way is to use the Windows Character Map which seems to show each and every font you have installed. You can find it on the Start menu (Start, Programs, Accessories, System Tools)

It's a trifle unwieldy but you can copy characters to the clipboard and then paste it where you like. (I'd include a picture but my system is Japanese Windows so it wouldn't be much use unless you can read what it says)

Then we have the CHAR function. This function works quite well but Greek symbols were not included. It also seems that the characters available change according to your system's language. Fortunately some of the more common characters appear to remain the same which is quite useful when working with formulas.

Last but not least, I found an interesting way to get the aforementioned Greek symbols by using the Symbol font (no surprise?)

Here's a picture of small and capital letters in a regular font, with the equivalents in Symbol font side by side.

Not so useful to some, but in my case I often use the "diameter" symbol at work and so now I can just use a small f, then use the mouse to change to Symbol in italics where it is needed to get something like this.

It also works with other software like Word and Outlook Express.  
Posted by andrewe at 17:15

October 17, 2004

Protect Formulas

I've posted a few times about Protection (Please look here, here and here)

Here's another very quick way to (partially) protect formulas.

Go to Data, Validation and select Custom from the Validation Criteria dropdown box on the Settings tab. Then enter the following into the Formula text box.


Simple. If you try to overwrite the formula, an error message will appear like below (you can choose a different style on the Error Alert tab)

So, what's happening here? Well, ="" means "equals nothing", so if you try to enter "something" an error will occur.

However, this is not 100% reliable as it won't prevent deletion by using the Back Space or Delete Keys, and the cell can be removed by deleting the Row or Column also.

At the same time, it will prevent accidental overwriting in most cases, so it's a good protection alternative to remember. It's easy to use and every little bit helps.  
Posted by andrewe at 20:09

September 24, 2004

Highlight with AutoShapes

Let's say you have something you want to draw attention to. Maybe it's something that's been changed or perhaps it's something you need to check.

Instead of using a regular comment, I often use an AutoShape placed over where I want to highlight and then make it semitransparent by right-clicking and selecting Format Autoshapes, then Colors and Lines. (There is better transparency control offered in later Excel versions - you can set the rate at a percentage with either manual input or using the scroll shown in the below picture)

Then I select the Properties tag and uncheck Print object.

You now have a "colorful" reminder which won't show up when you print but still enables you to see what's underneath.  
Posted by andrewe at 23:59

September 09, 2004

AutoShapes in a Hurry

Here's a couple of quick tips to make working with AutoShapes easier.

Quick Tip 1
Drag any of the sub-menus from the main AutoShapes toolbar to keep it permanently displayed as a floating toolbar or drag it to dock against one side of your Worksheet.

You can also double click an AutoShape to insert them continuously. Click the shape again when you are finished.

Quick Tip 2
Once you have inserted your AutoShapes, you can save time by selecting all of them if you want to make the same changes to each.

Push the Select Objects button and drag around the shapes to include all of them.

Any changes you make to one will be made to the others also.

Posted by andrewe at 23:14

September 03, 2004

Delete Blank Rows 2

By coincidence, I came across another way to delete blank rows today when I was using an AutoFilter at work. It deletes blank cells with formulas too.

I was using a formula to ignore duplicate entries in Column A (future tip) which in showed up as unique entries in Column B. In the case of duplicates in Column A, corresponding rows in Column B were left "blank", but they still contained my Ignore Duplicates formula.

Then I had the idea of using an AutoFilter. How it works is like this. Use the AutoFilter at the top and select Nonblanks.

Then copy the range and paste in another column.

Bingo, the entries without the blanks are now pasted as values. All that remains is to use the AutoFilter to select All once more.

PS. I could also use an Advanced Filter to show Unique records only, but I prefer this way because after I select All in the AutoFilter, all cells on the worksheet are visible again, including my selection minus the blank rows.  
Posted by andrewe at 01:52

September 01, 2004

Delete Blank Rows

Here's something I saw on a forum the other day. It's a quick non-VBA way to delete blank rows fast.

Ready? Push F5 (Go To dialog box shortcut), then Tab twice followed by Enter. Then push "K" and Enter once more. The blanks rows in your range should be selected.

Next push the Right Click key, (usually somewhere on the lower right of your keyboard), then "D", "R" and Enter.

One thing I noticed was that it although it works fine with cells that have Conditional Formatting or Data Validation, it doesn't work with formulas. Although the cells may look blank, by definition they are not.

Anyway, I thought it was a neat trick if you can use it, with practice you can do it quite fast.  
Posted by andrewe at 21:19

August 28, 2004

Sunken & Raised Text

Here is a way to get sunken and raised text.

First you will need your Drawing toolbar. If it's not already displayed, right click any other toolbar and select it from the menu.

Then using WordArt, choose your style and edit the text as necessary.

Next you will need to choose your Shadow Style. Use Style 17 for raised text and Style 18 for sunken text.

This is what you should end up with. (I prefer making the text and background the same colour)

It's a good effect to use for things like Hyperlink buttons.  
Posted by andrewe at 16:36

August 10, 2004

Fill 'er Up Part 2

Here are a few other things you can use to fill cells.

The Fill Format
Enter any number or text in a cell, then right click to select Format Cells, Alignment, then use the drop down list shown as Horizontal to select Fill.

Anything you enter will repeat to fill the entire cell.

The REPT function
If you just want to repeat something a certain number of times, you can use the REPT function as in the picture below.

A Custom Format
This is a way to mix entries with whatever Fill format you choose.

Right click the cell to select Format Cells, Number, Custom.

If you wanted a line of dots to show continuously (fill) to the right of your entry, use @*. or you can use *.@ to show dots to the left.

Experiment and have fun!  
Posted by andrewe at 22:30

August 08, 2004

Fill 'er Up Part 1

Excel has lots of useful Fill features. Here are just a few of them.

Edit, Fill
Here's a look at the Fill options available from the Edit menu.

Using Down, Right, Up and Left, you can quickly copy data, format or formulas into neighboring cells using the direction you choose. (Take note of the Ctrl + D and Ctrl + R shortcuts. Another useful shortcut is Ctrl + Enter which can be used to enter the same data or formula into multiple cells simultaneously)

You can also copy across Worksheets. Right click the Sheet tab to access Select All Sheets, then use Across Worksheets from the Edit, Fill menu.

There are some very good options available using the Series dialog box (Edit, Fill). Here are some examples.

Here I have used Linear which can be used to add the number entered in Step (in this case 10). I've also used Stop to for a maximum value of 50 (if I didn't use Stop, the series would continue to end of the range selected with the mouse)

In this case I have used Growth to multiply by 10. Again, the Step value is 10 so all cells that follow are in multiples of 10.

Here I have added months by using Date, Month with a Step Value of 3.

Finally , we have the Autofill option. To be honest, I think it's a lot easier to use the Auto Fill handle and it offers more options also, depending on what you actually want to do.

In the above picture I used a Step value of 3 to add 3 months at a time. This is also possible with Autofill, but you have to "specify" a step value by entering the first few cells to get the same step value throughout the range.

At the same time, if your are dealing with a step value of 1, then the Auto Fill handle may be the way to go.

Custom Lists
You can also add make your own Custom lists by using Tools, Options, Custom Lists, NEW LIST. If you want to use the same data in the same order on a repeated basis, this is a very handy feature. Note that the list can be used continuously once entered, unless you choose to delete it later on.

Once your Custom List is set, you can just enter the first cell, then drag down to fill following cells with the entire list.  
Posted by andrewe at 20:37

July 05, 2004

Go to Go To

It's a great navigational aid, I love the Go to dialog box.

Just push F5 and it appears on your screen, showing all of your cell and range names, plus it also shows the last four places you have been to, even on other Worksheets or Workbooks. (The ability to jump to other Workbooks make it even easier to navigate than the Name Box next to the Formula Bar)

Here's an example.

If you're not sold yet, try pushing Special. As you can see, there is a whole bunch of new selections available.

Conditional Formatting is a great thing, but if you're not careful, inserting rows can make other rows gradually creep down the page...(Hey, that's funny! Why are these cells changing colors where they shouldn't ?!)

Problem is that unless the conditional format itself is met, it's very difficult to know which cells are formatted and which ones are not.

This is where the Go to dialog box comes in handy. Select Conditional formats as in the image and they will be highlighted after you push the OK button.

I tried this a couple of days ago at a whim and was surprised to see a group of conditionally formatted rows had somehow broken away from the main group and were on their merry way to the bottom of the sheet. (Key word is were)  
Posted by andrewe at 00:45

June 07, 2004

Hiding Stuff, Part 3

To hide sheets, you can go to Format, Sheet and then select Hide or Unhide to hide or unhide any sheets you choose.

What's the advantage of hiding sheets? Well, if you are not using all of them, you might want to save some space where the Sheet Tabs are shown (too many tabs means not all will be visible and this will make harder to navigate around your Workbook)

You may also use a sheet for miscellaneous data such as pricing tables for LOOKUP or other reference formulas that are only referred to or updated recently. You might hide this sheet as a form of protection (you could protect the sheet and hide it to make it even safer. It might seem paranoid but just how important is your data?)

Or if you are really concerned about people snooping around you might consider making a sheet very hidden.

Hiding Sheets - Making Sheets Very Hidden

By making your sheets very hidden, you are merely preventing it from being noticed from casual users. It won't appear under sheets to unhide using the Format, Sheets method. But I should say that anyone who knows about VBA (even low life sludge like myself) knows how to find and unhide Very Hidden sheets. That said, it should still be a reasonable measure of protection for around the office and such, but think very hard before you email a Workbook with sensitive information to people you don't want to see it in the first place!

Okay, assuming you still want to know, let's go to work. Open the Visual Basic Editor. There are a few ways to do this. One quick way is to use the shortcut keys Alt + F11. Another way is to right click any Sheet Tab and select View Code.

In the Project Explorer (top left) you will see a list of files. Let's say your Workbook is Book 1, navigate to the sheet you want. In this case I have chosen Sheet 3.

In the Properties Window (bottom left) select Visible. (It should appear in both the Alphabetic and Categorized tabs) Click on the right and a drop down list should appear. Select Very Hidden and you're done.

So now you know. I just hope I haven't broken some little known taboo in the Excel community. If I don't post within the next few days, do me a favor and call Missing Persons!  
Posted by andrewe at 11:54

June 05, 2004

Hiding Stuff, Part 2

Hiding and unhiding rows or columns is quite easy. After dragging across your selection with the mouse, you can use the Format menu to select Rows or Columns and then Hide or Unhide, or you can just use the right click menu which is a lot faster.

This is fine for hiding (or protecting) formulas or data on a semi-permanent basis which you do not need access often. But you may find yourself with rows or columns that you want use regularly but still want to hide them to keep them out of the way at least some of the time (not to mention the bother of continuously hiding or unhiding a large number of rows of columns if you are working with a large file) One example I can use is from real experience - I usually refer to my company's part numbers rather than customer's part numbers for ease of inner company communication but on occasion I may want to look at the customer's part numbers to confirm I am referring to the right one. In other words, I want them within easy reach but usually kept out of the way.

There is an easier way! Open the Customize dialog box from the Tools menu and make a toolbar as per this tip. Then on the Commands tab scroll down to the Data menu where you can see the Outline commands as per the below picture and drag the Show Outline Symbol, Group and Ungroup buttons to your new toolbar.

Select the rows you want to hide and then push the Group button (right facing arrow button). You will see Group number buttons appear automatically in the top left corner. You can make groups and subgroups (subgroups being a smaller selection of an existing group of rows or columns).

In the next picture you can see an example. Columns C to E are a subgroup of Columns B to F. You'll notice that Rows 4 to 5 are already hidden. To open or close these rows and columns I can either push the cross buttons to hide or unhide individual groups or subgroups, or I can use the numbered buttons to hide or unhide all of the groups (subgroups) with just one click.

How handy is this? Very! Once you finish with your initial grouping, you will be amazed at how simple and quick it is to hide and unhide the rows and columns you choose. Just use the Show Outline Symbol to access the Outline buttons whenever you want to do so.  
Posted by andrewe at 12:07

June 02, 2004

Hiding Stuff, Part 1

Those Pesky Errors

I don't like errors cluttering up my workbooks so I thought I would mention a way to get them out of sight and out of mind. (Turning my computer off is not an option!)

Here's a quick way using Conditional Formatting. Assuming the error I want to hide is in cell A1, I enter the formula shown below and format using a white font. It will hide errors but not normal imput (numbers or text).

Those Pesky Zeros

These aren't as bad as errors but can still be an eyesore (then again they can be very useful depending on what you are doing). You could use Conditional Formatting to do this but the easiest way is to open up the Options dialog box and select the View tab, then untick the Zero Values checkbox. They're history!

Hiding Everything?

There are many ways to do this. Probably the easiest way is using white as your Font Color thoughout the Worksheet or Workbook, but next time I'll look at a more selective way to only hide what you want to.

Other tips are always welcome!  
Posted by andrewe at 18:32

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.

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


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)

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