Adding a VBA Command Button

Adding a Visual Basic Command Button

We brought our 401K Calculator worksheet to a conclusion by using an ActiveX Command Button. We wrote code in our Visual Basic Editor to function whenever the Button is clicked to automatically execute the steps needed to Clear Data, and then request new information from the user to add data in our Retirement Calculator.

That should give you a small taste of what you can accomplish using VBA with Forms and ActiveX Controls.

If you find this sample interesting, then feel free to complete the last part of the chapter on VBA (Visual Basic for Applications) by constructing a User Interface to entirely automate the worksheet. You'll be surprised by how much can be controlled by getting an understanding of the capabilities of using VBA. Look over and optionally complete pages 567-603.

Yom Ha'Atzmaut!!

"The pride each and every one of us feels, here and everywhere a Jewish heart beats, when we make the transition from the sanctity of mourning to joy, is the true story of this day." -- Ruby Rivlin

Israel celebrates 61st Independence Day

Trial Begins in France

Gang on Trial For Torturing French Jew, Ilan Halimi, to Death

Yom Ha'Zicharon

Yom Ha'zicharon Ceremonies precede Yom Atzmaut

20. Adding a Macro

What is a Macro?

Though printing our worksheet shows just what we had in our 401k Calculator and Data Table, we worked on creating a custom report by using a Macro.

Our Macro changes the orientation to Landscape, makes it scale up to 115%, centers the sheet both horizontally and vertically, and adds custom Headers. It then sends the job to the printer, and then undoes all the changes, and leaves us with our original Default Print job intact.

You can use a Macro to automate procedures so that you don't have to do them step by step every time. If you can think of a number of steps you'd like Excel to perform, you can create a Macro to do just that.

Remember that we changed the level of Macro Security, so that Excel requires us to OK a file with an included Macro for our own safety! We also saved our last file as an XLSM file, which means that it is an Excel 2007 file that is Macro Enabled. Saving, closing and re-opening the file gives you control over what Macro-Enabled files you open.

We looked at the code generated in VBA (Visual Basic for Applications) just to get an idea of what the code for a simple Macro looks like. The macro seems much more complex than the few steps we recorded.

We went on to Add a Button to the Quick Access Toolbar, and to assigned our Print Macro to the Button. That, along with our Keyboard Shortcut, allows us to quickly execute the Macro.

Materials Covered: Project 7 - Using Macros and Visual Basic for Applications (VBA) with Excel, pp. EX 548-566

Case 8: Developing an Excel Application. Follow the instructions to download your workfile.
Caution!! To correctly assign a Macro to a Button, use a Form Control Button from the Form Control Group, or your efforts will be useless!"
In your journal, write a short note telling me what you learned from this case. Thanks.

19. Adding a Data Table

Adding a Data Table to complement our Retirement Calculator gives us the ability to look at a broader view of the calculations based around the number of years you expect to be employed.

In examining the comparisons, you can see that at around 10 years of employment, there is a break even point - that is, the Return on the Investment is about the same as the Actual Investment. After that the return on an investment grows unimaginably!

You can do this type of analysis quickly and simply only through the use of a spreadsheet. These techniques give you the tools to answer financial questions easily!

Materials Covered: Project 7 - Using Macros and Visual Basic for Applications (VBA) with Excel, pp. EX 539-548

Yom Ha'Shoah

Yad Vashem is uncovering more Holocaust stories

18. Preparing for Retirement!!

Do you know anyone eligible for retirement? Well, one day it will be you who is eligible. Here's how to prepare! Put together a Retirement Savings Planning Calculator. The basic elements of the Calculator are your Annual Salary, the Percentage of your salary to be set aside in a 401k plan, your Employer's Matching funds, the Annual Return on your Investment, and the Number of Years you Expect to be Employed. This holds true whether you are an employee in someone else's company, or you are a business owner. You are permitted to put money into a 401k account tax-free!

With that basic information, you are able to set up formulas to calculate your Monthly Contribution to your Retirement Fund, your Employer's Contribution, and the Sum of both Contributions. Using the FUTURE VALUE Function, you can calculate the amount of money that you will have available at the time of your Retirement.

