Excel Zoom

Sometimes, it’s useful to be able to remove personal information from your workbook, in the event that you’re sending the file to someone else, whether it’s someone from another company, posting it to a forum, or sending it via email.

Excel will, by default, save information such as the author, file title, comments, etc. This information, could be potentially damaging if it was put in the wrong hands.

Luckily, Excel 2007 makes it easy to remove this information. Click the Office Button | Prepare | Inspect Document. Check all the options on the menu that appears, and click Inspect. Excel will then check each category to see if there is any document information that can be removed and will show a Remove All button next to each that has any information. Click the button, and the information is removed.

Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.

It’s also useful to be able to add comments to a formula, so that you can let other users of your workbook know if you’ve made a change to the formula, let them know what the result of the formula represents, or share other information about the formula.

After entering your formula, type +N(“Your comment here”)

For example if you have a formula that produces a class average, you can type:
=AVERAGE(A1:A25)+N(“Period 1 Class Average”)

 ”For” loop is used in a macro whenever you have an action that needs to be performed a set number of times. For example, if you want to change the fill color of the first 20 cells in a list, you could have the macro select the range and simply fill as desired. But what if you wanted to only fill the cells if they were blank, for example? Telling the macro to select the whole range and fill it wouldn’t work.

This is where a “For” loop is useful. You can have the macro to go down your list, and determine if the cell is blank. If it is, then have the macro fill the cell with a color, otherwise go to the next cell, until it has run through the whole list.

The following code will do exactly what was mentioned above.

Sub FLoop()
'MACROS BY EXCELZOOM.COM
For x = 1 To 20
Cells(x, 1).Select
If Selection.Value = "" Then
With Selection.Interior
.Color = 65535
End With
End If
Next x
End Sub

• The first line identifies the range of rows to be evaluated. It also tells the macro, that if ‘x’ is less than 20, keep on going.
• The next line tells the macro to select the cell in column 1.
• The ‘If’ section determines if the selected cell is blank, and if it is, it will fill it with a color (in this case 65535=yellow), and if it isn’t blank then just move on and evaluate the rest of the cells until the end of the range.

Just to have a little fun with the new Twitter account, I thought I’d put together a little Excel tool to make fitting into the 140 character limit a little easier.

Simply type what you want to say, and if it exceeds the 140 character limit, it will append “…” to the end, so that the last “.” will be the 140th character. That way you’ll know how your post will look before putting it online.

To make this more than just a formula that chops off anything in excess of 140 characters, also included is a macro that will open up your Twitter home page and copy the text that is already pre-formatted to 140 characters or less. Now all you’ll have to do is paste the text into the “What are you doing?” box and click “Update”.

Download the file here.

You can apply conditional formatting to your sheet that will allow you to format every other row however you specify.

Select the area where you want to highlight alternating rows.

Go to the Format menu and select Conditional Formatting.

For Condition 1, select “Formula Is”, and type in “=MOD(ROW(),2)=0″ if you want to highlight every even row, or type “=MOD(ROW(),2)=1″ if you want to highlight every odd row.

Click on the Format button, and choose the format you want your highlighted rows to have (i.e. font, color, background, patterns, etc.).

Click OK, and you should have every other row highlighted.

If you have a list with cells of all different colors, you can apply a filter to those cells to have Excel filter them by color, and then apply a formula to count how many of those cells are visible.

First, select your list. Right click and choose Filter | Filter by Selected Cells Color. You can then select the filter drop down in your list’s first cell and choose which color to filter. Next, in a row that is outside your list, type in the following formula: =SUBTOTAL(103,A1:A100), where A1:A100 are the cells in your list. Whenever you filter the list, this formula will tell you how many cells are not hidden.

You can remove the filter at anytime, by choosing the Data tab, and clicking the Filter button, and the formula will give you the count of your whole list (i.e. 100 in the example of A1:A100).

Using the fill handle is easy. Simply start typing a list of consecutive numbers, dates, etc. and drag the rest. You can easily fill 100 cells in no time at all. Well what if you wanted to fill the alphabet down a list of cells? Unfortunately, Excel doesn’t have this functionality built in. If you were to start typing the alphabet down column A, so that A1=A, A2=B, A3=C, and then drag the rest, you would only get A, B, C repeated as far as you dragged the fill handle.

There are two ways of making this work. The first is through a formula, and the second is by using a custom list.

To get the alphabet with a formula, type the number 65 in cell A1, and drag down to cell A26. Now column A should have the numbers 65 through 90. In cell B1, type =CHAR(A1), and drag this down to cell B26. Column B should have the capital letters A through Z. If you want lowercase, simply start with 97 and fill to 122 in column A.

Alternatively, you could manually type your list A through Z in cells A1:A26, then select that range (A1:A26). Click Tools | Options | Custom Lists (or in Excel 2007 Office Button | Excel Options | Popular | Custom Lists). On the bottom of the menu, click Import, where it says $A$1:$A$26. You’ll see A through Z appear under custom lists. Now whenever you type A, then drag down it will fill through the letter Z then loop back to A and start all over again as far as you drag.

In large worksheets, it is especially useful to be able to view a cell that contains summary data, such as totals. This can become tedious if your totals are at the end of your worksheet, while the data you’re editing is at the beginning, causing you to have to scroll down to be able to see the totals.

