101 software tips, tweaks and tricks

Microsoft Excel

033.

 Insert date and time
To insert the current date in a cell, press CTRL + ;. To insert the time, press CTRL + SHIFT + ;.

034.

 Colour your sheet tabs
You can colour-code the tabs on your Excel spreadsheets for easier navigation. Click the tab you'd like to colour, then right click on it and choose Tab Color. Select the colour you want and click OK.

Your tabs can be coloured however you like. (Credit: CNET.com.au)

035.

 Hide your sheet
You can hide Excel worksheets to reduce the number of sheets on your screen. In Excel 2003, just select the sheets you'd like to hide and select Format > Sheet > Hide. To restore them, go to Format > Sheet > Unhide.

In Excel 2007, right click on the sheet tab you want to hide, and select Hide. To reveal it, right click on any sheet tab and choose Unhide.

036.

 Clear formatting
To clear the formatting in an Excel 2003 spreadsheet, highlight the cells you want changed and select Edit > Clear > Formats. In Excel 2007, highlight the cells you wish to reset, click the Home menu, then click Clear, then Clear Formats

037.

 Sort things out
You can rearrange the data in a spreadsheet any way you like by clicking the column(s) or row(s) you want and then clicking on the Data menu, and selecting Sort. In the Sort window, select the column you'd like to sort by and whether you want the data in ascending or descending order.

Sort your data to prioritise your information. (Credit: CNET.com.au)

038.

 All together now
To select the entire spreadsheet in one shot, click the box in the top left corner that joins the columns and rows.

039.

 Mathematics and formulas
Excel supports a whole range of maths to calculate the data you need. The basics are easy enough to get a grip on:

  • To start a formula, you need to enter = in a cell first
  • Use the row and column addresses to perform operations
  • You can use +, -, / (divide), * (multiply), ^ (power of) and brackets to separate the operations. So for example, typing =(A1+B1)/C2*D5^2 would take the value in A1, add it to the value in B1, then divide it by the value in C2 multiplied by the square of the value in D5. As you update entries in A1, B1, C2 and D5, the formula will also update to reflect this
  • To simply add up values quickly, use the SUM command. For example, =SUM(A1:A6) would add up all the values in A1, A2, A3, A4, A5 and A6

You can see the formula for cell B7 in the formula field (shown next to the fx). Here it's adding up all the values from B1 through B5, even though the numbers only start at B2. If any values were updated, or a number put into B1, the value in B7 would also change to reflect this. (Credit: CNET.com.au)

040.

 The extend series function
The best way to explain this function is a demonstration.
  1. Type out in a vertical column: 1, 2, 3, 4, 5, then select the entries as shown below

    (Credit: CNET.com.au)

  2. See the little dot at the bottom right-hand corner of the selection? Left click and drag that down a few cells, then release. Low and behold, Excel has figured out the number series and has added the extra values automatically

    (Credit: CNET.com.au)

  3. You can also do the same with formulas, and it will alter all the cells referenced in the formulas accordingly. In our example below, we have added one formula for the initial total: A2+B2. We then clicked and dragged down, and Excel has intelligently created all the other total formulas

    (Credit: CNET.com.au)

    (Credit: CNET.com.au)

041.

 Make Excel 2007 save to .xls by default
Excel 2007 uses a highly incompatible .xlsx file format for saving files, making it difficult to share with friends and colleagues who don't have the same version. To change this:
  1. Click the Office button in the top left
  2. Click the Excel Options button
  3. On the left-hand side, select Save
  4. Change the Save files in this format drop down box to Excel 97 - 2003 Workbook
  5. Click OK

< Prev 1 2 3 4 5 6 7 8 9 10 11 12 13 Next >
Advertisement

Print feature brought to you by Adobe

Talkback 9 comments

    Ignore "tip" #7 Dean -- 26/09/08

    I didn't bother reading them all, but "tip" #7 (turn off UAC) is totally irresponsible. DO NOT TURN OFF UAC! Demand that your vendor fix their application to work correctly with UAC.

    I am a software developer, and on a daily basis, I see maybe 1 or 2 UAC dialog prompts. For a non-developer, you should practically *never* see the UAC prompt in day-to-day activities.

    But they don't apply to my PC Anonymous -- 27/09/08

    "Our insider secrets will help you master your PC and its most important applications"

    Where are the tips and tricks for Apple and Linux users? Barely any of these 101 tips and tricks apply to MY PC or the applications I use on it.

    Craig and Luis, not everyone in the world uses a Windows PC. Most, but not all. I was more than a little disappointed not to see these facts acknowledged.

    Hamish

    Good Work Anonymous -- 29/09/08

    There's some handy tips in there. Good Stuff.

    A safer alternative to turning off UAC would be using TweakUAC and selecting silent mode.

    party Anonymous -- 29/09/08 (in reply to #320112953)

    Wuz up

    #1 tip for a windows PC Anonymous -- 29/09/08

    I suggest that the number one tip to turn your Win PC into a fast, secure, reliable and productive machine is to install any one of the Linux flavors and remove your buggy, virus ridden, DRM controlled Win partition.

    Re Anonymous -- 30/09/08 (in reply to #320112989)

    I did that. Now life is good

    Another PrintScreen Shortcut Joel Potgieter -- 03/10/08

    You can also get a printscreen of only the window that has current focus [nto the whole screen] by using ALT-PrintSCRN. Also works for dialog boxes.

    Tip 51: Outlook - get rid of the large [emails] Anonymous -- 03/10/08

    If you want to keep the email but delete its (large) attachment, Open the email then go to Edit then Edit Message. Delete the attachment and re-save the email.

    Tip 048 Lookout Anonymous -- 13/07/09

    Nice article, there are really some good tips in there!
    But with one point I am not do not really agree with you...I wouldn�´t recommend Lookout, because it is an old an undeveloped tool! I think it would make more sense to work in Outlook with an modern, supported and developed tool, for example Lookeen( www.lookeen.net)! In my opinion this search tool is the best of all!

Add your opinion

Back to top

Featured