The calculation is based on a fixed Income, at a fixed rate of return, for a fixed number of years. Naturally that rarely will be the case in a person's lifetime, so this calculation is simply meant more as a guideline than as a real situation. It gives you a point of reference for thinking about Retirement Investments and Income.

Once you understand the process, you can begin to prepare for the day you can stop working and start enjoying yourself!!!

Materials Covered: MS Excel - Project 7:
Using Macros and Visual Basic for Applications (VBA) with Excel, pp EX 530-539

Case 7: Working with Named Tables, Logical Lookup, and Database Functions.

Follow the instructions to download your workfile. Caution!! Make sure you get Step 2 correct, or all your efforts will be for naught!" In your journal, write a short note telling me what you learned from this case. Thanks.

17. Take the Excel Homerun Challenge!!

Click Here for the Excel Brain Bender of the Day. This one requires you to think about how to pick and use functions in a clever way to solve a problem.

19. PivotTables & PivotCharts

A PivotTable does for Data what a Rubik's Cube does for fun - it lets you keep rearranging views until you have your data just the way you want it!

We used the data from our combined worksheet to creat a PivotTable. We added and then kept changing the view of the data so we could see it in different ways. In addition, we filtered the PivotTable so we could limit what we saw to a smaller portion of the data. We used a PivotTable Design to enhance the look of our work.

Then we created and formatted a PivotChart. We changed the order of the data in the Chart, and noticed that the data changed in the Table as well. Conversely, we changed the order of the data in the Table and noticed that it changed in the Chart. There is a wonderful interactivity between these two elements.

Materials Covered:Excel Project 9 - Importing Data, Working with PivotCharts, PivotTables, and Trendlines, pp. EX 745-760

Assignment 10 - Case 6: Working with Multiple Worksheets and Workbooks:

• Tracking Notebook Sales
• Follow the instructions to download your file, complete work on it, and then to upload it for grading.
• In your journal, write a short note letting me know what you learned from working with Charts in this case. Thanks.

18. Importing to Excel

Chapter 9 begins by Importing Data to Excel from several different types of files: a native Excel file, a Comma Delimited Text file, an Access Database file, a Word Document file, and from a Webpage. Excel can import lots of different file types including from XML maps & files, recent innovative file types.

Excel contains data filters to accommodate each different file type of import. These filters assist you in helping Excel to place the data so that when combining files into a spreadsheet, arts and crafts can be kept to a minimum, while you add the data.

Materials Covered:Excel Project 9 - Importing Data, Working with PivotCharts, PivotTables, and Trendlines, pp. 706-734

Read a NY Times smear of Religious Zionism in
A Religious War in Israel’s Army and
Having a Bat Mitzvah in Their 90s Because It’s a Hoot
What do you think?

16. Charts and Multiple Worksheets

We worked on customizing a Clustered Cone Chart that reflected the consolidated data. We formatted the background color, and added a title using Word Art. We added a Text Box and an arrow to accentuate the most important part of the Chart.

Getting the Workbook ready for printing is also an important part of this project. By selecting all the worksheets, we were able to change margins and create a Custom Header and Custom Footer that enhanced all our worksheets.

In short, there are projects where Templates can multiply the usefulness of the way we use Excel.

Materials Covered: MS Excel - Project 6:
Creating Templates and Working with Multiple Worksheets and Workbooks, pp. 461-480


A break from Cases this week:
Assignment 9 - Shortcuts and Function Keys in Excel 2007: An Online Reference:

1. Click Here for a list of Keyboard Shortcuts and Functions Keys.
   
2. When you're done, in an entry in your journal:.

in your opinion, what are

• three most useful Shortcuts and Function Keys specific to Excel,
• and three most useful generic Windows Shortcuts? Thanks.

15. Creating a Template

If you need several worksheets that will be used to pass their data to a Consolidation Sheet, and you want the worksheets to have a consistant structure, look and feel, then it may be worth your time to think about creating a Template. A Template is a special workbook that you can create and then use as a pattern in creating similar worksheets for a project.


We began working on our template for a multi-location business example. We setup our Title Rows and Column Headings just as we would for a regular worksheet, changing Row Height and Column Width, where appropriate. And we used a Function to include the System Date in the Header area. We entered some data so that we could test our formulas. The most interesting formula was for the Average Unit Price on an item sold by the business, in which we calculated the Margin on the products to be sold.

