Helping Average Spreadsheet Users
Become Local Spreadsheet Experts
THIS WEEK'S QUOTE:
"In any moment of decision the best thing you can do is the right thing, the next best thing is the wrong thing, and the worst thing you can do is nothing. " -- Theodore Roosevelt
GREETINGS FROM CANADA
Francis J Hayes (The Excel Addict)
- Quickly Select A Large Table or Long List
- Maintenance Free Subtotals On Your Reports
- Quickly Selecting Items In A List
Last weeks tips...
- Display Your Numbers As Thousands
- Sorting Sideways
- Keep Your Quick Launch Bar Uncluttered
You can still get them here.
You can access my archive of tips on my website by going to http://www.theexceladdict.com/members.htm.
|
You can help!
As you know, I really love using Excel and sharing my tips with everyone who can benefit from them. My goal is to have my weekly tips going out to 1,000,000 Excel users all around the world. I would really appreciate it if you could help me reach this goal by sharing this newsletter with all of your 'Excel-loving' friends and colleagues. You will be helping me and also everyone you share my tips with.
Thanks in advance for your support.
Francis J Hayes (The Excel Addict) |
EXCEL SCREENSAVERS: Don't forget to check out the cool Excel screensavers in the new downloads section of my website. http://www.TheExcelAddict.com/downloads
EXCEL "QUICK TIP"
Do you find yourself frequently dragging to select a large table or list? Maybe you have large lists that you frequently filter or sort. You know how sometimes when you're dragging to select a range of cells and Excel scrolls out of control, way past the range you intended? That can be pretty frustrating. Well, I'm going to give you a quick and easy-to-remember shortcut.
First of all, a 'list' in Excel is a range of cells containing data that is bounded by blank rows and columns (or the edge of the worksheet). That's a very important point to remember anytime you are creating lists in Excel.
If your list meets this condition, you can quickly select your entire list simply by clicking anywhere in the list and pressing CTRL+Shift+* (CTRL+A also works in Excel 2003). An easy way to remember this shortcut is to think of the asterisk as an exploding star which expands outward from the currently selected cell until it reaches the blank rows and columns surrounding your list.
Let me know at fhayes(AT)theexceladdict(DOT)com
Click here for more Excel "Quick Tips".
 PowerPlugs: Excel Solutions for Office Managers is a collection of professional office forms and spreadsheets. There are 21 useful forms. All conveniently work inside Microsoft Excel. They are designed to simplify your daily office tasks and help you run your business more smoothly. Take a look here! |
"HOW TO EXCEL" MINI-TUTORIAL
The problem with this method is that anytime you need to add a new subtotal, you have to update all other formulas that are used to sum the subtotals. For example, if your report has subtotals made up of SUM formulas in cells D13, D20, D27, D34, and D39, your grand total formula in cell D40 may be =D13+D20+D27+D34+D39. Let's say that you need to add some additional data, such as a new Sales Territory, to your report which requires its own subtotal. You will have to insert the new data, add a SUM formula to create the subtotals for the new data, then you have to edit your grand total formula to now include the new subtotal row.
You'll be happy to know that Excel has a built-in SUBTOTAL formula that is going to make creating subtotals and grand totals a breeze for you. Let's use the example I gave above to illustrate. Instead of a SUM formula in cell D13 you will now use =SUBTOTAL(9,D6:D12), assuming D13 totals all data from D6:D12. The 9 in the formula tells Excel to SUM the values in this range. Other numbers can be used to perform COUNT, AVERAGE, etc which won't be discussed in this tip. Just remember that you will be using 9 most of the time for subtotals.
You now add SUBTOTAL formulas to each of your other subtotal rows. As for the grand total row, this is the best part. Create a similar SUBTOTAL formula and, for your reference range, select the range from the first row of your report to the row immediately above your grand total row. It the example we are using, it would be =SUBTOTAL(9,D6:D39). This formula tells Excel to SUM all values from D6 to D39 but ignore the amounts in the SUBTOTAL formulas.
If you aren't already using SUBTOTAL formulas, you have to try them to appreciate them. I've created a practice workbook which you can download right now and practice on. There's also a bonus tip in this workbook that you're going to love. Right-click and save it from here.
Know an even better way?
When you need to open a file from a folder that has many files, you have to scroll down through the list and find the file you want. Try this instead. In the File, Open dialog box, click once in the list of filenames. Type the first letter of the filename. This will take you to the first filename that begins with that letter. Continue pressing the same letter until the file you're looking for is selected, then click Open. Note that this tip works similarly with many other dropdown lists in Windows including Excel. (i.e. Windows Explorer folders and files, desktop icons, paste functions, font names, etc...).
Let me know at fhayes(AT)theexceladdict(DOT)com
Let me know at fhayes(AT)theexceladdict(DOT)com
Click here for more "Non-Excel" Tips.
is a FREE weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630