See what you can do with drop-down lists, databases functionalities and SUMPRODUCT
An automated report

Click Here to download a FREE Excel spreadsheet (ZIP)

Click Here to download a FREE Excel spreadsheet (XLS)

On this page are 5 tips that I really enjoyed discovering
5 tips that have allowed me to take a giant leap in what I could do with Excel
Print this page and share it with colleagues

Excel-Tip 1: A Calculator?

You don't need a calculator when you work with Excel. Select two or more cells with numbers in them and take a look at the bottom of your screen, you will see . It is the sum of the selected cells. If you right click anywhere on the status bar, you will see this:

you can select this function to show the sum, the count, the average.... or nothing at all.

Excel Tip 2: Filters

You are ready to issue your report but you would really like to validate certain data. On your data sheet you have 100, 1,000 or 10,000 records (rows) and you want to make spot checks on the data for New York, Los Angeles, John the Salesman, the month of March or amounts greater than $10,000. To accomplish this task you need to use the filters "Data/Filter/AutoFilter" on the menu bar. But to be allowed to use these filters Excel must recognize your set of data as a database.

Here are the conditions to be allowed to filter data and have access to all the "Data" menu items.

1- A single row of unique title cells preferably with a different format (bold font and bottom border for example).

2- No empty rows or columns within the database.

3- Empty rows at the top and bottom of the database (unless it starts in row "1") and empty columns to the right and the left (unless it starts in Column "A").

You may now use the filters. Select any cell in the database and go to the menu bar "Data/Filter/AutoFilter". Small arrows will appear to the right of each of your title cells. Click on them and select the values that you want to see. To remove the arrow, go back to the menu bar "Data/Filter/AutoFilter".

Excel Tip 3: Drop-down Lists

Anybody can add drop-down lists in his/her workbook. Select a cell or a group of cells (ex: A1:A12) go to the menu bar Data/Validation, select "List" in the "Allow" text box and in the "Source" text box submit a list of values separated by commas (John, Mark, Joe, Peter) or a range of cells (ex: =B1:B5). Click OK. Now when you select any of the cells A1 to A12 a small arrow appears to the right of the cell. Click on it and you are offered a selection of the values that you have submitted or of the values that you have entered in cells B1 to B5.

A little extra: submit =B1:B50 even if you only have 5 names. When you add new names in B6 to B50 your drop-down list is automatically updated.

Even if Excel tels you that your list may not be on another worksheet it is false. Discover how to do it in the 35 Resource Workbooks that you can download from this website.

Excel Tip 4: Entering Dates

To enter today's date in a cell of your Excel spreadsheet just hold the CTRL key and press ; to enter the time CTRL/SHIFT and press :

If you want the day's date to appear and change each time you open your spreadsheet, use the formula =now() formatting the cell to show a date. If you format the cell to show a time, it will show the time when the workbook was last opened.

And here is the tip that really changed my life a Business Data Analyst

Excel Tip 5: SUMPRODUCT

We all work with data coming from databases with hundreds and even thousands of records (lines) and many fields (columns) for the date, the product, the account, the city, the branch, the quantity, the amount, etc. So we all need a function that can sum the amount or the quantity based on many criteria like the date, the account, the branch...or based on a combination on many of these criteria (the amount for account 34562, the city of New York, the branch 2353). We need this kind of function because we want to develop a report based on these sums. We want this formula to calculate this sum whatever the number of lines and whatever the order of the data. With this function we can simply add data or refresh the data and the report is automatically refreshed.

I don't think that the creators of SUMPRODUCT in Excel knew that you could execute conditional sums based on multiple criteria with SUMPRODUCT because the "Help" on this function doesn't talk about such a capability. You can't even use the dialog box of this function to execute such a vital sum. My clients and I use it in 75% of the spreadsheets that we develop and you will do the same saving time, money and even creating analysis and reports that you never thought were possible.

Learn more about SUMPRODUCT in a tutorial dedicated to the subject. Hundredss of examples with step by step instructions.

You will find even more in these spreadsheets
And you will see all on them at work in real business situations

35 Resource Workbooks

Examples, templates, tutorials, tests and solutions
And an Ebook on VBA for Excel

excel-vba.com Site Map

excel-vba.com Home Page