We formatted Column Titles for a consistant look, applying Number Formats, with the Currency Style in the first and Total rows of dollar figures, and applied a Custom Format by adapting a Comma Style, with Blue format for Negative Numbers.

The key move for us was to Save the Worksheet as a Template in the 'Save As Type' field.

We used selected worksheets to 'Drill' entries through three sheets, and then customized each sheet for a different location.

The best part is that using a Template to create multiple worksheets is a great way to expedite this kind of project, where we want to funnel data from several sheets into a Consolidated Worksheet with easy to create Three-Dimensional references to Sum up data across the sheets.

Materials Covered: MS Excel - Project 6:
Creating Templates and Working with Multiple Worksheets and Workbooks, pp. 418-460

14. Formatting & Charting

We formatted in ways that make the worksheet legible, presentable, and logical. We used the Currency Format (rather than the Accounting Format) in first lines and total lines of dollar figures - this is standard for a financial document, like an Income Statement - and the Comma Format, for all figures in between first lines and totals.

A picture helps to understand the data. The Three Dimensional Pie Chart is perfect for this project because it makes it easy to see the six monthly totals graphically. Pie Charts are great when you have limited data.

The Chart Tools Layout Tab helped to:

• Insert a Chart Title
  
• Insert Data Labels
  
• Rotate the Chart
  
• Apply a 3D Format with beveling and a special effect
  
• Explode the Pie Chart
  
• Change the color of each slice

In addition we:

• Renamed,reordered and added color to our Sheet Tabs
  
• Ran a Spell Check
  
• Ran a Print Preview to hone the Print Job
  
• Analyzed data by changing some of our Assumptions
  
• Ran Goal Seek to force the result in one cell by changing another

This Project is filled with interesting examples of how to set up a good worksheet, how formatting enhances the information, how to 'Chart' it, and how to use the analytic capabilities of Excel for making predictions. Each operation by itself is a small addition to the entire picture. But all together the result becomes a powerful means of presenting information!

Materials Covered: MS Excel - Project 3:
What-if Analysis, Charting, and Working with Large Worksheets, pp EX 161-228

Case 5: Working with Excel Tables:

• End of Year Stationery Sales
• Follow the instructions to download your file, complete work on it, and then to upload it for grading.
• In your journal, write a short note letting me know what you learned from working with Charts in this case. Thanks.

13. Making Assumptions

We began entering data into a new business style worksheet to analyze six months of projected income and expenses for Campus Clothiers. Our worksheet will end up as a projected Income Statement.

We entered the sheet name, and subtitle, and then first column heading, 'January'. We changed the orientation of the text to appear at 45 degrees, then used the Fill Handle to create a series of the Months from January to June. Notice that the formatting of our first cell was copied along with the series.

We proceeded to add our Row Names, creating areas for Sales Income, Expenses, Operating Income, and working Assumptions on how we allocate funds to expenses. All of our formulas for expenses were based on 6 months of projected income. Here we allocate a percentage of our income to each expense, never spending more than we earn.

Because we are creating a large Worksheet, we use the technique of Freezing Panes. That helps you to keep your titles in view as you scroll through the data of the sheet.

We saw the value of using Relative and Absolute references in our formulas. In parts of our formulas that reference our Assumptions, we have to use Absolute reference, so that formulas copied across the 6 months with the Fill Handle will always reference the specific cells that contain our Assumptions. **Notice that the F4 Key is a useful utility for painting $ signs around those parts of our formulas where we need to maintain Absolute Reference.

You can see that the IF FUNCTION helps in making decisions about when to pay out the Bonus to the employees of the company. The AUTOSUM FUNCTION gets the MVP award in this worksheet! And the TODAY FUNCTION also helps to inform us of what day we printed the Worksheet.

Materials Covered: MS Excel - Project 3:
What-if Analysis, Charting, and Working with Large Worksheets, pp EX 161-194

12. Another Use for the Data Table

The Beanbag Exercise: Pick a partner to work with on the Beanbag Company Quarterly Income Statement exercise. You will analyze at what point a manufacturing business breaks even on income and expenses, and begins to make a profit. This exercise guides you in becoming more observant about the details of creating a really good worksheet using a Data Table, and helps you to focus on techniques you've already learned in order to solve a new type of problem.



Case 4: Working with Charts and Graphics:

