"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"
Hello Everybody,Like i have promised here are a few tips and tricks on Microsoft excel. I have learned all these tips from an excel addict named Francis Hayes. Thanks to him who has made my life hell lot of easier by providing all these great tips . This guy has a great book on excel name 101 secrets of excel addict and i am really looking forward to buy this one which i would definitely going to share with all of you. So for the time being here are my todays TIPS ON EXCEL. Till then wait for my next post.
Avoid Numeric Heading Errors In Your Formulas
Often you will use numeric headings in your worksheets. For example, you may have a table that uses years for the headings (i.e. 2004, 2003, etc...) Sometimes these headings may incorrectly get included in the calculations for some formulas, such as when you use AutoSum to total a column which has a numeric heading. Excel's AutoSum sees the heading cell as numeric, so it includes that cell in the formula. This can easily happen and likely will lead to costly and embarrassing problems.
There are a couple of ways to avoid this error. One is to precede the numeric value with an apostrophe (i.e. '2004). However, my preferred method is to format all of the heading cells, often the entire row, as Text.
Here's how:
- Select the heading cells (or the entire row)
- Select Format, Cells, click the Number tab.
- Select Text from the Category box, and click OK.
Now when you use AutoSum, Excel recognizes the heading as text and doesn't include it in the formula.
Prevent Duplicate Entries In A Range
If you want to prevent duplicate data from being entered into a range, here is a pretty simple solution by using Data Validation.
- Select the range of cells in which you want to prevent duplicates (for example, A1:A50).
- From the Data menu select Validation.
- Click on the Settings tab.
- From the Allow dropdown box select Custom.
- In the formula box type =COUNTIF($A$1:$A$50,A1)=1 (see explanation below)
- Click on the Error Alert tab.
- In the Title box type "Duplicate Entry!"
- In the Error Message box type "You cannot enter a value already in the list."
Explanation:
This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($A$1:$A$50,A1)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert.
Instead of typing the cell ranges in the formula, you can select them with the mouse. For the validation range (i.e. A1:A50) select the range, then press the F4 key to make it an absolute reference (includes dollar signs). For the criteria range (i.e. A1) click on the first cell in the validation range (no dollar signs). You do not need to copy this formula. When you select the range first and then apply Data Validation, the validation applies to the whole range. The cell reference you use in the formula is not limited to one column. You can use a range such as $A$1:$C$50.
Editing A Range Within A Formula By Dragging
I often see Excel users completely redo a formula because a cell reference within the formula is incorrect. Sometimes this is the best option, especially if it is a simple formula such as a SUM formula for the range immediately above. But when you have a more complex formula that contains several different range references, try the following method to easily correct the error.
- With the cell containing the formula selected, identify the incorrect range in the Formula Bar.
- Now, down in the worksheet, select the correct range of cells which will replace your selection in the Formula Bar. If necessary, use the scroll bars to move the range you wish to select into view.
- Now, down in the worksheet, select the correct range of cells. If necessary, use the scroll bars to move the range you wish to select into view.
- When you are satisfied that you have the correct range in your formula, press Enter.
If you make a mistake selecting the range:
- If you haven't already pressed Enter, press the Esc key to cancel the change.
- If you have already pressed Enter, select Edit, Undo (CTRL+Z) to undo the change.
Showing Just The Day Of The Week
Sometimes you may be using dates in your worksheet and want the cell to show the day of the week (i.e. Sunday, Monday,..) rather than a date. You could simply type in the day of the week in the cell, but if you have formulas in your worksheet that refer to that cell, those formulas would get messed up. What you need to do is create a custom number format for the cell containing the date.
To create a custom number format for the day of the week is easy.
- Select the cell containing the date you want displayed as a weekday.
- From the menu select Format, Cells, and click the Number tab.
- In the Category box select Custom from the list.
- Type dddd in the Type field on the right side of the dialog box
- Select OK.
Now, go back and try that again, but this time notice as you type each d in the Type field how the sample format on the top of the dialog changes. You can use this technique to display the day of the week in four different formats: 1-digit number, 2-digit number, 3-digit text, or full-word text.
Pasting Part Of Your Worksheet To Another Document
If you ever need to include a small part of your worksheet in an email, a Word document, or a PowerPoint presentation, a simple solution is to paste it as a picture.
- Select the cells you want to copy.
- Hold down the SHIFT key and select Edit, 'Copy Picture'. (Note, the Copy Picture menu item is only available if you hold down the SHIFT key).
- In the Copy Picture dialog box select either 'As shown on screen' or 'As shown when printed'. Try each of these to see which you prefer. If you choose 'As shown on screen', the gridlines will be shown in the picture if they are visible in your worksheet. If you choose 'As shown when printed' the gridlines will not be visible in the picture.
- For the Format option choose Picture.
- Click OK
- Switch to the document where you want to paste the picture.
- Press CTRL+V (i.e. keyboard shortcut for Paste) or choose the Paste option from the menu or toolbar for that program.
Page Break Preview
Often you have to go through trial and error to get multiple page reports to print the way you would like. It can sometimes be tricky getting the pages to break at the right place. Print Preview will show you how your pages will print, but changing page breaks (Insert, Page Break) and switching back to Print Preview can sometimes be awkward.
A much more user-friendly solution for getting your pages to break at just the right places is to set and adjust your page breaks in Page Break Preview mode. In Page Break Preview you are given a view of your worksheet with page breaks shown as blue lines. Blue dashed lines show automatic page breaks and solid blue lines indicate manual page breaks (i.e those set by the user).
The best part of Page Break Preview is that you can easily adjust page breaks and instantly see how the changes affect the printing of your report.
To set or adjust page breaks in Page Break Preview:
- Select View, Page Break Preview
- Move the cursor to one of the blue lines separating a page until your cursor changes to a double-headed arrow.
- Drag to move the page break.
The Secret Of Pasting Multiple Items In One Step
Say you have several lists of data and you want combine them into one list. Maybe they are on different worksheets or even in different workbooks. You have a couple of options. You can copy and paste each list individually, typically what most Excel uses do, or you can copy everything first and then paste them all in one step.
I'll use an example to explain this. Say you have a workbook with separate sheets for each soccer team and each sheet contains the names of soccer players. You want to create a summary worksheet with all of the names together for tracking stats.
You can go to the first team sheet, copy the names, switch to the summary sheet and paste, go to the next team sheet, copy the names and so on. Another option which you may not be aware of is that Office allows you to copy multiple items of data from various worksheets (or even other Office documents) and then paste them in other documents. You can paste them in any order or paste them all in one step. The big secret here is that you need to use the Office Clipboard, which you may or may not have seen. The copy and paste buttons for the Clipboard are on the Clipboard Toolbar. Normally when you copy two items in a row, the Clipboard toolbar will appear automatically. However, if it doesn't appear, you can right click on any toolbar and select Clipboard from the list of available toolbars.
In this simplified example, you can copy the names from the first sheet, copy the names from the next sheet, and when you have the names from all sheets copied (maximum allowed is 12), you can then switch to your summary sheet and paste them all in one step.
Just try it and see how it works!
The Clipboard also gives you the option of pasting the individual items in any order.
When you have multiple items on the clipboard, it may be difficult to remember which item is which. A little trick I've learned is to pause your cursor over the clipboard item for a second and you'll see a small Tool Tip pop up showing a small part of that item. Click on the individual item and it will be pasted.
Using Headers and Footers
How many times have you seen a printed report and wondered who created it and when? How often have you had a printed spreadsheet but couldn't remember the filename? I've seen people start over from scratch because they couldn't find the workbook that the report came from. Often reports get distributed to others who have no idea where they came from. To the reader of a report, when a report is created and printed is often useful information. Using headers or footers on your reports is a great habit to get into that could save you and others a lot of grief.
If you're not familiar with headers and footers, here is a quick introduction. Headers and footers contain information that you may want to print at the top of each page of your report, such as page numbers, print dates, filenames, etc... These are different from column headings, which you can print at the top of each page. Header and footer information is contained in your print settings not in the cells of your worksheet. Headers print on the top of each page. Footers print on the bottom of each page.
The most common use of headers is to display page numbers (i.e. Page 1 of 19). Excel offers the most commonly used headers as presets that you can choose from a dropdown list. Select File, Page Setup, Header/Footer, click on the Header or the Footer dropdown and choose one of the preset options. The preset options can be placed either on the top of the report, on the bottom of the report, or both. You have no control as to whether the presets print on the left, in the center, of on the right. Some of them will contain information that will print on the left, right and center of the page.
You also have the option to create your own custom headers and footers. Select File, Page Setup, Header/Footer, click on the Custom Header or the Custom Footer buttons. Decide if you want your header or footer on the left, right, or centered. Click in the appropriate box and type in what you want printed as part of your header or footer.
You'll notice buttons in the Header and Footer dialog boxes. The first one is the Font Button. It allows you to change the font of the information you have selected. The second button is the Page Number Button. This button inserts code that tells Excel to print page numbers. The third button is the Total Pages Button. This is commonly used in conjunction with the Page Number code. For example, to have your header print "Page 1 of 19", you would type "Page " (without quotes), click the Page Number Button, type "of ", and click the Total Pages Button.
The fourth button is the Date Button. This button inserts code to tell Excel to print the date in the header or footer. The date used is the date that the report is printed. Next is the Time Button. Printing the time and date in your header or footer helps when you make frequent revisions to your work and need to know which printed report is the most recent.
Using the next two buttons, the File Name Button and the Sheet Name Button, make it easy to match your printed report with the file on your computer.
Aside from the codes that Excel allows you to put in your header and footer, you can type almost anything else to suit your needs. The company I work for uses the following very useful information as a standard in our footers: Filename, Originator, Date, Time. The printed footer looks like this...
June Expense Report.xls (Orig. F Hayes)
31/5/04 11:45AM
Now when anyone gets a report, they know who produced it and when.
Headers and footers are another of Excel's features that most users do not fully utilize because they do not understand their capabilities. Hopefully my (rather lengthy) tutorial will help you appreciate their usefulness.
How To Use Excel's IF Function
Most Excel users have heard of and seen IF functions used in worksheets, but a large number of these users find the IF function confusing.
The IF function is one of the most used worksheet functions in Excel and, contrary to what you may think, the IF function is pretty easy to understand. It is so versatile that, once you learn how to use it, you will think of many, many places to use it. It will open up many new opportunities for you as you develop your workbooks.
The purpose of the IF function is to put one of two values in a cell based upon a condition.
There are four parts to the IF function.
=IF(Condition, ValueIfTrue, ValueIfFalse)
- The function name
- The condition to test (must evaluate to either TRUE or FALSE)
- The value to put in the cell if the condition evaluates to TRUE
- The value to put in the cell if the condition evaluates to FALSE
Note that the value returned can be either text, a number, a formula, or a reference to another cell or cells. Text must be in quotes.
In plain English the function says, If the condition is True, put the value of ValueIfTrue in the cell, otherwise put the value of ValueIfFalse in the cell.
Let's look at an easy-to-understand example.
Column A contains student names. Column B contains student grades. In column C we will add a formula that will tell if the student passed or failed.
Here's the formula we would use: =IF(B2<50,"failed",>
If the grade in cell B2 is less than 50, put the word Failed in cell C2, otherwise put the word Passed in cell C2.
This is just a basic explanation of the IF function. IF is a very powerful function that every Excel user should have at their disposal. In future tips we will look further at some other more advanced uses of the IF function.
Round Numbers To The Nearest 1000
Often in Excel, when working with large numbers it is common practice to show numbers as thousands, with a note to that effect shown somewhere on the report. To display your numbers to the nearest thousand in Excel you do not need to change any numbers or do any conversions. You simply change the way the number is displayed by creating a custom number format.
- Select the cells containing the numbers you want to display as thousands.
- Select Format, Cells, and click the Number tab.
- In the Category box select Custom
- In the Type box enter
and click OK
The number 5,250,760.95 will now display as 5,251.
Always remember that formatting a number has no effect on its underlying value.
One issue you may have with this practice is that sometimes your totals may not appear to add up. In the above example, if we have three cells with amounts of 5,250,760.95 and total them, the total will show 15,752. This total appears to be wrong, but the fact is that the underlying values of those three cells add to 15,752,282.85, which when rounded to the nearest thousand is 15,752.
If this is a problem for you, you will either have to re-enter the values in your worksheet as thousands (i.e. 5,251 instead of 5,250,760.95) or may get away with using the Round function in some of your formulas. =ROUND(formula,-3)
Creating A Custom Number Format
Excel offers many different predefined number formats that you can apply to your data. However, Excel doesn't always have exactly what you need.
For example, if you were an automotive parts dealer and your parts are assigned inventory numbers such as 5-656-5987, typing a lot of these part numbers can get very tedious, especially with inserting the dashes.
What you need here is a custom number format.
- Select the range of cells that will contain the part numbers.
- Choose Format, Cells and click the Number tab.
- Under Category, select Custom
- In the Type box type #-###-####.
- Click OK
Now, in one of the cells that you just applied the custom number format to, type 12345678 and Excel will display 1-234-5678.
Don't worry about all the funny looking symbols you see in the Custom Number Format dialog box. Just remember that the # symbol is a placeholder for the formatted digits of your number. If you want you number to contain leading zeros for part numbers that aren't 8 digits long (as in this example), use 0s in the custom number format instead of the #s (i.e. 0-000-0000). Then, if you enter a number such as 123 it will display as 0-000-0123.
In a future newsletter I will go into more depth explaining Custom Number Formatting.
How To Create Cumulative Totals
What's the best way to create a column that stores cumulative totals?
Let's say you have a data table. In Column A you list the days of the month, 1 to 31. In Column B you enter your daily totals (i.e. sales, rainfall, etc...). Assuming Row 1 is used for column headings, your daily totals will be entered in cells B2:B32. You want to keep a running month-to-date total in column C.
Here is a really easy solution that you can use:
Using the scenario above, you enter =SUM($B$2:B2) in cell C2, and copy that formula down column C. Note the dollar signs in the first cell reference in the formula ($B$2) and no dollar signs in the second cell reference (B2). The dollar signs indicate an "absolute reference". This means that when you copy this formula down column C, the reference to $B$2 won't change. The second cell reference to B2 doesn't have dollar signs, so that means it is a "relative reference" and will adjust as it is copied. When the formula is copied to cell C3, the formula becomes = SUM($B$2:B3) and the result will be the sum of the range B2:B3. As you enter values in column B, the formulas in column C will show the cumulative totals.
After you have copied these formulas down column C, you will notice that the cumulative totals in the rows below the last value in column B all show the same cumulative total. To avoid showing cumulative totals for the rows that have no value entered in column B, you will need to modify the formula I just gave you. Go back to cell C2 and enter this formula instead and copy it down column C as far as C32.
The formula in cell C2 will be =IF(B2="","",SUM($B$2:B2))
This says, if cell B2 is blank then don't put anything in cell C2, otherwise add a cumulative total formula. Now, as you enter the amounts in Column B for each day, the cumulative total will appear next to each amount.
Learn To Function Smarter
There are formulas and then there are functions. Excel's help menu defines functions as "... predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure."
All of us are familiar with the Excel's Sum function because it is the one most commonly used. What most people don't realize is that there are hundreds and hundreds of other very useful functions right at your finger tips - another of Excel's little known secrets.
You probably won't have much use for many of these functions, but I'm sure you will find many to be very helpful in your daily use of Excel.
Take some time to explore Excel's vast collection of useful and time-saving functions. Click on the Insert menu and select Function. The functions are grouped by Category on the left. Click a category and the related functions will be displayed on the right. As you click on the function name on the right, a brief description of the function will be displayed below. To try out the function, click the OK button. As you click in each of the 'argument' fields, a brief explanation of that argument will be shown.
Some of the functions that I use regularly, are DAY, MONTH, YEAR, ABS, ROUND, SUBTOTAL, SUMIF, SUMPRODUCT, COUNT, COUNTIF, VLOOKUP, INDEX, MATCH, OFFSET, LEFT, MID, RIGHT, LOWER, UPPER, PROPER, TEXT, TRIM.
Change The Format Of Excel's Comments
In order to change the format of an individual comment in Excel, you format it the same as if it was a text box.
- Right click on the cell containing the comment.
- Select Show Comment.
- Right-click on the border of the comment and select Format Comment.
- Make formatting changes the same as with any text box in Excel.
- Right-click on the border of the comment and select Hide Comment.
You can also change the default format for all comments in Excel. However, it has to be done from outside of Excel. Be aware that to change the default format for Excel comments, you will also be changing the default format for all tool tips (those little notes that popup every time you pause the cursor over a tool button) in Windows.
Here's what you need to do:
- Go to your Windows Desktop (Windows key + M).
- Right click on the desktop and choose Properties from the popup menu.
- Click the Appearance tab.
- From the Item dropdown list, select ToolTip.
- You can now choose a new background color, font face, font color, font size, bold, or italics.
- Click OK to accept the changes.
Note that these changes will apply to across all Windows' ToolTips as well as Excel's comments.
An Alternative To Cell Comments
Cell comments (covered in an earlier tip) are great for displaying messages on your worksheets as reminders for yourself and others. You just hover the mouse over the cell containing the comment for a moment and the comment will pop up. But if you move to that cell using the arrow keys, the comment won't display.
Here is an alternative that you can use to display a message when a cell is selected with either the mouse or the keyboard.
- Select the cell where you want to see the message.
- Click on the Data menu and select Validation.
- Select the Input Message tab.
- Put a check mark in the 'Show input message when cell is selected' field.
- Type a heading for your message in the Title field. This text will appear in bold at the top of your message.
- Enter your message in the 'Input message' field.
- Click OK
Now, anytime you move to that cell, the message will pop up. If you develop workbooks for other users this is great tool to use to help them know what to do.
Calculate The Number Of Days, Months Or Years Between Two Dates
Have you ever needed to determine the number of days, months or years between two dates? Calculating the number of days using Excel is pretty simple. Just use a formula to subtract the later date from the earlier date.
For example, if cell A1 contains 1-Jan-2004 and cell A2 contains 03-Mar-2004, you simply enter the formula =A2-A1 in cell A3 to get the number of days. At first the result may look strange. That's because Excel assumed you were entering another date and automatically formatted cell A3 as a date. To fix that, go to the Format menu, select Cells, click the Number tab and select General from the Category list. Your answer should be 62.
However, calculating the number of months or years between two dates isn't so obvious. There's a Function in Excel that makes this task easy but for some reason Microsoft has hidden it away. You won't find it in the Paste Functions list (Insert, Functions). The function is called DATEDIF (i.e. date difference).
Let's use the same dates as above. The syntax for the function is
=DATEDIF(startdate,enddate,"interval").
Our formula to calculate the number of complete months between the two dates would be
=DATEDIF(A1,A2,"m").
Similarly, the formula to calculate the number of complete years is =DATEDIF(A1,A2,"y"), although in our example it would yield 0 complete years. Change cell A1 to a date a year or more earlier and you'll see the result.
A couple of things to keep in mind about the DATEDIF function:
- The start date must be less than or equal to the end date, otherwise it will give an error.
- Acceptable interval codes are "d", "m", "y", "ym", "yd", "md" (with quotes)
- It may appear obvious what the "ym", "yd", and "md" interval codes do but they require a second look. The "ym" interval code yields the number of months between the two dates as if they were in the same year and ignores the year. The "yd", and "md" interval codes yields the number of days between the two dates as if they were in the same year and ignores the year.
To calculate the number of years, months and days between two dates (more than a year apart) you can use this formula (assuming your start date is in cell A1 and your end date is in cell A2).
=DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"
Spice Up Your Worksheet With A Picture Or Two
Excel is not just for numbers. Did you know that you can add pictures to your Excel workbooks? It's easy!
- Select Insert, Picture, From File...
- Browse to the folder containing the picture you want to insert.
- Select the picture file and click the Insert button.
Wasn't that easy?
To move your picture just click and drag it.
To resize the picture, move the mouse over one of the corners of the picture and the cursor will change to a two-headed diagonal arrow. Hold down the left mouse button and drag it to the size you want.
AutoFit Columns Based On Selected Cells Only
You may already know how to easily adjust column widths based on the contents of the column. Just double-click on the border between two column headings and the column will automatically adjust its width based on the widest entry in that column.
However, often you will find that you also have a report heading occupying a cell in the same column you want to adjust. The problem is, when you double-click, the column adjusts to fit the report heading which is usually much wider than the data in the cells below. Most users will then resort to manually adjusting the column width by dragging the border of the column heading.
What most Excel users are not aware of is that there is a way to adjust column widths to fit your data but ignore data in other cells in that same column. It's called AutoFit Selection.
1) Select just the cells you want to base the column width on. 2) From the Format menu select Column, AutoFit Selection
The column width will adjust to fit the widest entry in the selected cells only. Note also that this tip applies similarly to row heights.
(Extra Tip) How To Use Named Ranges
Another underused feature of Excel is Named Ranges. Named Ranges allow you to assign a meaningful name to a cell or range of cells and then use that name instead of the actual cell reference in your formulas. A name is easier to remember when you are constructing your formulas.
For example, assume you have a workbook that contains a lot of formulas that refer to a tax rate. You could simplify things by using one cell to store the tax rate, naming that cell 'TaxRate' and then, instead of using a cell reference in your formulas, you would use the name 'TaxRate'.
Try this:
- Enter 45% (tax rate) in cell B5.
- To name the cell, select Insert, Name, Define.
- Type 'TaxRate' and click OK.
- Now, in cells C10:C15 enter some numbers.
- In cell D10 enter the formula =C10*TaxRate and copy it down to cells D11:D15.
Using Named ranges helps you to create easier to understand and more organized workbooks. Now whenever you need to change the tax rate, you just change it in one cell and all dependent formulas are instantly updated.
How To Use Excel's AutoFilter
Excel has some great tools that allow you to do amazing things with your data. The sad thing is that most Excel users spend years working with Excel without ever knowing they even exist.
One such tool is Excel's AutoFilter. If you work with lists of data in Excel (and who doesn't?), AutoFilter is essential.
I'll cover the basics here.
First you must have a list. The first row must contain headings. The list can be any number of columns wide and any number of rows high. There should be no blank rows in the list. There should be a blank column to the right and a blank row to the bottom of the list. If the list doesn't start in row 1 there must be a blank row above the headings. If the list doesn't start in column A, there should be a blank column to the left of the list.
To get started you need to turn on AutoFilter. Select a cell in your list and from the Data menu select Filter, AutoFilter. You will see a dropdown arrow appear beside each column heading in your list.
Selecting either dropdown arrow will present a list you can choose from to filter your data to show just the rows that match the criteria you select. All other rows are temporarily hidden. You can further filter your list by selecting another dropdown arrow.
Notice that blue dropdown arrows identify the columns you have applied filters to and blue row numbers identify the rows that meet the filter criteria..
To remove the filter applied to a column, click on that dropdown arrow and select (All) from the list.
To remove the filters applied to all columns, from the Data menu select Filter, Show All.
If you want to do further work with your filtered data, you can copy and paste it somewhere else.
To calculate amounts for your filtered data, add a SUBTOTAL formula two rows below your list in the column you wish to calculate. Do this before applying any filters. Your subtotal formula will look like =SUBTOTAL(9,D1:D50) where D1 is the first row in your list and D50 is the last row in your list. Now, when you apply filters to your list, the subtotal formula will automatically calculate the total for only those records matching your filter criteria.
(Bonus Excel Addict Trick: For ad hoc analysis of your data, instead of putting the subtotal formula below your data, insert a couple of rows above your data and add the subtotal formula there. Then you won't have to scroll all the way to the bottom after applying each filter.)
This is powerful!
To remove the filters applied to all columns and turn off AutoFilter, from the Data menu select Filter, AutoFilter.
Display Values With Dollars And Cents Symbols
There may be times when you want to show a cents symbol (¢) for values that are less than $1.00 and a dollar symbol($) for values that are a dollar or more. To do this you need to use a "conditional number format" (i.e. a number format that depends on the condition). If the condition is that the value in the cell is less than a dollar, the cents symbol will be displayed (i.e. .87¢) otherwise, the dollar symbol will be displayed.
To create this conditional number format...
- Select the range of cells you want to apply this format to.
- Select Format, Cells, and click the Number tab.
- In the Category field, select Custom.
- In the Type field, enter [<1].00¢;$0.00_¢
Note: You can copy this format directly from this web page or, if you enter it yourself, remember that to get the cents symbol you need to hold down the ALT key and enter 155 (or 0162),then release the ALT key. - Click OK.
Caveat: Technically .87¢ is not 87 cents, it's 87 one hundredths of a cent. So use this with caution. No, you can't get rid of the decimal - I tried it.
Print The Same Cell Range On Multiple Excel Worksheets
As you know, an Excel workbook can contain many individual worksheets. Often you will find that some of your workbooks are made up of multiple worksheets that are identical in every way except for the input data. For example, you may have twelve monthly sheets plus a summary sheet.
You probably already know that you can print multiple sheets by holding down the CTRL key and selecting each sheet tab you want to print (or click the first sheet tab, hold down the SHIFT key and click on the last sheet tab). Sometimes you may want to print just a small identical selection from each sheet.
Here's how:
- Select the first worksheet you want to print.
- Select the range of cells that you want to print.
- Hold down the CTRL key and select each of the other sheets tabs you want to print. If the sheets are all together, you can click the first sheet tab, hold down the SHIFT key and click on the last sheet tab.
- Select File, Print, and click the Selection option in the 'Print What' section. At this point you may want to click the Preview button to make sure you've got it right.
- Click OK to print.
How To Center Numbers And Still Keep Decimals Aligned
Do you find yourself sometimes wanting to center your numbers in a column but when you do the decimals are not lined up? Here's a tip that I use anytime I need to perform such a task. The numbers aren't exactly centered but it gives the illusion that they are.
Here's how:
- Select the cells you want to center.
- Select Format, Cells and click on the Number tab.
- In the Category field, select Custom.
- If the selected cells already have a number format, that format code will appear in the Type field. Otherwise, the word 'General' will appear and you'll either have to select a format from the list of formats or enter one yourself.
- Once you have the desired number format in the Type field, type two or more 'blank spaces' following the code. NOTE: If the number format is a two-part format $#,##0_);($#,##0), you'll have to add spaces after each section $#,##0_) ;($#,##0) .
- Click OK to see if the format gives you the desired result. Add extra spaces to adjust.
How To Password Protect A Workbook
You may have workbooks with sensitive information that you don't want others to see. Excel allows you to save a workbook so that a password must be used to open or modify it.
- Excel 97: Click File, Save As. In the Save As dialog box click the Options button.
Excel 2000 / Excel 2002: Click File, Save As. In the Save As dialog box select Tools (from the toolbar) and General Options.
- Type a password in the 'Password to open' box to prevent others from opening the workbook without entering the password.
Type a password in the 'Password to modify' box to prevent a user from making changes to the file. The user can still save the file with a different name.
- If you select the 'Read-only recommended' option, the user will be prompted to open the file as read-only unless changes will be made. You can use this option without requiring a password.
Keep in mind that this type of protection will keep most people from accessing your workbook but it is not really secure. If someone wants to access your data they can use one of the many commercial password-breaking software programs available.
Caution: If you assign password protection to a workbook and then forget the password, you cannot open the workbook, access its data through links in another workbook, remove protection from the workbook, or recover data from the workbook. Keep a list of passwords and corresponding workbooks in a safe place.
Tabbing Through Input Cells
Do you create worksheets for other users to input information?
Wouldn't it be cool if you could set up a worksheet so that when they enter information in one input cell they could quickly jump to the next input cell?
You can do this fairly easily with Excel.
Basically what you do is unlock all of the input cells and protect the sheet. Then, instead of pressing Enter after you input data, you press the Tab key and you will automatically jump to the next input cell. By default, all cells in a worksheet are locked. Locked cells prevent users from inputting data or making changes to them, however, unless the worksheet is protected locking or unlocking cells has no effect.
Here's how to do it ... it's really pretty straight forward.
- First, select all of the cells you want the user to input data into by holding down the CTRL key while selecting each cell.
- Since all worksheet cells in Excel are locked by default, you need to unlock just the input cells. With the input cells still selected choose Format, Cells, click the Protection tab, uncheck the Locked option and click OK.
- Now you need to protect the sheet by selecting Tools, Protection, Protect Sheet, OK.
One thing to keep in mind when designing your input sheet is that, as the user tabs through the (unprotected) input cells, the active cell moves across the row to the next unlocked cell and then down to the next row containing unlocked cells.
Locking cells doesn't prevent a user from selecting them, only making changes to them. However if you have Excel 2002, you can prevent the user from selecting locked cells by removing the check mark from 'Select locked cells' on the Protection tab in Step 2 above.
Rotate Your Column Headings
Sometimes you may find that you are working with data that requires narrow columns but your headings are wider than will fit in the column? You could try Word Wrap (Format, Cells, Alignment, and put a checkmark in Word Wrap). However, sometimes that may not be what you need.
For example, I have a worksheet to keep track of attendance for my all-star soccer team. I have all the players names listed down the left column and I have the dates of the practices across the top. Because I enter an 'o' to denote each players attendance for each practice, my worksheet requires only narrow columns. However my dates don't fit to the column width.
So, in order to display my dates without having to make my columns too wide, I change the orientation of my headings so that they are on an a 45 degree angle.
Just try it.
- On a new sheet, adjust the width of columns C to H to 5.
- Enter todays date in cell C2 (CTRL+;).
- Drag the fill handle (the small black square on the bottom-right corner of the selection) and drag it across to cell H2.
- Now select Format, Cells, Number, Date, and select the format that matches DD-MMM-YY (or something similar).
- Select Format, Cells, Alignment, enter 45 in the Degrees box and click OK.
- With your heading cells still highlighted, select Format, Cells, and click the Border tab.
- Click on the Outline button, the Inside Button, and click OK.
As you can see, this gives you very nice looking column headings. I bet you'll find lots of uses for that.
Preventing the display of zeros
There may be times when you don't want a report full of zeros. Maybe the report would be easier to read if only non-zero values were shown. It was only last week that someone asked me how to do this.
One option is to select Tools, Options and select the View tab. Then deselect the 'Zero values' option. If you want to hide zero values for only part of your worksheet, the following stpes will do the trick.
To easily format a range of cells to hide zero values:
- Select the range of cells you want to apply the format to.
- Select Format, Cells and click on the Number tab.
- In the Category box, select Custom. The current format (i.e. #,##0.00;-#,##0.00) should be displayed in the Type field. Notice that there's a semi-colon in the format. The format to the left of the semi-colon is for positive numbers. The format to the right of the semi-colon is for negative numbers.
- A little-known trick is that you can add a second semi-colon to the right of the existing format (i.e. #,##0.00;-#,##0.00;). This will force cells containing zero values to display as blanks.
Fast Editing Using Your Keyboard
As I've said many times, Excel power users always learn more than one method to accomplish their everyday tasks in Excel. Sometimes you are in 'keyboard mode' where a keyboard shortcut would be easier. Other times you are in 'mouse mode' where clicking with your mouse is the quickest and best option.
Editing the contents of a cell is a common task in Excel where most users unnecessarily switch from 'keyboard mode' to 'mouse mode' because they don't know the keyboard shortcut. When you want to edit the active cell, simply press F2.
Once you are in edit mode, you can use your arrow keys to move around the formula bar. Pressing the HOME key brings you to the beginning of the formula. Pressing the END key brings you to the end. Holding down the SHIFT key while pressing an arrow key selects text. Holding down the CTRL key while pressing the arrow keys moves the cursor one full word at a time. You can also use a combination of CTRL+SHIFT+arrow key.
These shortcuts also work with many other programs. Just try them and see what you've been missing.
Quickly Convert Text To Lower Case
Have you ever had data in a worksheet where all the text was all uppercase? Then you had to retype it all to get it into lowercase? No more!
Next time here's what you can do:
- Find a blank cell to the right of the cell containing the uppercase text and enter the formula =LOWER(origtext).
- Replace 'origtext' in the formula with the address of the cell that contains the original uppercase text i.e. =LOWER("A1"). Excel will instantly convert it to lowercase.
- Copy the formula down for each cell you want to convert.
- Select all of the cells containing the formulas and click Edit, Copy.
- Select all of the cells containing the original text and click Edit, Paste Special, Values, and OK.
- Go back and delete the cells containing the formulas.
You can also use the UPPER and PROPER worksheet functions to convert text.
NOTE: It's always a good idea to backup your data before making wholesale changes to it like this. Better safe than sorry.
Use Autoshapes To Highlight Information
Try using Excel's AutoShapes for adding notes and highlighting other information on your worksheet.
- Right-click anywhere on your toolbars and select Drawing. The Drawing toolbar will appear.
- Now click on AutoShapes and select a shape from the Callouts option. The cursor will change to cross hairs.
- Click and drag in your worksheet to draw the shape. Type in some text and then use the sizing handles to resize it. (The cartoon bubble shape is really cool.)
Experiment with various types of formatting. To apply formatting to an AutoShape you first need to right-click it's border and select Format AutoShape. To add or edit text in an Autoshape, you click in the middle of the AutoShape.
I've got lots more fun and amazing tips to show you about AutoShapes later. So stay tuned.
Add A New Window For A Better View
Sometimes you may wish that you could see two parts of your workbook at the same time. Who says you can't be in two places at the same time? I have a great tip that will let you do just that.
Let's assume you have a workbook with several sheets and you want to work back and forth between two sheets. It can get a little tedious switching between sheets, especially if you need to do it often. I have a solution that will allow you, not only to see two separate parts of your workbook at the same time, but to easily work between them with virtually no switching back and forth.
Excel allows you to open a new window of your workbook. It may look like another workbook but actually it is just another view of the same workbook. But even working between two windows doesn't help much. The trick is to get both of these windows in your view at the same time.
Here's how:
- Go to the first sheet and range that you want to work with.
- Open a new window of the same workbook by clicking Window, New Window. You will notice in the Title Bar of the window there is a number following the filename indicating which window it is.
- In the second window browse to the other sheet and range that you want to work with.
- Now, to get both of these windows in the same view, select Window, Arrange, select Tiled and put a check mark in 'Windows of active workbook' and click OK.
Now that you've got both working areas in view, you can easily cut, copy, past, move and much more between the windows. What's really cool is that you can easily drag and drop from one window to another. Remember that when dragging and dropping you hold down the CTRL key to copy the selection (notice the small plus sign when you're dragging) otherwise the selection will be moved.
Oh, I almost forgot. When you're finished working with both windows, close the second one (click the Close button on the top right corner of the window) and Maximize the original window.
Now don't just read this tip. Try it. This really is a great time-saver.
Easily Center Your Report Headings
Often when you create a report in Excel you'll want your report heading to be centered across the top of your page. If you're new to Excel you may not yet have discovered how you can do this very easily. Actually, there are a couple of ways.
One way is to merge all the cells across the top of your report into one cell and center align it (Format, Cells, Alignment, Merge cells). One drawback to this method is that merging changes the column and row structure of your spreadsheet which may cause you problems in the future such as selecting a range of cells that intersects with merged cells.
The other method is called Center Across Selection.
- Ensure that the text for your headings is in the left-most column of your report.
- Highlight the cells containing the headings and extend the selected range over to the right-most column.
- Now choose Format, Cells, and select the Alignment tab.
- From the Horizontal drop-down box scroll to the bottom, select Center Across Selection and click OK.
Note that although it appears to be centered on your report, the text for your headings is still in the first column.
Hiding Unused Rows and Columns
As you may or may not know (or even care), an Excel worksheet contains 16,777,216 cells (65,536 columns x 256 rows). Most Excel worksheets use only a very tiny portion of that area. It may be useful to hide the area that you aren't using to keep from scrolling off the edge of your work area.
Here's how:
- Select a cell in a row beneath the work area of your worksheet.
- While holding down the CTRL + SHIFT keys press the down arrow on your keyboard. This will select a range of cells from the cell you selected down to the last row of the worksheet (i.e. row 16,556).
- Select Format, Row, Hide.
- Next, select a cell in a column to the right of the work area of your worksheet.
- While holding down the CTRL + SHIFT keys press the right arrow on your keyboard. This will select a range of cells from the cell you selected over to the last column on the far-right side of your worksheet (i.e. Column IV).
- Select Format, Column, Hide.
You will notice that the cells outside your work area are grayed out. Be aware that if you use the Select All button (the grey rectangle in the upper-left corner of the worksheet where the row and column headings meet) before adjusting your row heights or column widths, this will unhide the previously hidden rows or columns. To adjust the row heights or column widths without unhiding the other rows and columns, select only the visible row or column headings first.
Quickly Delete Blank Rows From A Long List
If you have a long list of data that contains blank rows that you want to delete, you have several options.
- You could select each row, one by one, and delete them individually. If you have dozens or hundreds of rows, this could be time consuming.
- You could sort the rows alphabetically, which would put all the blank rows together, and then delete that group of blank rows. However, there will be times when you may want to maintain the order of your list, so this would not be an option.
- You could filter the list for blanks, select these rows (i.e. visible cells) and delete them.
- You could use a little-known shortcut to quickly select the blanks in your list and delete them.
This week I will cover option number 4 and next week I will cover option number 3. The first two are self-explanitory and usually are not your best choice, so I won't discuss those.
Suppose you receive a list of data that contains blank rows that you want to remove without affecting the order of the list. This technique will allow you to delete all rows that have blank cells in a particular column. Here's the fastest way to do it.
- First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
- Now, select the cells in one column from the top of your list to the bottom.
- Make sure that all the blank cells in this selected range are the rows you want to delete.
- Press the F5 key on your keyboard (or select Edit, Goto).
- Click the Special button.
- Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
- Now choose Edit, Delete, select the Entire Row option and click OK.
If you work with large lists of data in Excel, this tip will save you a lot of time. If you don't work with large lists often, remember this tip anyway, because sooner or later you're going to need this.
How To Use Named Ranges
Another underused feature of Excel is Named Ranges. Named Ranges allow you to assign a meaningful name to a cell or range of cells and then use that name instead of the actual cell reference in your formulas. A name is easier to remember when you are constructing your formulas.
For example, assume you have a workbook that contains a lot of formulas that contain a tax rate. You could simplify things by using one cell to store the tax rate, naming that cell 'Rate' and then, instead of using the cell reference in your formulas, you would use the name 'Rate'.
Try this:
- Enter 45% (tax rate) in cell B5.
- To name the cell, select Insert, Name, Define.
- Type 'Rate' and click OK.
- Now, in cells C10:C15 enter some numbers.
- In cell D10 enter the formula =C10*Rate and copy it down to cells D11:D15.
Using Named ranges help you to create easier to understand and well organized workbooks. Now whenever you need to change the rate, you just change it in one cell and all dependent formulas are instantly updated.
How To Create Vertical Titles For Your Reports
Did you know that you can create vertical titles in Excel that span multiple rows? For example, you may have a table and you want a vertical title to go down the left side.
- Enter your title in the top cell of the range your title will span (i.e. B5).
- Select the cells you want your title to span (i.e. B5:B15).
- From the Format menu select Cells and then click on the Alignment tab.
- On the right side of the dialog box enter 90 in the Degrees box.
- From the Horizontal: dropdown select Center.
- From the Vertical: dropdown select Center.
- Select Merge Cells and click OK.
How To Find and Replace Wildcard Characters (* ?)
If you use Excel a lot, one day you are going to need to do a Find and Replace on text that includes a wildcard character. If you've already come across this situation in the past, you probably remember that it wasn't a simple, straight-forward task. You may have even given up in frustration and did the Find and Replace manually.
The problem is, Excel (and many other programs) use these characters as wildcards to aid in searching. On the rare occasion that you find yourself in this situation, simply precede the wildcard character with a tilde (~). So for example, if you need to replace all asterisks you would use ~* (tilde + asterisk) in your search.
Email That Excel Workbook Fast
You've just finsihed updating a workbook and you need to email it to a collegue or client in a hurry.
You can do it the slow time-wasting way:
- Save the workbook
- Close the workbook
- Open your email program
- Start a new message
- Browse for and attach the workbook file to your email
- Enter the recipients email address
- Click Send
Or the Excel Addict way:
- Save the workbook but don't close it
- Click File, Send To, Mail Recipient (As Attachment)
- Enter the recipient's email address
- Click Send
How To Use Fractions In Excel
Bet you thought you couldn't use fractions in Excel. Well, as a matter of fact, Excel does do fractions, but at first you may find it a little tricky entering them.
If you enter 1/2 in a cell, Excel will interpret this as a date and automatically format the cell as a date.
To enter a fraction that Excel can recognize, you have to precede your fraction with a zero and a space, for example 0 1/2. Excel will now recognize this entry as a fraction, store it's decimal equivalent in the cell and format it to display as a Fraction.
How To Paste A Hyperlink
In a previous tip I showed you how to create hyperlinks using Insert, Hyperlinks.
Well, like so many other things in Windows, there is yet another way to create hyperlinks. Here's how you can paste a hyperlink.
- Select the destination cell you want the hyperlink to link to.
- From the Edit menu choose Copy.
- Next, select the cell where you want to place the Hyperlink.
- Now, hold down the Shift key, select Edit, Paste Hyperlink.
Keep Those Headings Visible
When you are working with a worksheet with many rows and columns and you want to keep your row of column headings always visible on the screen as you scroll down through the rows, here's what to do.
- Click the entire row directly below your row of headings.
- Select Window, Freeze Panes.
If you want to keep the left-hand column of Row Labels visible instead.
- Select the entire column immediately to the right of your labels.
- Select Windows, Freeze Panes.
Now, let's assume you have column headings in Row 1 and row headings in Column 1 and you want to keep both the row and column labels visible as you scroll down and to the right.
- Select the cell immediately below and to the right of your labels (in this example cell B2).
- Select Windows, Freeze Panes.
Now, scroll to the right and scroll down and see what happens.
How To Combine Text in Cells
Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell.
I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature allows me to easily combine the last name from one column with the first name of the other column and show the result as a full name in another column.
Let me show you how easy it is.
Before I get started, when talking about text I'll often use the terms ‘string of text’ or ‘text string’ to refer to text.
The best way for me to show you how this works is to give you a simple example to do yourself.
- In cell A1 enter "First" (without the quotes)
- In cell A2 enter "Mickey"
- In cell A3 enter "Ozzy"
- In cell A4 enter "Bart"
- In cell B1 enter "Last"
- In cell B2 enter "Mouse"
- In cell B3 enter "Osbourne"
- In cell B4 enter "Simpson"
Lets pretend that this is a spreadsheet with a list of 300 names and you need the full name in one cell. How would you go about doing this? It could take hours to retype hundreds of names.
Here’s the trick.
In cell C1 enter "Full Name" as your column heading.
In cell C2 enter either of the following formulas:
=A2 & " " & B2 (will give you "Mickey Mouse")
=B2 & ", " & A2 (will result in "Mouse, Mickey")
Now just copy the formula down for the remaining cells. At this point you'll want to convert the formulas in these cells into values. To accomplish this, just highlight all of the formulas in column C, click Edit, Copy, and then click Edit, Paste Special, Values, OK. Now, if you want, you can delete the original two columns of data.
NOTES: To insert a space between the first and last names we have added a space between two quotation marks. The ampersand (&) joins the text string from each cell into one string of text.
Copy Column Widths To Another Sheet
Anytime you create a new worksheet and find that you need column widths the same as those in an existing sheet, there is an easy way to duplicate them.
- Select a range of cells from the original worksheet that include the columns you want to copy
- Choose Edit, Copy.
- Switch to the new sheet and click in any cell in the column where you want the copied widths to begin.
- Now click Edit, Paste Special, choose the Column Widths option and click OK.
Now, isn't that much easier?
How To Create A Hyperlink To Another Workbook
Everyone who has surfed the Internet knows the benefits of hyperlinks.
Hyperlinks on a web page are the text or graphics that you can click on to give you instant access to other information no matter where it is located.
Did you know that you can create hyperlinks in Excel? You can create hyperlinks in one workbook that will allow you to instantly open another workbook without having to remember where it is located.
Here's what you do to create a hyperlink to another Excel workbook.
- Select a cell where you want to place the hyperlink;
- Type a description of the workbook you want to link to;
- Right click on that cell and choose Hyperlink...;
- Click the File... button and browse to the file location;
- Select the file you want to link to and click OK;
- If you want to go to a specific sheet in the workbook, click on the Bookmark... button, select the sheet name and click OK.
- You can also click the Screen Tip... button to add a description to your hyperlink that will pop up when a user holds the mouse over the hyperlink.
- Click OK to close the Insert Hyperlink dialog box and save your workbook.
- Try out the hyperlink to see how it works.
Hyperlinks are very convenient in Excel for giving users quick access to related files. I use them to create a Favorites sheet for all my frequently used workbooks and webpages. I'll tell you about how to do that in an upcoming newsletter.
How To Print Your Report Headings On Every Page
If your report contains more data than will print on one page you will probably want your report headings to print on the top of every page.
Here's what you do.
- Choose File, Page Setup, and then click on the Sheet tab
- Click in the 'Rows to repeat at top' box and click and drag the cursor through the rows that contain your headings
- You can click OK at this point, or you can choose the Print Preview button to see if everything looks OK
- On the Print Previw screen, click the Next button to see that your report headings will also print on page two
- If everything looks OK, click the Close button.
How To Easily Duplicate Cell Formatting
Often it takes several steps to format a cell or a range of cells to get just the right look (i.e. fonts, borders, color, etc..)
When you want to apply this same format to another cell or range of cells you could copy the cell(s) and then click Edit, Paste Special, Formats. But there's a much easier and faster way. It's called the Format Painter. Look on your toolbars and you should see a button with a picture of a paintbrush on it.
Here's what you do to apply the format from one range of cells to another:
- Select the first cell or range of cells.
- Click on the Format Painter button.
- Select the cell(s) you want this format applied to.
Voila. It's like magic.
If you select only one cell in the first step, the format of that cell will be copied to the cell(s) you select in Step 3. If you select a range (more than one) cell in Step 1, the format from that range will be copied to a range of the same size using the cell you select in Step 3 as the top left corner of the range.
There are a couple of other features of the Format Painter that you'll want to know about. In the above example, the Format Painter copied the formats once and then turned off.
If you want to paint a format to more than one range of cells you will need to double-click on the Format Painter button in the first step. Then select each range of cells you want the format applied to. When you are finished painting, simply click the FormatPainter button again (or press the ESC key) to turn it off.
This feature can be a major time-saver when designing your reports.
Insert Time and Date Stamps In Your Worksheet
Anytime you need to insert the current time or date into your spreadsheet here's the quickest way to do it.
- For the current date press CTRL+semicolon.
- For the current time press CTRL+SHIFT+semicolon.
- For both current date and time in a cell press CTRL+semicolon, then press the SPACEBAR, then CTRL+SHIFT+semicolon, then ENTER.
How To Display Text In A Cell On Multiple Lines
Did you know you can create a line break within a cell to give you multiple-line headings in the same cell?
Sometimes the text for your column heading is wider than the data in the column. For example, a column heading called Monthly Expenses would require a much wider column than the numeric data below it, resulting in a lot of wasted space.
Rather than having a column that is too wide just to accommodate your heading, you can split your heading into multiple lines within a single cell.
There are two ways to do it. Here's one option:
- Select the cell where you want the label or heading to appear;
- Type the first line of information;
- Press ALT+ENTER;
- Type the second line;
- Repeat step 3 if you have additional lines to enter.
Using the above method, Excel will automatically wrap the text and adjust the row height for each cell in which you use ALT+ENTER.
Another way to wrap text in a cell, is to format the cells so that Excel will automatically wrap them based on the column width.
Here's how:
- Select the cells that you would like to apply a Wrapped Text format to;
- From the Format menu select Cells and then click the Alignment tab;
- Put a check mark in the Wrap Text option and click OK.
Now whenever you enter text in any of these cells that is too wide to fit in the column, Excel will automatically wrap the text to a new line based on the column width and adjust the row height.
The advantage of the first method is that you can force the line break wherever you want. The advantage of the second method is that you don't have to use ALT+ENTER. You can have the best of both worlds by using the second method and then, in cells where the text doesn't break where you want, you can use ALT+ENTER.
