Automate Excel

Sumproduct is handy when you need to lookup and sum data from a list selectively. Here is a good overview.

Tutorial: SUMPRODUCT

Link:
http://www.excelonlinetraining.com/excel-sumproduct-function.html

Did you keep track of time as a number? For instance 30 + 40 and need a way to convert it to time?

1. Divide the number you want to convert to time by 1440
2. Format the cell as time

Let’s take a look at the VLOOKUP function. In our example we have a company with a list of part numbers, along with information about them. Let’s asssume this list is very long, and they would like to enter a part# in a cell and have it return information about that part quickly.

This picture shows our desired result. In the orange cell we enter a part number, then the location and price will automagically display

So how do we accomplish this? Here is the VLOOKUP in layman’s syntax

=VLOOKUP(CellToLookup ,RangeToLookIn, WhichColumnToReturn, ExactMatch?)

And now in practice:

1. We need a list of data sorted by the first column.
2. In a blank cell that we would like to return a result from the list, type =VLOOKUP(
3. Click on the cell where we will enter the value to lookup (this will enter the cell in your formula)
4. Hit , (comma)
4. Click and drag to select the entire list including headers
6. Hit, (comma)
7. Type the numerical value of the column you would like to return (A=1, B=2, C=3,,,,)
8. Hit ,(comma)
9. Type “False” and hit enter

Sidenote: Enter “TRUE” to find an approximate match, one reason you might use approximation is on data with Typos or poor standards, for example “MR Man” and it’s actually “MR. Man”.

Sidenote: This works across multiple worksheets. If you would like to store your data in one sheet, and your VLOOKUP in another, it’s the same process.

Do you need to Forecast some data? This tip will give you the basics of using the Forecast function.

We will use the picture in our example:

1. In a blank cell (B11) type =Forecast(
2. Click on the cell (A11) where the month you want to forecast will be (this will enter the cell in your formula)
3. Hit , (comma)
4. Click and drag the range of Sales to forecast (B3:B7 or known_x’s)
5. Hit, (comma)
6. Click and drag the range of months to forecast (A3:A7 or known_y’s)
7. Hit enter

Sidenote: You can change the forecasted month (A11) to see the Forecast for additional months, or use Autofill.

This formula will tell you the number of cells used in a range

In the cell you want the result:
1. Enter Counta(
2. Select the range you want to query
3. Hit enter

Given MPH and Distance, find the average time.

1. Enter 70 in cell A2
2. Enter 140 in cell B2
3. Enter =(B2/A2)/24 in cell B3

Given Time and MPH, find the average distance in miles.

1. Enter 2:00 in cell A2
2. Enter 70 in cell B2
3. Enter =B2*(A2*24) & ” Miles” in cell B3

Given Hours and Distance in miles, find the average speed.

1. Enter 2:00 in cell A2
2. Enter 140 in cell B2
3. Enter =B2/(A2*24) & ” MPH” in cell B3

A common question in Excel pertains to removing extra spaces from the beginning and end of a cell. Try using the Trim() function.

To do this in the spreadsheet.
1. You need to use a helper cell, or an addtional cell next to the one you want to trim
2. In the helper cell, type =Trim(
3. Click on the cell you want to trim
4. Hit enter

To do this from a macro:
1. Put the following code in a module

Public Sub RemoveSpaces()
Application.ActiveCell = Trim(Application.ActiveCell)
End Sub

2. Click on a cell you want to Trim, and run the macro

Here is the easiest way, a simple use of the ampersand ( & ) sign.

In the formula bar1. Enter an equals(=) sign
2. Click the first text cell to join
3. Enter an ampersand( & ) sign
4. Click the second text cell to join
5. Do this as many time as you like

Sidenote:
If you would like to add a space or additional text between cells, enclose it between quotation marks.

I had a hard time today doing a find for all of the question marks ? in my spreadsheet. This is becuase Excel treats this as a wildcard. To search for wildcards just put a tilde ~ in front of your search.

Here are a few examples:

To find ? in a spreadsheet enter ~? in the find box.

To find ~ in a spreadsheet enter ~~ in the find box.

To find * in a spreadsheet enter ~* in the find box.

Or change column headers from letters to numbers. Old school spreadsheets used numbers for rows and column headers, excel calls this old method R1C1 reference style. Here is how to toggle between the two.

On the main menu
1. Click Tools
2. Click Options
3. Select the General Tab
4. Place or remove a check next to the “R1C1 Reference Style” option to suit your liking and hit OK.

If you aren’t a fan of Excel auto-hiding the menu items you don’t use that often, you can turn this feature off. Here’s how……

On the main menu
1. Click Tools
2. Click Customize
3. Select the Options Tab
4. Put a checkmark next to the “Always show full menus” and click Close

Excel can’t do everything! Here is a good list of exactly where it falls short and how far it goes. Most notably, Excel is limited to 65,536 rows by 256 columns.

Link:
http://www.asap-utilities.com/scripts/tips/tips.php?detail=2001-09-05_999723135.tip

The best place on the internet to ask an excel question or get help is in the Office Discussion Groups. More than likely a search here will turn up your answer and eliminate your need to ask.

Link:
http://www.microsoft.com/office/community/….

For tips on posting in the Office Discussion Groups look here:

Link:
http://www.cpearson.com/excel/newposte.htm

Sometimes it would be perfect if we could open(or view) the same workbook twice. This is particularly useful when working on Sheet1 and needing to see data on Sheet2 easily. There is a way!

On the main menu bar:

1. Click Window
2. Select New Window

This opened another copy of the current workbook. To see these more easily do this:

3. Click Window
4. Select Arrange
5. Select the Vertical radio button (or whatever suits your needs) and hit OK .

Excel Workspaces are an easy way to manage working with multiple workbooks. Save a default “look” of your multiple opened workbooks and create a workspace to open them the same way each time.

Link:
http://techrepublic.com.com/5100-6263-1033366.html

When using security in an excel workbook, or just creating a simple template, the need arises to protect the worksheet, but unprotect the cells you would like to allow data manipulation. Here’s how.

Before the worksheet is protected:

1. Right-click the selected cells
2. Choose Format Cells
3. Click the “Protection” tab
4. Make sure the checkbox next to “Locked” does not have a checkmark in it and hit OK
5. Protect your worksheet now, and your previously selected cells will be unprotected, but the rest of the sheet is protected.

A site dedicated to Excel links.

Description: A directory of Excel links, including templates, tutorials, add ins, tips, training, and articles. Users can rate links and add comments.

Link:
http://vertex42.com/ExcelLinks/

Free Excel expense report template. A perfect template to start from. Also check out the other templates while you are there.

Link:
http://office.microsoft.com/templates/…