Archives for March 2009

How to Create Scenarios in Excel

Scenarios are part of Excel’s “What-If” Analysis tool set – aka Data Analysis. Use Scenarios when you need to know what the result will be when you change one or more variables in one or more  situations (scenarios).

Frequently, I create Scenarios to define  a “Good, Better, Best” or “Worst Case, Best Case, Case Most Likely” outcome.

In this short Excel Training Video, I demonstrate how to create, edit,  and report four Scenarios for a Monthly Office Expense Budget.

These are the steps used in this Excel Video Lesson:

  • Create (or open) an Excel worksheet that contains a formula. Identify the formula and the precedent  cells that feed into the formula.
  • Select the “target cell” – the cell that contains the formula whose result will change with each Scenario.
  • Choose TOOLS – SCENARIOS and follow the directions in the Create Scenario dialog box.
  • First, click ADD. Name the Scenario. Click OK. Confirm that you have selected the “Target Cell” and then click / type in the “Changing cells.” The Changing cells should be either direct or indirect precedents to the Target cell formula. Click Add. Add additional Scenarios as desired.
  • Click on the name of your Scenario and select SHOW to display the changing cell values and the new result in your target cell.
  • If you wish, choose SUMMARY to create a summary report in a separate worksheet.
  • I strongly recommend that you create named cells and use them in the formulas and changing cells of your Scenario.

NEW! Download the Excel Practice file used in this lesson:

Use my Index to search for the free Excel Training Video that you want to view.

New! Danny’s DVD Training Series, “The 50 Best Tips …” is now available at the online store for The Company Rocks

Related Videos

12 Excel Practice Files to Download

I want to thank the viewers who emailed me and responded to the Poll on this blog. I have now uploaded 12 Excel Practice files to this blog. Follow this link to navigate to the Download Excel Practice files page.

  • Click on the Practice file that you are interested in.
  • Right-click the Icon to determine how and where to open the practice file.
  • The Practice files are saved as “Interactive Web Pages.” From here, you can decide to work with the file over the Internet or to save it to disk as an Excel Workbook.

I will be adding additional Practice files – approximately three each week – so check back to locate the latest files that I have downloaded. If there is a particular file that you want to work with, email me your request – danny@thecompanyrocks.com and I will make it available as quickly as I can.

Thank you for your continuing support of The Company Rocks!

Sorry, there are no polls available at the moment.

My Favorite Excel Keyboard Shortcut

I use Keyboard Shortcuts in Excel everyday. However, the one that I count on the most; The one that has saved me from near disaster on multiple occasions is:

Ctrl + Z – The UNDO Command.

Ctrl + Z can Undo my last 16 actions (one at-a-time.) In Excel 2007, you can Undo up to 100 of your last actions!

Watch this short Excel Training Video as I demonstrate how to take advantage of the Ctrl + Z Shortcut.

Here are the steps to follow in this lesson:

  1. Type an entry or apply formatting to a cell or range of cells.
  2. Hold down the “Ctrl” Key while you press the “Z” key. Excel will Undo your last action.
  3. To Restore the Action that you just UNDID, use the “Ctrl + Y+ Keyboard Shortcut.
  4. You can Undo your last 16 actions in Excel 2003. Use the drop-down menu next to the UNDO Command Button on the Standard Toolbar.
  5. Follow a similar  procedure to RESTORE up to your last 16 actions in Excel 2003 with the drop-down menu next to the Restore Command button on the Standard Toolbar.
  6. In Excel 2007, you can UNDO and RESTORE up to your last 100 Actions!

Are you looking for Excel Video Training for a specific topic? Search my Index of Excel Video Topics.

Related Videos:

NEW! Now you can practice your Excel Skills – Download the Excel Workbooks that I use in my video lessons

How to Publish an Excel Workbook as a Webpage

How good is your information – if you or your colleagues can not access it when you need it?

Not good enough!

I love USB Flash Drives for their portability. But, when I need – or I need to send an Excel Workbook – halfway around the world …? I need a better way to access that information.

I need to learn how to publish my Excel workbook as Web page – complete with Interactivity. Watch this short video lesson as I demonstrate how to:

  1. Save an Excel Workbook as a Web-page – be sure to enable Interactivity.
  2. Add, edit and delete data in the workbook while on-line.
  3. Send the Webpage version of the workbook back to Excel – to communicate my changes or to further analyze the data.

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related videos

Looking for the solution to an Excel problem? Search my Index of Excel Video Lessons.

What is an Interactive Excel Workbook? Please Vote

I created this short video for two reasons:

1)  To demonstrate what an “Interactive Excel Workbook” looks like. Interactive Excel files are “published” to an Internet or Intranet browser. You save an Excel Workbook as a “Web Page.” Adding “interactivity” is an option that you can select.

Notice that there are no Menus and there is only one toolbar. Also, you activate the other worksheets in a different way.

I am happy to make these Practice files available to you – if there is sufficient interest.

2) Please signify your level of interest by taking the POLL on the sidebar of this page. It is fast and easy – there is only one question; and only one choice, “Yes” or “No.”

I hope to hear from you!

Go to my new page to download the Interactive Excel Practice Files
Thanks in advance!

Danny Rocks

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Are You Interested in Subscribing to a Podcast?

Update: My Video Podcast, “Danny Rocks Tips and Timesavers” is now available – free of charge – on iTunes. I add at least three episodes per week. Follow this link to view, comment, or subscribe to my Podcast.

Based on the positive response to my recent post, “Statistics Do Not Tell the Complete Story,” I am planning to initiate a podcast.

Initially, I am planning to produce two podcasts per month. The subject area is” improving communications skills.”

Are you interested in subscribing? Or do you need more information? Do you presently subscribe to podcasts?

I would like to hear from you on this topic. Please take a moment to “vote” on this topic – I have added a poll  to the right sidebar of this page. (the poll is in the middle of the sidebar.)

Thanks in advance for voting on this topic!

Sincerely,

 Danny

Do You Want Copies of My Excel Workbooks?

I have a question for you?

I just added an “interactive copy” of my Excel Workbook for my video lesson – “Rearrange Your Columns – Sort from Left to Right”

Here is the link to the workbook.

My question is: Should I go back and add links to the Excel Workbooks for earlier lessons? To make it easy for you to respond, I have included a quick poll to the right sidebar on this page (at the bottom of the sidebar).

Thanks in advance for your response!

Danny

Sorry, there are no polls available at the moment.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Rearrange Your Columns – Sort from Left to Right

Did you know that you can sort data from Left to Right in Excel? In my experience, most people do not realize that you can.

Have you ever been asked  – or wanted – to rearrange your columns in Excel? Probably, yes. This is a fairly common request. And, if you are like most Excel users, you insert a blank column and then “Cut and Paste” to arrange your columns the way that you want them.

STOP!

There is an easier way to rearrange your columns of data – Learn to “Sort Across Columns.” Sort your data from Left to Right.

Watch this short Excel Video Lesson to learn how:

Here are the steps to follow in this lesson:

  1. Insert a blank “dummy row” above your Column Headers Row (if necessary).
  2. Place a number (1, 2, 3, etc.) in each cell in the “dummy row.” This will indicate the order you want to rearrange your columns in from Left to Right.
  3. Select a cell in your data set. Select Data – Sort.
  4. Check, “MY Data Range has NO Headers.”
  5. Click, OPTIONS and select “Sort from Left to Right.” Click OK and Click OK.

Looking for help with a specific Excel problem? Search my Index of Excel Video Lessons by Category or by Keyword.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

NEW! Download The Excel Workbook used in this video lesson:

rearrange-columns