Tips and Tricks
AutoSum For Multiple Columns and Rows
This Isn't Excel, It's Magic...the book
AutoFilter
AutoFilter allows you to display only the desired rows that fit into your defined grouping (and filter out the remaining rows). Your entire worksheet remains intact and no data is lost; only the visual display of your items changes.
In this sample, we wanted to look at the Light Bulb items that we carry. Excel looked at the hundreds of rows of items that we have in our spreadsheet, and is displaying only the rows where the Item's Description includes 'bulb':
To set up AutoFilters in Excel, go to the Data menu, then the Filter menu, and choose AutoFilter. The AutoFilter arrows will appear at the top of each column, thus allowing you to filter on whichever column you choose.
For example--as shown above--I can tell Excel to display only the rows where the Description (Column B) includes 'bulbs'; then I can do another filter and have Excel display only my nails.
And you can filter first by Description (Column B), then change your mind and filter instead by your Item ID (Column A).
You can easily return to "display all rows" view--which will make the AutoFilter arrows disappear at the top of the columns--by going back to the AutoFilter menu and select the Show All option.
AutoFilter is also perfect for printing out only the filtered rows (no matter how far apart they are in the actual spreadsheet).
AutoSum for Multiple Columns and Rows
Excel's AutoSum feature is a great time saver when you want to add all the data in a row or column.
But what if you have multiple columns and/or rows that you want to AutoSum--doing each row or column separately is tedious and slow.
How to save time? Use Excel's ability to AutoSum multiple Columns and/or Rows simultaneously.
In our sample here, we want to get 7 totals simultanously...the total hours for each of our 3 employees, the total hours of all our employees for each month, and the total hours of all employees for all three months.
First, enter your data and make sure that there is an empty row or column (or both, as in our sample here) where the AutoSum results can be displayed.
Data Validation
Data Validation is an Excel function that forces the user to enter specific types of information, or formats, in a cell. For example, you can force the user to enter only numeric values into a cell; or limit text length in a cell; or enter only a date; or even allow the user to select from a list of custom values. In other words, Excel validates the data type that is being entered in that cell.
In this sample, we are forcing the user to choose one of the pre-specified 'custom values' in our list:
Using Excel's built-in Data Validation function can help you ensure that data entry in your spreadsheets is done properly, and conforms to the standard that you want.
Formatting Text Within A Cell...No More Boring Text!
Ever want to bold only PART of your text in a cell? Or underline only ONE word out of many? Or use different fonts within that cell? Excel can easily handle all this.
To start, we recommend that you make the Formula Bar visible (if you don't, simply go to View > Formula Bar and select it so that it now is visible.
Next, type your text into the cell. While you are still in that cell you will see that text appear within the Formula Bar. Click into the Formula Bar and format the text there--highlight the appropriate words and apply the formatting desired: bold, color, italic, different font size, etc.
The formatting changes will NOT show within the Formula Bar. But, after you finish and click OUT of the cell you have been formatting, you will see the text change to the formatted text on your spreadsheet.
How simple is that? And dramatically more effective than monotonous ol' black text!
Multiple Windows
Ever find yourself scrolling up-and-down on a worksheet to look at data in different parts of that spreadsheet? Did you know that you can have multiple windows open for the same worksheet or workbook?
In this sample, we are looking at the same worksheet, with one window displaying Rows 1 thru 7, and the second window displaying Rows 118 thru 122 at the bottom of that worksheet:
To set up multiple windows in Excel, go to the Window menu, and select New Window.
The only limit to the number of windows that you can display for each worksheet is your monitor's screen size, and how many windows can fit on it.
This Isn't Excel, It's Magic...the book
We found this book, and LOVED it. And while we didn't author it, we highly recommend it to all Excel users.
Whether you are looking for some simple techniques to make you more productive in Excel, or if you are ready for some sophisticated how-to's, you will learn valuable information from This Isn't Excel, It's Magic.
If you want to get your own copy of This Isn't Excel, It's Magic, click here to go to the publisher's website.
