everydayExcel Newsletter Header

Tips and Tricks 2007

The complete change in the Excel 2007 interface has jolted many Excel users out of their comfort zone as they struggle to locate and familiarize themselves with the regular functions they have been using for many years. Most Excel users tend to stick to the regular functions that they have been using to get their job done and may not be aware of shorter and more productive methods of working with the new version of Excel. As such, we have specially identified and compiled more than 30 Tips and Tricks using the new Excel 2007 interface. Each of the Tips and Tricks has the potential of saving you hours of cleaning up the data manually, drastically changing the way you use Excel. 

Our Tips and Tricks include:

  • Customize The Quick Access Toolbar (QAT)
  • Double Click On The Ribbon To Hide It
  • Quick Access To Frequently Used Files
  • Tracking Real-Time Changes With Excel Camera
  • Jump Instantly To The End Of Your Database
  • Searching And Entering Formula With Autocomplete
  • Create Attention-Getting Comments
To learn these simple yet powerful functions, you can either

1. sign up for our seminar on 23 May 2012, 2pm to 5pm at International Plaza. You will also receive our complimentary Tips and Tricks book when you attend this seminar. 

2. purchase a copy of our book online and have it sent to you via post. 

3. purchase and watch the Excel 2007 Tips and Tricks online

Increasing Productivity With Simple Solutions

In March, I attended a seminar regarding the Singapore Budget and how companies could make use of the government incentives to transform themselves, increase their productivity and manage their businesses with a tighter labour market. The event organized by a high profile association attracted hundreds, maybe close to a thousand participants. As I approach the reception, I saw three persons, each holding a stack of paper with the names of the participants, checking whether participants have made the payment for the seminar prior to the seminar. I paid on the day of the seminar using cash and requested for a receipt. However, I was told that the receipt could only be issued via post after the seminar because it would be too time-consuming to write receipts on the spot. Their response immediately triggered me to wonder why they had not prepared a laptop and used Excel to print the receipt. That would have saved them an estimated $1.00 per receipt (which includes receipt writing, handling and postage costs). Multiply that by 1000 participants and that would have been a savings of $1,000. But, how could they have printed the receipt using simple Excel formulas?

  1. Design a layout for the receipt and duplicate the layout for the receipt.
  2. Create a dropdown using data validation for the list of prices, from $10 to $100, in multiples of $10. That should cater for companies with 5 sign-ups, each paying $20. (Each participant has to pay $10 or $20 for the seminar.)
  3. Using the VLOOKUP formula, we can display the seminar's fee amount in words. So when they select the amount in numbers, the amount in words will also automatically show up.
  4. Create another VLOOKUP formula to enter the serial number of the participant on the name list. When the serial number is keyed in, the company name of the participant will also show up in the receipt.
With this setup, only entering the serial number and the fee amount is required and this probably takes less than 5 seconds. Click the print button to print the receipt on the spot. Assign the receipt with a number and the page will print out two copies of the receipt. Tear out one copy for the participant and keep the other. The entire process would take less than 30 seconds and save the postage fee of $1.00. In one hour, they would have saved $120. That is what I call increasing productivity with simple Excel solutions!

Ask us an Excel question and get your question answered by our team of Excel experts


I use a work book to keep a record of each staff members holiday records.

Work book comprises of a master sheet which collates entitlement and remaining hours for each staff member (linked from their individual worksheet). Each staff members records are recorded on a desperate worksheet named as a number. 1, 2, 3 etc

The workbook is protected to read only for staff, but all staff members can view each others records.

What I want to achieve is to set up individual passwords for each staff member so that when opening the workbook they can only see their worksheet. But, also set so that I can open all worksheets with only entering one password and not having to use staff members individual passwords to update their records. 

Click on this link for the answer.


I have a data sheet with a list of 100 venues, each with different room hire costs for each day of the week. I am looking for a formulae that will pick up the costs based on the venue and day chosen. 

Click on this link for the answer.

Upcoming Courses

With the newly introduced PIC bonus, you are now paid (not paying) 60% of the course fees for sending your staff to our courses. Check out now in this IRAS PIC Link.

Hidden Secrets in Data Analysis with Excel 2007/2010 - 5 & 6 Jun 2013 

Dynamic Real-Time Forecasting with Excel 2007/2010 - 27 & 28 May 2013 

Learning the Magic of Macros 2007 (Excel VBA) - 16 & 17 May 2013 

Breakthrough Performance for HR in Excel 2007/2010 - 25 & 26 Mar 2013 

Interactive Dashboard Reporting with Excel 2007/2010 - 6 & 7 May 2013

For the details of the courses, please refer to our Excel courses page.

Subscribe to our newsletter

If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.

Follow us at Facebook, LinkedIn

If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.

Yours Sincerely,

Jason Khoo
Chief Trainer
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and 
"Excel Secrets for Highly Effective Marketers"


Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.