Julian' s Excel Tips

             last update (30 June 2010)            

Julian's Excel Tips are for beginers to intermediate users. The secret to a great spreadsheet is planning. Most people just jump in and start creating the spreadsheet without thinking about future enhancements. In my personal opinion a good spreadsheet is not static. It is fluid and dynamic, making it more efficient and more powerful. Excel is a powerful tool in the hands of someone who plans & utilises Excel's full potential power. Here I hope to share some useful tips for BEGINNERS when using Excel 2003. 

Formulas

Totals
Every body uses totals right? See the example on the 
right. Most users would create the total on row 3. If 
they need to expand to insert more rows, generally 
they would tend to insert above row 3. This may result
the total excluding the new rows
(unless Excel XP is used). To over come this problem,
always create the total in row 4, leaving a blank row above the total. Resize the row height as shown above and if you need to insert more rows, insert above the blank row (ie row 3). By the way the formula for the total would be =sum(a1:a3) Simple huh?

More Totals
Lets say you need to total numbers that are not in a range, example a1, a5, a10. Well normally the formula would be =a1+a5+a10. If you have 10 cells to total up that would be slow coz you need to type in "+". Next time try using type =sum( first, then hold down the control key, use the mouse & click on a1, a5, a10, then release the control key and type ) and you're done! The formula would be =sum(a1,a5,a10) Fast wasn't it? 

Dates
Excel recognises dates whether you use slash ("/") or hyphen ("-"). However if you use full stop (".") Excel would assume the date as a text. Therefore you will not be able to customise the date styles or even perform calculations with the date. So dates should be 25/12/2004 or 25-12-2004 and not 25.12.2004 (a pretty common mistake).

More Dates
Do you want excel to use the system date? Use this formula =today() and presto you can see the system date. Cool huh? But if you just want the system date without using the formula just press ctrl : and Excel will insert the system date. If you want the date & time use =now() and the date & time will appear.

Text / Strings
There are times you need include numbers or dates together with your text. Typing it in doesn't do much if the numbers keep on changing because it's a formula and you want it linked to your text. Example you need your text to say
"Print date : 31-Dec-2004" but you want the date to be linked to a cell with formula so that whenever you print your report, the date in the text is always changing according to your system date. Do do that you will need a formula. Lets say cell "A1" contains the date (formula =today() remember?). Type this formula anywhere you want the text to appear ="Print date : "&TEXT(A1,"dd-mmm-yyyy") and you're done. Each time you print your report, the date automatically changes.  Similarly if you need a number or value to be linked, you could change the number format to 2 decimals this way (assuming "A1" contains the value)
="Profit for the month is $ "&TEXT(A1,"0,000.00")

File Name
It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file. Just type in this formula =cell("filename") Note that the word "filename" is typed and not the actual filename. And lo & behold, the filename appears in the cell.

Formatting

Quick Formatting
It's a rat race out there sometimes. Short cuts do help & is faster then reaching for the mouse. Especially if your mouse is not working properly (pretty common problem for some of us huh?)

Bold = Ctrl B
Italic = Ctrl I
Underline = Ctrl U
Formatting Cells = Ctrl 1 (this is actually in the menu if you noticed)

Text Formatting
There are time you just want to squeeze everything into one cell. Other than adjusting the column width, you could reduce the font size either manually or automatically. To do it automatically, go to format cells (Ctrl 1), select Alignment, and at Text Control, click on Shrink to Fit & you're done. If you don't want to change the font size but you don't mind a higher row height, then choose Wrap Text instead and automatically the row height will be adjusted.

Centres Text Across Columns
When you need to centre text across cell, the most common method used would be the merge & centre tool. However merging cells could cause other problems when selecting a range or when trying to perform a copy and paste. I would prefer to use Centre Across Selection formatting than to merge cells. Assuming you want to centre the text MICROSOFT EXCEL (which is in cell A1) across from A1 to F1, select the range A1:F1, press CRTL 1 (to format cells), select Alignment tab and in the Horizontal drop down list choose "Centre across selection" and click OK. Done !!!