• Making Revenue and Sales Projections
• Follow the instructions to download your file, complete work on it, and then to upload it for grading.
• In your journal, write a short note letting me know what you learned from working with Charts in this case. Thanks.

*** Did you know...the future is happening now?

11. Finding Information in the Database

With Excel's Table feature, we can use some terms regularly associated with Database functions. We set up a Criteria Range at the top of our worksheet. We executed a Criteria search with the Advanced Filter command, and noted that our Table then only displayed the records meeting our Criteria.

We also set up an Extract Area to perform the same Criteria search. This time, our selected records were copied to the Extract Area, while our List Area remained unchanged.

That allows us, if we wish, to manipulate our extracted records without making changes to our original list. The key to these operations are in naming the Criteria and Extract Ranges, using the names "criteria" and "extract". These words actually give Excel the contextual clue that it needs to perform these powerful steps.

As you may now know, there are 12 Database/Table Functions in Excel, all beginning with the letter D, for Database (such as, DAVERAGE, DMAX, DCOUNT). Most use the three arguments: Table Range, "Field Name", and Criteria Range.

With that in mind, we created a mini Criteria Area on an outer area of our worksheet, and used it in conjunction with the Database Functions DAVERAGE and DCOUNT.

Using DAVERAGE, we calculated the Average Age for Females, and the Average Age for Males. We can compare that to the TABLE TOTAL row provided by Table Design to have an internal check. Using DCOUNT, we calculated the number of those Reps on our list who had scored an A.

We also used two additional Functions, (not classic Database functions) related to the IF Function, SUMIF and COUNTIF. We used SUMIF to calculate the total YTD Sales for those that scored an A; we used COUNTIF to add the number of Females on our list.

Materials Covered: MS Excel - Project 5:
Creating, Sorting & Querying Lists, pp 380-398

Case 3: Working with Formulas and Functions:

• Follow the instructions to download your file, complete work on it, and then to upload for grading.
• In your journal, write a short note letting me know what you learned from this case. Thanks.

10. Useful Tools for Tables

In today's class, we continued working on our Silver Photography Sales Rep Table.
We:

• Created a Grade Table
  
• Learned how to use the VLOOKUP function to determine letter grades
  
• Added Conditional Formatting using a Rule with colorful Icons
  
• Used a Table Total Row
  
• Examined the variety of ways to Print the Table
  
• Sorted the List using the Sort and Filter Button
  
• Using Sort on the Data Tab
  
• Sorting with an AutoFilter
  
• Sorting on Multiple Fields
  
• Converting a List to a regular range of cells
  
• Displaying Automatic Sub-Totals for analysis
  
• Using Sub-Total Tools
  
• Removing Sub-Totals
  
• Re-Formatting the Table
  
• We used the Table AUTOFILTER's to find subsets of our records.

Materials Covered: MS Excel - Project 5:
Creating, Sorting & Querying a Table, pp 356-384

9. Using Tables

The ability to create Tables, what we ordinarily call Databases, in Excel 2007 (previously known as 'Lists' in Excel 2003), is one of Excel's very useful specialties. This feature of the program is much more robust in '07. Not only are there special tools just for this type of data structure, but Tables lend themselves to maintaining lists of information that can typically be very helpful in either a business or for personal needs, whether you have lots of data (like Inventory or Customer information) or small amounts that you use for record keeping (like your personal checkbook).

We began putting together the Silver Photography Accessories Sales Rep Table, and went over how to use Excel's Table Design tools to

• Create and pre-Format our Table
• Add Data Validation
• Enter Records using a traditional type of data entry.
• Sort and Query data
• Create a calculated field

Materials Covered: MS Excel - Project 5:
Creating, Sorting and Querying a Table, pp 338-356

Excel Online Exercise on Financial Functions:
Click Here for a short online review to get better acquainted with Financial Functions. There is a nice twist here on what we've learned about the PMT Function - it is the NPER function! Excel's Financial Functions are related to one another like the faces on a Rubik's Cube.

You'll notice that this is a tutorial from Excel 2003. It hasn't yet become available for Excel 2007, but it's so important, I thought you should get the benefit of this one anyway! I hope you agree.

Write an entry about this online activity in your journal - express your opinion in your blog!!!

8. An Amortization Schedule

