Julian' s Excel Tips
last update (30 June 2010)
Julian's Excel Tips
are for beginers to intermediate users. The secret to a great
planning. Most people just jump in and start creating the spreadsheet
thinking about future enhancements. In my personal opinion a good
not static. It is fluid and dynamic, making it more
and more powerful. Excel is a powerful tool in the hands of someone who
& utilises Excel's full potential power. Here I hope to share some
tips for BEGINNERS when using Excel 2003.
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.
the row height as shown above and if you need to insert more rows,
the blank row (ie row 3). By the way the formula for the total would be
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
"+". Next time try using type =sum(
first, then hold down the control key, use the mouse & click on a1,
then release the control key and type ) and
you're done! The formula would be =sum(a1,a5,a10)
Fast wasn't it?
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
or even perform calculations with the date. So dates should be
25-12-2004 and not 25.12.2004 (a pretty common mistake).
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
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
Typing it in doesn't do much if the numbers keep on changing because
formula and you want it linked to your text. Example you need your text
"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
is always changing according to your system date. Do do that you will
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
changes. Similarly if you need a number or value to be linked,
change the number format to 2 decimals this way (assuming "A1"
contains the value)
="Profit for the month is $ "&TEXT(A1,"0,000.00")
It is always a good idea to insert the file name & path and include
the print range so that you will know the filename & it's location
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.
It's a rat race out there sometimes. Short cuts do help & is faster
reaching for the mouse. Especially if your mouse is not working
common problem for some of us huh?)
Bold = Ctrl B
Italic = Ctrl I
Underline = Ctrl U
Formatting Cells = Ctrl 1 (this is
in the menu if you noticed)
There are time you just want to squeeze everything into one cell. Other
adjusting the column width, you could reduce the font size either
automatically. To do it automatically, go to format cells (Ctrl
1), select Alignment,
and at Text
Control, click on Shrink to Fit
done. If you don't want to change the font size but you don't mind a
height, then choose Wrap Text
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 !!!
Sometimes we inherit files belonging to someone else who has a bad
colour, fonts & borders. You want to clear all formats quickly
get sore eyes. What do you do? Simple, highlight the range to clear,
and all the bad formatting disappears.
Need to draw tables? If you noticed your formatting tool bar, the
pretty limited border styles. Where the heck do you go to if you want
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
style & colour, then click on the type of border you need. You can
preview in the centre. Pretty cool huh?
(more suitable for
For those who may not have time to format or simple have got an awful
colour selection, you could use Excel's Auto Format. First select you
Format / Auto Format and select your choice of formats by
the format that suits you best. You may just find out that you actually
better taste in colours than Microsoft!!!
What if you want your dates to show Sunday, 25 December?
the cell with the date, click Format/Cells/Number/Custom
(If you are too lazy just press Ctrl
remember?), go to the TYPE text box and type in dddd,
mmmm but if you want Sunday to appear as Sun the type ddd
instead. The same applies to the month.
day m =
month y = year
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.
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.
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.
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.
There are lots of
short cuts in
Excel. Like I always say to my students, when in doubt, Right Click.
you point & right click at an object, a menu associated with the
pop up. There is no need to remember the item on menu bar. Moreover,
bar is way at the top. Too far for my liking!!!
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.
More Tool bar Tips
How do you turn on your toolbars? Very simple, point at any tool bar
CLICK and select the tool bar that you want to appear. Like I
always say, when in doubt point & right click.
If you're sharing a computer, there's always a smart alec who likes to
mess up your toobars. To reset them back to the default style, Right Click any
tool bar, Customise,
Click the Tool
tab, select the tool bar you want to reset & click on RESET and
presto. It's done. If you're a person who needs to be fast &
efficient (a nice way of saying LAZY), customising your toolbars will
give you lots of mileage. Simply customise your tool bar by dragging
icons that you seldom use & replacing them with icons the you use
often. This will speed things up. So if you get a big bonus never
forget yours truly.
Everybody knows how to ride a mouse (er... move a mouse). Sometimes the
is faster. Well here's a list of controls.
Page Down = To move 1 page
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
This is a simple job. Don't sweat it. When you point between the column
(That's the Alphabets at the top of the columns A, B, C .... etc) the
will turn into a double arrow head. When you see the arrow head, click
to the right (to increase column width) or left
width) to resize the column. So if you want to resize column B, point
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.
to a column
If the contents of the cell cannot fit the column & you want it to
nicely, you could use auto fit. Just point between the border (just as
and double click. Wow!
Rows - How to
Adjust Row Height
Equally simple. Just like adjusting columns, point between the row
(That's the numbers on the left of the rows) the pointer will turn into
arrow head. When you see the arrow head, click
drag up or down to resize the rows.
When your row height is much higher the your font, you will notice the
will be anchored to the bottom of the cell. And that's a bit ugly. What
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.
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
formula) and when you pasted it get an error because of the formula.
need to do is perform the copy (as normal), select your destination and
Special (click Edit/Paste Special)
box will appear, select the option Values
and the value
of the total will be pasted instead of the formula. Just like magic.
we just need to
copy the formats (example just the borders), select the range, perform
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
tab & drag to the left or
To rename a sheet, double click on
tab & type in the new name.
To copy a sheet, hold down Ctrl, click &
(to left or right) the sheet you want to copy and presto it's copied.
To delete a sheet, right click on
tab, select Delete.
To insert a sheet, right click
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.
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
useful or interesting websites.
Microsoft Office Training
In today's highly competitive job market, success will
by your ability in fully utilising the power of computers. Build that
competitive edge over the others. Corporate / personal training available in Petaling Jaya, Kuala Lumpur and surrounding areas.
I have been conducting
for over 10 years. With almost 20 years of using spreadsheets in the
field of finance I can assure you that quality training with real
life examples is what you will get.
For more details
email julian.excel @ gmail.com
Don't get left behind in the IT world. On site training available for
Courses available in Microsoft Excel are :
|Excel - Basic (1 day) / Intermediate (1 day)
||Managing A Database in Excel (1 day)
|Excel - Advance (2 days)
||Excel Functions & Formulas (2 days)
|Excel Pivot Tables (1 day)
||Excel Macro Programming - Basic (2 days)
|Excel - Charts (1 day)
||Excel Macro Programming - Intermediate (2 days)
|Excel - Financial Analysis & Modeling (2 days)
||Not listed above? Just tell me what you need
<< A Passion
To Excel In All Things >>