Clearing Formats
Sometimes we inherit files belonging to someone else who has a bad taste in colour, fonts & borders. You want to clear all formats quickly before you get sore eyes. What do you do? Simple, highlight the range to clear, click Edit/Clear/Formats and all the bad formatting disappears.

Borders
Need to draw tables? If you noticed your formatting tool bar, the border icon has pretty limited border styles. Where the heck do you go to if you want some fancy borders with colours? It's actually under our nose. Click Format/Cells/Border and here you can choose your line style and line colour. First select the line style & colour, then click on the type of border you need. You can view the preview in the centre. Pretty cool huh?

Auto Format (more suitable for tables)
For those who may not have time to format or simple have got an awful sense of colour selection, you could use Excel's Auto Format. First select you range, Click Format / Auto Format and select your choice of formats by clicking on the format that suits you best. You may just find out that you actually have got better taste in colours than Microsoft!!! 

Date Formats
What if you want your dates to show Sunday, 25 December? Simple, select the cell with the date, click Format/Cells/Number/Custom (If you are too lazy just press Ctrl 1 remember?), go to the TYPE text box and type in dddd, dd mmmm but if you want Sunday to appear as Sun the type ddd instead. The same applies to the month. 
d = day        m = month        y = year

Number Formats
Let's say your numbers need to be 4 digits long. 1 to be shown as 0001, 2 as 0002 and so forth. Click Format/Cells/Number/Custom go to the type text box and type in 0000 & click ok. The advantage of this formatting is that you can still perform calculations as it is still a value and not a text.

Tips on overcoming common problems

Data Entry Problems
Entering data in rows & column can be pretty tedious especially when you have wide columns and the great number of columns. Excel has a data form feature that makes data entry a breeze. Select any part of your table and Click on Data > Form. A userform would appear and data entry can be input into your spreadsheet via this form. If there are formulas, it would be greyed out. The formula would automatically copied for the new record.

Date Problems
Getting wrong date formats when entering a date? Let's say you entered 1st April 2010 in excel, but upon pressing the Enter key the date shows 4th January 2010 instead. This is because the window setup for dates is in the American date sytle. To correct this, go to the control panel and change the date setting to dd/mm/yy ie day/month/year format.

Sorting Problems
Unable to sort your dates correctly? If the dates are entered as a text (eg key in as '1/4/2010) then sorting would give you undesired results. You could use the TRIM function to get rid of  all the ' in your dates.

VLOOKUP Problems
Unable to do a lookup? Entries look the same but lookup returns #N/A error. This is a formatting problem. To over come this you have a couple of options. One is to copy the data from Excel and paste it to Notepad. This strips the data from any formatting. Copy the data from Notepad and paste it back to Excel. Presto ! Your lookup now works like a charm. An alternative method would be to do a TEXT to COLUMN under the DATA menu.

Miscellaneous Tips

There are lots of short cuts in Excel. Like I always say to my students, when in doubt, Right Click. Whenever you point & right click at an object, a menu associated with the object will pop up. There is no need to remember the item on menu bar. Moreover, the menu bar is way at the top. Too far for my liking!!!

Menu Bar
The menu bars for Office 2000 applications tend to help you save time by displaying the regularly used commands instead of the full list of  commands and to display the full list you would have to point & wait a couple of seconds or click the double arrows at the end of the list to display the full list. For me, that's too much work. Try double clicking on the Menu items (Eg double click on File and the full list of commands will be displayed)

Standard toolbar & Formatting toolbar sharing one row
Again Office 2000 applications tries to save space buy cramping both toolbars into one row.  Land  is an expensive commodity these days!!! So  just point  at the light grey vertical line at the begining of the Formatting toolbar until you see a crossed double arrow head. Click and slowly drag the toolbar down to place it below the standard toolbar. Now you will be able to see all the icons in each toolbar.

Disappearing Toolbars
How do you turn on your toolbars? Very simple, point at any tool bar & RIGHT CLICK and select the tool bar that you want to appear. Like I always say, when in doubt point & right click.

Moving Around Excel
Everybody knows how to ride a mouse (er... move a mouse). Sometimes the keyboard is faster. Well here's a list of controls.

Page Down = To move 1 page down
Page Up = To move 1 page up
Alt Page Down = To move 1 page to the right
Alt Page Up = To move 1 page to the left
Ctrl Page Down = To move 1 sheet to the right (ie from Sheet 1 to sheet 2)
Ctrl Page Up = To move 1 sheet to the left (ie from Sheet 2 to sheet 1)

Columns - How to Adjust Column Width
This is a simple job. Don't sweat it. When you point between the column headings (That's the Alphabets at the top of the columns A, B, C .... etc) the pointer will turn into a double arrow head. When you see the arrow head, click & drag to the right (to increase column width) or left (to reduce width) to resize the column. So if you want to resize column B, point at the border between column B & C, and just click & drag and you're done. To resize several columns with the same column width, just select the range of columns and resize any one column (like you normally do) and all selected columns will be rezed accordingly.

