Excel Zoom

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”.

Do you want one central location where you can easily navigate to any worksheet in your file, and then navigate back with one click? This macro creates an index that lists all sheets in your workbook. The best part is that the index excel macro updates itself everytime you select the index sheet.

If you need an index sheet in your file, you probably already have a zillion worksheets in your file, but add one more, and call it “Index”, or whatever you want to identify it as an index (table of contents, etc.). Next, right click the Index tab and select ‘View Code’. This is where you will enter the code below. Click on another sheet in your file, then click back on your Index sheet. You’ll notice that it has populated a list of all the sheets in your file, complete with a convenient link to them. In all your other sheets, cell A1 will have a “Back to Index” link for easy navigation. If you want to use another cell for this backwards navigation, change the code in both places where it says A1 to whatever cell you’d like.

Need help? Use our nifty guide to help figure out how to install and use your macros.


Private Sub Worksheet_Activate()
'
'MACROS BY EXCELZOOM.COM
'
Dim wSheet As Worksheet

Dim l As Long

l = 1

End Sub


Often times you’ll find yourself working on a file in Excel that needs to be sent to a co-worker as an attachment. If you store your files on a shared network drive, it could take a little while to find your file, even if you know exactly where it is located. Even then, you may have to worry about attaching the wrong file if there are several saved with similar file names.

By using the macro below, you’ll be able to attach the file you have open to an email with the click of a button.

Copy all the code below. Paste it into your PERSONAL.XLS workbook’s Visual Basic editor, under a Module. The following steps also need to be completed to use this macro:

• Change “someone@example.com” to the email address of the person you want to send your file to
• The line “This Email was sent on ” & Format(Date, “mmmm dd, yyyy”) is the subject line. Change it to whatever you would like your subject to say. This one would read “This Email was sent on March 04, 2009″ assuming today was March 04, 2009.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub email_active_workbook()
'
'MACROS BY EXCELZOOM.COM
ActiveWorkbook.SendMail "someone@example.com", _
"This Email was sent on " & Format(Date, "mmmm dd, yyyy")
End Sub

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.

Often times, you’ll do work in one Excel file, that would be useful to show in another Excel file. You can simply copy and paste the data in, but if the data is dynamic (i.e. changes often), you might want to link between the two files.

You can link to a cell in another file just as easily as you can link to a cell in the same workbook, the only difference is that you need to specify the file location of the workbook you are trying to link to.

To explain, we’ll start by showing how to link to another cell in the same worksheet, to another cell in a different worksheet but in the same file, and then to another cell in a different file. Note: in all examples below, we’ll assume that you want to link to cell B1 in whatever worksheet you’re referencing.

First, to link to another cell in the same worksheet, type “=B1″ (without the quotes ” “). This will produce whatever is in cell B1 in the current worksheet.

To link to another worksheet (say, Sheet2) in the same file, type “=Sheet2!B1″ (without the quotes ” “). This will produce whatever is in cell B1 in the worksheet named “Sheet2″.

To link to another worksheet in another file (assuming the other worksheet is called Sheet1, and is stored on your desktop, with a file name Book1.xls), type “=’C:Users**YOUR_USERNAME**Desktop[Book1.xls]Sheet1′!$B$1″ (without the quotes ” “). This will produce whatever is in cell B1 in the worksheet named “Sheet1″ stored in the file Book1.xls located on your desktop. Note: Replace **YOUR_USERNAME** with your computer’s logon user name.

Also, if you have the two files open, you can type “=” (without the quotes ” “) into your worksheet and then use your mouse to click on the cell you wish to link to. Excel will automatically fill in the appropriate information.