We worked on part 3 of Financial Functions by beginning our work on an Amortization Schedule. The Amortization Schedule works in tandem with our Loan Payment Calculator and our Interest Rate Schedule.

The Amortization table shows us the Beginning and Ending Balances for each year of the life of a loan, as well as separating out what's been Paid on the Principle of the Loan, and on the Interest Paid. That's particularly useful if you should want to pre-pay a loan before its term expires. We worked with the financial funtion, PV, which yields the Present Value of a Loan. In our case, it calculates the Ending Balance for each year in the life of the loan when we provide the Loan Rate per Month, the number of Monthly Payments left (here we subtracted the actual Year from the Full term of the Loan), and (a negative) Monthly Payment Amount.

Because we copied the the PV, as well as the Principle and Interest Paid, down the column, we were able to see that when we changed the number of years of the life of the loan to 18, that the PV Function went negative in the 18th year and beyond. We were able to correct that problem by wrapping the IF Function around the Present Value, and using logic to express the PV as zero is it exceeded the number of years of the loan.

We had the IF Function check to see whether the year we were looking at was Less Than or Equal to the total number of Years of the loan. If so, the PV Function was executed. If not, a Zero was placed in the cell. We did something similar with the formula for Interest Paid - that is we had the IF Function check whether the Opening Balance for the Year was Greater than Zero.

And we finished up by making the Beginning Balance of the second year equal the Ending Balance of the first, and fill-handling down.

Materials Covered: MS Excel - Project 4:
Financial Functions, Data Tables, and Amortization Schedules, pp 294-302

Tzipi or Bibi?

7. A Loan Rate Schedule

Using a Data Table for Analysis
Seeing how spreadsheet answers vary with changes to the spreadsheet's input is called 'Sensitivity Analysis'. It's a way of asking Excel 'what-if' questions, where you want to see a variety of answers. A DATA TABLE allows you to see and compare the results of a formula for several different values. A one-input data table lets you vary the value in one cell. A two-input data table lets you vary the values in two cells.

Adding a pointer by using Conditional Formatting is a way of setting off a particular piece of data that is important because it is the value that causes everything else to change when it changes.

Materials Covered: MS Excel - Project 4:
Financial Functions, Data Tables, and Amortization Schedules, pp 284-294

Case 2: Formatting a Workbook:

• Follow the instructions to download Case 2's Worksheet, Formatting a Two Year Analysis Report
• Complete work on this file, and then upload it for grading. See how well you do. You may submit your file more than once till you're satisfied with the results.
• In your journal, write a short note describing what you learned from this case. Thanks.

Read a chilling article by Judea Pearl, the father of slain WSJ reporter Danny Pearl, Daniel Pearl and the Normalization of Evil.

6. Understanding Loans

A Loan Calculator
We created a Loan Calculator using some basic loan data - amount, interest rate, and duration in years - along with Cell Names and the PMT Financial Function. The PMT Function is a powerful tool in Excel. Knowing how to use it will help you to analyze loans to see whether the conditions of a particular loan are good for you or not.

We used a interesting technique, Naming Cells. This operation has two very distinct advantages: first, it allows us to create a formula using words in English rather than simply using Cell References, which can sometimes be confusing; second, the cell's name actually is an Absolute Reference, wherever and however it is used in your worksheet!!

Materials Covered: MS Excel - Project 4:
Financial Functions, Data Tables, and Amortization Schedules, pp 266-283

5. Introducing CaseGrader

First CaseGrader Assignment

You will need to acquire CaseGrader: Microsoft Office Excel 2007 to do the following:

Case 1:

1. Read CaseGrader textbook pages ix in the text to get the overview of CaseGrader exercises.
2. Go to the CoursePort Website at: http://login.course.com
3. Select CaseGrader and go to the page containing a listing of the Cases
4. If you haven't yet done so, add CaseGrader and your text's Keycode to your account, followed by Joining the class with the Class Code: 7DD18525.
5. Select Case 1: Getting Started with Excel, Newleaf Paper Company - Calculating Employee Bonuses
6. Download and save Case 1
7. Follow the directions exactly while working on the spreadsheet.
8. When you've completed all the steps, save your file, then upload and submit your worksheet for grading.
9. From your GradeBook, generate the Summary Report to see a detailed report of how you did on each step.
10. Note: If you find errors that you made, you may make corrections to your worksheet and submit your file more than once, until you're satisfied with the results. I will only count the highest grade, so you have nothing to lose by trying until you succeed!
11. In your journal, write a short note letting me know what you think of the value of this experience. Thanks.