If you use Excel’s Watch Window, you can keep your worksheet’s summary data in view while no matter where you are editing within the worksheet.

To do this in Excel 2002 or 2003, right click the cell you want to watch, and select “Add Watch” from the list. The window that appears will give you information about the cell, such as the workbook and worksheet it is located in, it’s cell reference, the value in the cell as well as the formula that is in the cell. If you wish to watch several cells, simply repeat the above steps for each cell you wish to watch. You then can drag this window to any edge of the spreadsheet to “dock” it in place, as you would any other toolbar.

If you’re using Excel 2007, you can still do this, except you can’t simply right click the cell as you would in 2002 or 2003. In Excel 2007, select the cells you wish to watch, go to the Formula tab on the ribbon and select Watch Window. In the Watch Window, click “Add Watch”.

Deleting a cell from the Watch Window is just as easy. Simply select the cell from the Watch Window and click “Delete Watch”.

Note: To work along with this tip, you can download the PivotTable Example file. The download file does not contain the PivotTables referred to below, it simply contains the source data so you can follow the steps to create the same PivotTables.

PivotTables are a useful way to quickly summarize data and display it in an easy to read format. Their biggest problem is that not everyone knows how to use them. Once you learn how to use PivotTables, though you’ll use them all the time.

In the PivotTable Example file, you’ll notice a list of salespeople, who have sold various widgets throughout the year, and for all different dollar amounts. You want a summary of sales by salesperson, location, date, and product.

First you need to get the data into a PivotTable.

• Click somewhere within the data, and click Data | PivotTable and PivotChart Report. If you’re using Excel 2007, click the Insert tab and select PivotTable.
• Ensure the selected range includes A1:F27, and place the PivotTable report in a new worksheet.

Now you have a blank PivotTable, with a list of available fields off on the right hand side of the screen.

We now want to find out which salesperson has sold the most. Drag the Salesperson field to the row area, and the Order Amount to the Data/Values area, depending on which verson of Excel you are using. You should see the following totals:

• Evans – $1,689
• Gordon – $2,008
• Jackson – $595
• Jones – $1,837
• Miller – $1,353
• Nolan – $1,649
• Smith – $3,197
• Snell – $1,131
• Stein – $1,358

To find out who sold the most by quarter, drag the Date field to the Column label area. The grand totals should be as follows:

• QTR 1 – $4,288
• QTR 2 – $3,213
• QTR 3 – $3,939
• QTR 4 – $3,377

To find out which location sold the most, replace the Salesperson field with the Location field in the row area. The grand totals by location should be as follows:

• Mid-West – $1,837
• Northeast – $4,550
• Northwest – $1,726
• Southeast – $3,338
• Southwest – $3,366

Finally, to find out which widget was the best selling one, replace the Location field with the Product field in the row area. The grand totals by product are as follows:

• Widget A – $2,508
• Widget B – $2,492
• Widget C – $1,527
• Widget D – $1,245
• Widget E – $1,561
• Widget F – $1,852
• Widget G – $1,512
• Widget H – $1,447
• Widget R – $673

You can also play around with the location of each field, by adding several fields to the row area, for example, to see which salesperson sold the most of a particular product. Basically, visualize how you want your report to look, and then drag your fields into place in order to get the information you want. Also, instead of summarizing the list based on the “Sum” of the dollar amounts, you can tell the PivotTable to count the number of items. This way you can count how many of Widget B Smith sold (two units).

As you can see a PivotTable can be a powerful tool when trying to summarize large amounts of data. Remember, a PivotTable is just as easy to set up if your data contains 30 or 30,000 rows of data. As long as they are formatted with a header row, and the data is in a consistent format, a PivotTable will have no problems creating useful reports.

If you have a list of data in a column, formatted in a similar manner throughout that you wish to separate into several columns, you can use Excel’s Text to Columns feature to separate this information.

For example, say your list is in column A and it contains a list of customer email addresses. You want to retrieve just the part before the “@”, because that is also their ID in your system. If their email addresses are set up in a similar format throughout (i.e. first, middle, last initials and a number – 4 characters before the @), you can use the Text to Column’s fixed width option. More likely though, your customer’s email addresses are each set up differently (i.e. various number of characters before the @), so in this case, you can use the delimited option, where you specify a character to specify where to separate each field. In this case the character is the “@” symbol.

To do this, select the column with your list, then Data | Text to Columns in pre-Excel 2007 versions. In Excel 2007, select the Data tab and Text to Columns on the ribbon.

Assuming you are using the delimited option, select the Delimited option, and click Next. Under Delimiters, select Other and enter a @ in the box. Make sure no other boxes are checked here. In the Data preview window, you’ll see a line between the email address’ user name and the domain, where the @ symbol would be. Click Next.

You can change the column data format and specify the destination of the data, if you wish. Note: if you don’t want to import one of the columns, click the column so that the information is shaded black with white text, and select the Do not import column (skip) option under Column data format. Click Finish.

If you happen to have data that is all formatted identically, you can use the fixed width option in the first step, and drag a line in the Data preview window so that it separates your data where you want it.