Quick adjustments to a column (Auto Fit)
If the contents of the cell cannot fit the column & you want it to fit just nicely, you could use auto fit. Just point between the border (just as before) and double click. Wow!

Rows - How to Adjust Row Height
Equally simple. Just like adjusting columns, point between the row headings (That's the numbers on the left of the rows) the pointer will turn into a double arrow head. When you see the arrow head, click & drag up or down to resize the rows.

Row Height related problems (Text Alignment)
When your row height is much higher the your font, you will notice the contents will be anchored to the bottom of the cell. And that's a bit ugly. What if I want it to be in the centre instead. No problem. Select the range Format/Cells/Alignment on the Vertical List box (click on the triangle) select Centre and click OK.

Copying & Pasting
There's more to it than just copy (Edit/Copy or ctrl C) & paste (Edit/Paste or
ctrl V). You can copy  and control exactly what you want to paste. Example you need to copy a total (where you used a formula) and when you pasted it get an error because of the formula. What you need to do is perform the copy (as normal), select your destination and Paste Special (click Edit/Paste Special) a dialogue box will appear, select the option Values and the value of the total will be pasted instead of the formula. Just like magic.

Likewise sometimes we just need to copy the formats (example just the borders), select the range, perform the copy as normal, select your destination and Paste Special (click Edit/Paste Special) a dialogue box will appear, select the option Formats and presto! Just the formats without the contents.

Sheets - How to deal with them
To move a sheet, simply click on the sheet tab & drag to the left or right.
To rename a sheet, double click on the sheet tab & type in the new name.
To copy a sheet, hold down Ctrl, click & drag (to left or right) the sheet you want to copy and presto it's copied.
To delete a sheet, right click on the sheet tab, select Delete.
To insert a sheet,  right click on the sheet tab, select Insert.

Text Boxes (for exact text placement)
When you need place your text on an exact spot, (for instance when you need to print on pre-printed forms) adjusting the row height & column width can be a big headache coz when you adjust, all other text will move. The best way to overcome this problem is to use TEXT BOXES. The icon is in your Drawing Tool bar.

Training Provider In Malaysia
Centrilinc Sdn Bhd - Centrilinc is registered with the Microsoft Partner Program, and courses conducted by us are claimable under the HRDF / PSMB (Pembangunan Sumber Manusia Berhad) SBL Scheme.

Julian's Macro tips

Julian's Excel Solutions

Submit A Link
Do you have an interesting website you think I should include in my Home Page? Or do you wish to exchange a link with me? Please email me giving me details regarding the website. I would be happy to include useful or interesting websites.

<< A Passion To Excel In All Things >>

Back to