* Thinking of Purchasing Office 2007?

Here are a few things you should be aware of:

• If you are interested in downloading a Trial Version of MS Office 2007 before making a buy, CLICK HERE to go to Microsoft's Website, and choose the version you want to download. It will be good for 60 days.
  
  
• Want to BUY Office 2007? There are at least two options:
  
  1. Buy the Student Special Ultimate version direct from Microsoft for $59.95. This version comes with all the apps you will want.
     
  2. Buy the Enterprise Edition through Yeshiva University's software connection. Here you pay $83.75.
  
  
• MAC Users - Read the NY Times article New Tools to Bolster MAC's World for some insight into this version. It's not available on the Microsoft site, but it IS on YU's site above.
  
  
• If you have any additional information on the subject, let me know, and I'll pass it along to everyone
  

4. Formatting Silver Dollar Worksheet

We continued working on the Silver Dollar Stock Club worksheet. We learned about Fixed Dollar Signs as an effect of the ACCOUNTING FORMAT, the Floating Dollar Signs of the Currency Format, the use of the COMMA FORMAT for our other cells, and how to increase or decrease the number of displayed Decimal Places.

We used CONDITIONAL FORMATTING to have Excel flag us when percentage values dropped to less than zero, by showing the cell data with red fill as a warning. We stretched Column A's width, and used Best Fit for Columns B, C, and D.

We performed a number of additional operations:

• Changing Column Widths to a specific fit
• Changing Row Height
• Running a Spell Check
• Displaying the Formula Version of a worksheet should we need to audit it for problems
• Importing External Data using a Web Query
• Referencing the Query in our Analytic worksheet so that each Query Refresh brings in the latest values
• Renaming Sheet Tabs

We used the Internet to Get External Data with an MS Stock Query to find out the real current values of our stocks. Then we linked the latest prices from the query to our Investment Analysis page, so that every Internet Query lookup updates our Stock Analysis sheet in Real Time. We renamed the sheet tabs, 'Portfolio Summary' and 'Real-time Stock Quotes.'

Since there are no real agreed upon standards for setting up a good worksheet. You must rely on your own sense of what works best in each separate case. That actually gives you lots of room for creatively attacking a project!! Study the techniques used in other worksheets!!

These operations should convince you of the awesome power of Excel to analyze information in an extraordinarily useful way!! You can see how sophisticated your use of Excel can be. You can create different sorts of Financial information and documentation. You've already started to master this tool and to be able to create Financial Worksheets where the only limitation is your imagination!!!

Materials Covered: MS Excel 2007 - Project 2:
Formulas, Functions, Formatting and Web Queries, pp EX 81-144

3. Creating a Stock Portfolio

We began working on the Silver Dollar Stock Club Portfolio Summary spreadsheet.

We set up a sheet title, and column headings designed to help us clearly see the stock holdings of the club. We entered data, including stock, symbol, acquire date, shares, initial price, initial cost, current price, current value, gain/loss, and % gain/loss.

By using the numeric keypad, we were able to enter our numbers and values quickly and efficiently with the least amount of hand movement. We also entered formulas for Initial Cost, Current Value, Gain/Loss, and Percent Gain/Loss, both by hand and with the 'POINT AND CLICK' method. Using the Fill Handle, we were able to copy our formulas quickly and easily down columns. We used the Autosum button to add totals for Initial Cost, Current Value, and Gain/Loss.

We entered Functions to calculate Average, Max and Min, and to generate statistics for our data. Then we began to format the worksheet, changing the FONT, FONT SIZE, FILL COLOR and FONT COLOR, and used the MERGE AND CENTER tool to get our Title to look presentable.

These operations should convince you of the awesome power of Excel to analyze information in an extraordinarily useful way!!

Materials Covered: MS Excel 2007 - Project 2:
Formulas, Functions, Formatting and Web Queries, pp EX 81-106

An Overview of Basic Operations: An Excel Online Tutorial:

