Wednesday, September 30, 2009
Tuesday, September 29, 2009
I am putting together an 8 hour Functions seminar for the Indianapolis CPA Society and I think I am going to restruture it a bit. Usually when function are taught, they are taught as isolated examples. However, a lot of times you really need to use multiple functions together. So, I am going to show some useful examples that people can actually use. I am jumping the gun a bit here but I just put together a useful example and thought I would share it with you.
Frequently dates don't import the way you wish and you need to do a little 'surgery' on them. In the example below, I needed to use 4 Text Functions and 1 Date function. Now, obviously the first time or two you do this, it'll take awhile but then it will be second nature......... well maybe :)
In this example, dates that were imported into Column A are not in a date format familiar to Excel.
Using 5 different functions: 4 Text functions and 1 Date function we can extract, combine and then display Column A values in a date format.
I have broken it apart so that you can identify all the different functions.
The Left function retrieves the 4 leftmost characters in the cell
The Find function searches for the period in the cell and determines that there are 4 characters to get to the period plus 1 = 5 characters
The Mid function goes to the cell and uses the 5 characters + 1 character as the starting point and then grabs the 2 next characters
The Right function grabs the last 2 characters to the rightAll the functions are housed within the Date function which then provides joins the characters together in a familiar date format.
Stayed tuned... let's see what else I can come up with.
If you use any useful combination of functions please let me know. I love to use practical examples.
Monday, September 28, 2009
Here is a picture of Rockport Harbor.. absolutely beautiful..
Wednesday, September 16, 2009
Getting ready to create budgets for 10 departments? Don’t create a budget and then copy it 10 times – instead use grouping. Not only is it faster- it also ensures all your original formatting is retained. Other uses for grouping include printing. Perhaps you want to print Sheet 2, 5 and 6 – the easy way to print them all at once is to group the sheets and then select print. Another use for Grouping is to apply headers and footers to all your sheets at the same time.
Methods for Efficiently Updating/Moving Data Grouping Sheets
Grouping sheets allows you eliminate the need of copying cells in a worksheet to another and then reformatting. Data entered into a group of worksheets is actually placed into each of the worksheets. Any formatting is also included. This feature is particularly useful if you need to have similar worksheets set up in the same format.
To group sheets adjacent sheets:
1. Select the first sheet.
2. Press Shift and hold it down.
3. Click on the last contiguous sheet in the group.
The title bar now shows the word Group after the book name and that all the sheets in the group are active.
Anything that you type on one of the group sheets will automatically be copied simultaneously onto all of the sheets in the selected group including formatting techniques such as changing column widths. Remember to ungroup the sheets when finished so that you can individualize them.
To deselect an individual worksheet, press CTRL + the specific tab.
To ungroup the sheets, select a worksheet tab that was not in the group. You can also right-click and select Ungroup.
Thursday, September 10, 2009
I was putting together some information on range names and realized that when I posted my information on range names (under Basic Tips) that I neglected to mention a new feature in Excel 2007.
If you create a range name using the Name Manager icon, you can specify if the range name should be at the workbook scope (this is the default) or you can specify a specific sheet for that range name to be used on. In other words, in Excel 2007, you can actually use a range name more than once as long as it is at sheet level. If you do create a range name at the sheet level, it is only recognized on that particular sheet.
Click on the Formula Ribbon and then go to Name Manager and click New...
Type in a range name and then click the drop-down arrow beside Scope if you want it to be sheet specific. As I mentioned earlier, the default is workbook level.
In all other versions of Excel, you can only use a range name once in a workbook.
Wednesday, September 9, 2009
Scenario Manager - What IFWhere has the week gone? It is Wednesday already! I received some excellent news yesterday. After five and half months, CFO Resources / CPASelfstudy.com has finally been approved as a CPE sponsor in New York. Hurray!
But I do need to finish up my What-IF section by talking about Scenario Manager. This is a cool feature and is available in all versions of Excel- you just have to figure out where Microsoft hid it. In earlier versions, you have to go to Tools>Addins. In Excel 2007, look under the What-if icon on the Data ribbon.
Scenarios allows you to substitute values in a number of cells and save that condition under a unique name. By invoking different scenarios, up to 32, you can easily demonstrate to your customers and managers the impact of different events or possibilities.
- A scenario is particularly useful in creating budgets and forecasts when you have a few key variables that are subject to change.
- You can use scenarios to forecast different outcome based upon changes to these variables in your model. You can create and save these scenarios with different names and then display them.
- To compare several scenarios, you can even create a report that summarizes them on the same page- similar to a data table. The report can list the scenarios side by side or summarize them in a pivot table.
Before creating a scenario you need to determine which cells will be changed. Normally these should be your input or changing cells. You can have up to 32 changing cells in a scenario.
1. Click the Data tab on the Ribbon.
2. Click the What-if Analysis button and click Scenario Manager from the menu.
3. Click the Add button.
4. Enter a name for your scenario in the Scenario Name box.
5. Click in the Changing Cells box.
6. Hold down the Ctrl key and select the cells in your worksheet that you wish to change.
7. In the Scenario Values dialog box, enter new values.
8. Click OK.
9. To Apply a Scenario to your worksheet, select the desired Scenario and then click Show.
10. Click Close.
Careful! Adding scenarios to a workbook will change the underlying data so it is highly recommended that you save a backup of your original workbook first.
Once you have created your scenarios, you can create side-by-side Summary reports to compare your scenarios.
Take a look at it if you get a chance. Tomorrow I will try to post some screenshots and an example.
Friday, September 4, 2009
If you are one of those people with more than 6 or 7 sheets in a workbook, then you will find this tip useful.
-Right-click on the navigation bar and Excel will display a listing of all the sheets by name. Select
Thursday, September 3, 2009
I forgot to mention that down at the bottom of the blog, is a link to a video I did on Goalseek. Check it out when you get a chance. And don't forget to feed my little fishies....
Today, I wanted to talk about spinners. If I have time I will put some screen shots out on the different spinner steps but as usual I am behind schedule :)
A spinner, or Excel refers to it as a scroll bar sometimes, allows you to click and select different numbers. A spinner allows you to generate a large number of scenarios that vary each input between its high and low value. A spinner is a button that is linked to a given cell. As you click the spinner button the value of the linked cell changes and you can see the impact of these changes. This can be really useful if someone else is going to use the worksheet and is not very knowledgeable about Excel.
To create a spinner:
If the Developer tab is not available, display it by clicking on the Microsoft Office Button , and then click Excel Options. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
++If you are using Excel 2003, you would find the spinner button on the Form Toolbar. You can find the Form toolbar under the View menu.
1. On the Developer tab, in the Controls group, click Insert.
2.Under Form Controls, click Scroll bar .
3. Click the worksheet location where you want the upper-left corner of the scroll bar to appear.
4. Right-click and select Format Control
5. Select the values you want
6. Click OK
++ Please note that the maximum value cannot exceed 30000
For spinner control set the cell link to an adjacent cell on the form.
If the cell is locked, the spinner control will generate a warning when you use it after protecting the worksheet. Avoiding this problem is easy—with the worksheet unprotected, simply right-click on the cell associated with the spinner and choose Format Cells... Select the Protection tab and deselect the Locked checkbox. You can then protect the worksheet and use the spinner control without any problems.
Tuesday, September 1, 2009
Okay- my theme this week, in case you have not guessed, is What-If. Let's talk about Goal Seek. This is one feature that some people are familiar with simply because you were able actually find it in early versions of Excel under the Data menu unlike Solver and Scenario Manager that are add-in programs that you actually have to look for.
Goal seek is great to use if you are doing a simple and quick what-if. The reason for this is that Excel only allows you to change one cell with Goal Seek as opposed to 32 in Scenarios! Also, the cell you are changing cannot contain a formula. However even though it is a bit limited, it is still useful to know how to use it when you have something simple to determine. It certainly beats typing in a number, pressing Enter and looking at the answer and then typing in another number and seeing what that answer is... yes... some people still do that.. you know who you are. Try Goal Seek instead.
Goal seek allows the user to find a specific value for a cell included in a calculation by changing one other variable in the equation. It is a wonderful tool if you are trying to work through a what-if scenario.
For example, suppose you want to obtain a home loan but can only afford $800 a month. With Goal Seek, you could determine the maximum home purchase price that you could afford quickly.
Goal Seek requires 3 parameters.
1. The cell you want to change. In the example above, this would be the loan amount.
2. The value to which you want to change the target cell.
3. The cell you want to change to achieve the target amount.
To Use Goal Seek
1. Click the Data tab on the Ribbon.
2. Click the What-if Analysis button and click Goal Seek from the menu.
3. In the Set Cell Box, type the cell reference of the cell that contains the formula you want to change.
4. In the To Value box, type in the value to which you want to change the cell.
5. In the By changing cell box, enter the cell reference of the cell that contains the value you want to adjust.
6. Click OK.
7. Click OK.
I have a screenshot below example below: I wanted to change the current monthly payment in H6 to $800 by changing D6 which was the value of the loan.