1. Click Here for an online review on creating formulas. Work on all three sections - Get Started, Use Cell References, and Simplify Formulas by Using Functions.
   
   Just go through the tutorial. If you also want to work on the interactive exercises, you'll need to work on a machine, like the ones in our lab, that runs Excel 2007 in order to complete this exercise.
   
   
2. When you're done, write an entry in your journal about this activity, and any insights you may have learned from this exercise. Did you find out anything new? What was it.
   
   
3. Since our work will depend on your knowledge of basic Excel operations, please have this assignment done by Sunday evening. Thanks

2. Getting to Know Excel 2007

We began our unit on MS Excel with Project 1 from our textbook, MS Excel 2007, Comprehensive Concepts and Techniques.

Looking at the screen layout and tools of the program gave us a good idea of how different this version of the program is, and the variety of tools this program has to offer.

We observed and used:

1. the Office Button (the old file menu)
   
2. the Quick Access Toolbar
   
3. Tabs and Ribbons
   
4. the new default font, Calibri, size, 11
   
5. AutoSum, the Fill Handle, new Formatting Themes and Styles, Merge & Center, Number Formats
   
6. Adding and moving an embedded Chart
   
7. Live Preview of Styles
   
8. Document Properties
   
9. Print Preview and Setup
   
10. the Autocalculation Tool in the status bar

We were able to create a professional looking Worksheet with an embedded Chart within a short time, and to exercise control over some essential Excel internal tools.

Materials Covered: From MS Excel 2007 - Project 1:
Creating a Workbook and an Embedded Chart. pp 2-69

1. excel for business

Welcome to Excel for Business
and to our class 'Blog'

My Dear Student:

Welcome to Spring 2009's Excel for Business course. This class is unique in that we'll be working to develop an expertise in one particular software tool that is used extensively in business, the professions, as well as in personal areas. We'll all be working together toward the goal of becoming creative and competent Excel Mavins!

Thanks for selecting this Excel class. The hour will go by quickly, but you'll be pleasantly surprised at just how much you'll love all the new Excel tools you'll be learning to use in each session!!

We'll be using Excel 2007 in class. We have new textbooks that have been written specifically for Excel 2007. This version of Excel has more power tools than previous ones. It will help us to stretch our capabilities. As you may already know, Excel 2007 is even more interesting and fun than the '03 version of the program!!

During the semester, we'll work at becoming a dynamic community of Excel specialists. We'll work on projects, data analysis, and business modeling, and we'll help each other to nurture and hone our Excel skills.

Blogger: The page you're reading now was created in Blogger, which you've registered for in our first class session. This is your Excel for Business Course 'Blog'. You may already have experience with blogs and know that they have become very important tools on the Internet, in business and in the news. Now you too are a member of the blogging community! That means that on the Internet, you are not just a spectator, but a participant!

I'll be using this journal to keep an ongoing description of what we accomplish in each class session, and to post assignments as well as thoughts. On a practical level, if you miss a class, you'll be able to find out what we did and how you can make up the work. If you need a quick overview of what we've done during a session, here's the place to come. Everything you need to know about our Excel course will be accessible from this page, so there should never be a question as to what's expected of you.

You'll be keeping your own journal as we explore Excel, and you may also be writing about a variety of other subject matter. I encourage you to personalize your blog with art, skins, links, photos, movies, music and writing that genuinely show off your talents, interests, new ideas, and any thoughts you may want to express in writing! And, you'll be pleasantly surprised at how satisfying and empowering that can be!

The Links on this Page: As you may have already noticed, I've placed links on the sidebar of this page. Those links take you to a variety of places, starting with our Course Oriented Links. I'll be adding more as the term progresses. Feel free to suggest additional links.

Right now, you should look over the Course Outline, and print a personal copy for yourself. This is our 'road map' for the next 12 to 14 weeks. In addition to detailing the elements of how the course is constructed, the Course Outline lists all the Class Requirements, including what goes into calculating your grade!! Read it so there are no surprises at the end of the semester.

You'll also have access to the Class Directory, where you'll find everyone listed. I will be adding your email and journal addresses. Very soon we'll also have everyone's photo viewable as well! And that will be a way of getting more familiar with your classmates.

Some of the other links will take you to a listing of our Textbooks, to the NY Times Circuits section, to Blogger, to the Course Technology Support Site related to our text, to the Student Downloads Page, and to the CoursePort Website, which we'll be using when we work with our CaseGrader textbook.

I've also included several news links for you to be able to keep up with events here, in Israel and around the world, in addition to some other fascinating sites. Feel free to explore them.

Hyperlinking makes it easy for you to get to all the important information for our course online whenever you want it, regardless of what time it is, or where you happen to be.

In class, we've registered for individual Blogs, and we've signed up to be regular readers of the New York Times Online Edition. Once registered with the Times, in addition to having regular access to news and other events, you will be able to enjoy the following special features

• Circuits-Thursday's section of articles all about Computers
  
• Cybertimes-an online selection of articles on Technology Issues
  
• ScienceTimes-Tuesday's section of articles on Science, Medicine and Health
  

Your First Assignment: An Online Excel Tutorial:

1. You'll begin with an overview of the new Excel 2007. Work on all three sections - What's changed, Get to work in Excel, and A new file format.
   
   You don't have to work on the practice exercise portion of the tutorial unless you want to. If you do, you'll need to work on a machine, like the ones in our lab, that runs Excel 2007.
   
   
2. When you're done, write an entry in your journal about this activity, and any insights you may have learned from this exercise. Even if you've done this exercise in the past, did you find out anything new? What was it?
   
   
3. Since our work will depend on your knowledge of basic Excel operations, please have this assignment done before our next session on Monday. Thanks
   
   
4. Click Here for the initial Excel 2007 tutorial

Course Textbooks: You'll want to get a headstart on the course by purchasing the textbooks we'll be using. There are two books. The first textbook is the guide for our lab work on the computer.





Microsoft Office Excel 2007: Comprehensive Concepts and Techniques, Gary B. Shelly, Thomas J. Cashman, James S. Quasney, ISBN 10: 1-4188-4344-X, Publish date: August 23, 2007, 816 pages, Softcover. If you can find a used copy of Microsoft Office Excel 2007, buy it quickly without hesitation!!


Our major assigned work will come from the following text:







CaseGrader: Microsoft Office Excel 2007 Casebook with Autograding Technology, Thad Crews, Chip Murphy, ISBN 10: 1-4239-9823-5, Publish date: August 1, 2007, 64 pages, Softcover.

If you've been with me before, you'll recognize that this text is based on an exciting technology! It will provide us with an Internet tool for learning in Excel. After working hands-on with spreadsheet files, you'll then be able to have your work automatically checked and graded via the Net. That will happen immediately upon submission, and you'll be able to see what you got right and what you got wrong, and you'll be able to make corrections immediately!

Do NOT buy a used copy of the CaseGrader! You will have to buy a brand new copy of this text. Once used, KEYCODES are NOT reusable.

I recommend that you get the texts as soon as you can. They should already be available at the bookstore. Our in-class labs will be coming from the Excel 2007 textbook (You'll certainly want it for study and review purposes), and our exercises from the Internet and from the Casegrader.

As you probably already know, you may order your books from Barnes and Noble, Amazon or Half.com. However, in the past there have been problems with the timely receipt of books from these sources. I would stick with the bookstore. It's worth the extra few dollars to know that you have the books in hand.

Blogger Observations: One of Blogger's nice features is that not only can you use regular, plain text, but you can also include HTML coding within the text to add hyperlinks, graphics, as well as to make the text stand out in interesting and unusual ways. We'll use just enough HTML to be dangerous, and you'll be able to use it to enhance your journal.

If you're the adventurous type: Change your Template in the Blogger Template screen, or go to Blogskins and check out another entire set of Templates. Do a Google search on 'Blogs' to see what other blogwriters have done with their sites. Or you can visit sites using the "Next Blog" button in the 'Blogger NavBar' at the top of this journal page. If you really want to dig into your template, find the spot where you can personalize your own links bar to include some of your favorite Internet sites.

A part of your grade in this class will depend on the quality of what you write in your journal, as well as the timeliness of your entries. Make this a regular part of your routine during the term.

And, oh yes, you can communicate with me at anytime via email. My email address is: jorisch@yu.edu or jorisch@gmail.com.

Consider this your invitation to take part in an unusual, interesting and exciting learning experience, one that I'm sure you'll enjoy!!

Let the fun and the 'Excelling' begin!
HJ


To Top of Page1