TIM hw1

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Prepared by: Rany Polany (2012) & Daniel Core (2009) UCSC, Baskin School of Engineering

Table of Contents

Introduction ............................................................................................ 1

General Excel Questions ........................................................................ 2

1. Where can I use Microsoft Excel and print if I do not have my own computer? ........ 2

2. Are there any general-purpose tools in Excel?................................................................ 2

3. Are these tools already activated in the version of Excel on my computer? ................ 2

4. How do I install the “Solver” and “Analysis Toolpak” Add-in toolkits? ..................... 3

5. Can Excel be used as a programming environment? ..................................................... 5

Macintosh Related Questions ................................................................ 6

1. Is there a good website that teaches me how to use Excel on a Mac? ........................... 6

2. How do I access the Visual Basic editor if I have a Mac? .............................................. 6

3. How do I access the analysis tool pack/Solver in Office 2008? ...................................... 6

4. How do I add Graphical User Interface elements to a spreadsheet? ............................ 6

Additional Web References: .................................................................. 7

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 1

Introduction This handout is meant to provide a very broad overview of some of the frequently asked questions to get

you started in Excel. This is by no means a substitute for getting a good book on how to become a power

user of Excel.

1. Sample of Recommended References

 Excel 2010 For Dummies,(ISBN-10: 0470489537), or, similar version (e.g., Excel 2007).

 Excel VBA Programming For Dummies, (ISBN-10: 0470503696), or, similar version.

 Free Microsoft Online-Video Training: http://office.microsoft.com/en-us/excel-help/CH010369467.aspx?CTT=97

 Get to know Excel 2010: Create your first spreadsheet" - click "Start this Course" on webpage to watch Free video. http://office.microsoft.com/en-us/excel-help/get-to-know-excel-2010-create-your-first-spreadsheet-RZ101773335.aspx

 “Get to know Excel 2010: Create formulas” - click "Start this Course" on webpage to watch Free video. http://office.microsoft.com/en-us/excel-help/get-to-know-excel-2010-create-formulas-RZ101862712.aspx

 Getting Started with VBA in Excel 2010 http://msdn.microsoft.com/en-us/library/ee814737.aspx

Prerequisite Excel Skills:

Entering Data

Copying/Pasting Data

Formatting Cells

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 2

General Excel Questions

1. Where can I use Microsoft Excel and print if I do not have my own computer?

The UCSC Computer Lab directory and offerings are summarized at this webpage:

http://its.ucsc.edu/computer-labs/descriptions/all-labs-summary.html

 MAC software availability: http://its.ucsc.edu/computer-labs/software/mac.html

 PC/Windows software availability: http://its.ucsc.edu/computer-labs/software/pc.html

2. Are there any general-purpose tools in Excel?

In the “formula” toolbar there are several important classes of functions. These can be used by

going to the insert menu, and selecting function. From there you can select the individual

function to be inserted. To use a function place an = at in the cell, followed by the function. The

syntax for the function can be accessed at the link below, or the help menu in Excel.

 Financial Functions: Provide built in tools for calculating financial values including, Net Present Value.

 Lookup Functions: Allow for finding a value that meets criteria within a large group of cells. The main functions for this are Lookup, and Match

 Math/Statistical Functions: Are used to calculate values, and return singular values. Functions include Average, Sum, Absolute Value, Minimum, and Maximum.

For more information on all the functions available in Excel go to:

http://office.microsoft.com/en-us/excel/CH062528191033.aspx

3. Are these tools already activated in the version of Excel on my computer?

The functions in the “formula” toolbar are pre-installed

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 3

4. How do I install the “Solver” and “Analysis Toolpak” Add-in toolkits?

o Enable the Solver Add-In.

Windows OS instructions:

Useful video guide:

http://office.microsoft.com/en-us/excel-help/video-install-and-activate-the-

analysis-toolpak-and-solver-VA101956375.aspx?CTT=1

 Excel 2010:

http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010342660.aspx?CTT=1

 Excel 2007:

http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010021570.aspx?CTT=1

 Excel 2003:

http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP001127725.aspx?CTT=1

Mac OS instructions:

 Excel 2011: Office for Mac 2011 Service Pack 1 (14.1.0) needed.

Download: http://support.microsoft.com/kb/2525412

 Excel 2008: http://www.solver.com/mac/dwnmacsolver.htm

o Installing the “Excel Analysis ToolPak” and “Analysis Toolpak – VBA”.

Windows OS instructions:

 Excel 2010:

http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP010021569.aspx?CTT=3

 Excel 2007:

http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP010021569.aspx

 Excel 2003:

http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP001127724.aspx

Mac OS instructions:

 Excel 2011: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=17198

 Excel 2008: Not Available. VBA is not Supported.

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 4

To access many of the advanced features available in Excel, the Analysis Tool Pack and Solver-

Addin need to be installed. This can be done by going to the Tools menu  Add-Ins. Then select

the checkboxes for Analysis ToolPack, Analysis ToolPack-VBA, Solver-Addin.

Load the Add-in Instructions: 1

The Solver Add-in is a Microsoft Office Excel “add-in” (A supplemental program that adds

custom commands or custom features to Microsoft Office) program that is available when you

install Microsoft Office or Excel.

\To use the Solver Add-in, however, you first need to load it in Excel: 2

1. Click the Microsoft Office Button (Excel 2007) or the File Menu (Excel 2011), and then click Excel Options.

2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.

3. Select the MANAGE: EXCEL ADD-INS. Then Click Go. 4. In the Add-Ins available box, select the Analysis ToolPak, Analysis ToolPak-VBA,

and the Solver Add-in check box, and then click OK.

1 Source Instructions: http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010021570.aspx

2 Source of Instructions: http://office.microsoft.com/en-us/excel-help/load-the-solver-add-in-HP010021570.aspx

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 5

 Tip: If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.

 If you get prompted that the Add-in is not currently installed on your computer, click Yes to install it.

5. After you load the Add-in’s, the Solver is available in the Analysis group on the Data tab.

5. Can Excel be used as a programming environment?

Excel has the ability to incorporate Microsoft’s Visual Basic for applications (except Excel 2008

for Mac this feature was not available). Visual Basic is an easy to use language that allows users

to create a dynamic front end, while providing for powerful yet simple manipulation of data. It

can be used to connect multiple workbooks, and spreadsheets. Also because it is built into Excel

it has the ability to use it’s built in functions. Because of these advantages it is well suited for

solving business problems.

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 6

Macintosh Related Questions

1. Is there a good website that teaches me how to use Excel on a Mac?

You can find many quality videos tutorials and guides on the Internet. One option that is free is

to use Youtube.com which has many, high quality, and free publicly available video tutorials.

For example:

Title: Introduction to Excel for Mac – 1

Uploaded by gauravnjoshi on Sep 14, 2009.

Website address: http://www.youtube.com/watch?v=9GPUwnDoXOE&feature=related

The author shows you how to build your data columns and then graph the data points.

Title: lynda.com Tutorial | Excel for Mac 2011 Essential Training—Creating line charts

Uploaded by lyndapodcast on Nov 1, 2010

Website address: http://www.youtube.com/watch?v=3MUMOesp3fg

The authors shows you how to graph using line chart and, how to fix common issues in charting.

Title: Lesson 3 - Add titles and labels to a chart

uploaded by officeformac on Feb 22, 2011.

Website address: http://www.youtube.com/watch?v=daopWElQOrg&feature=related

The author shows you all the aspects of how to clearly label charts.

Once you find a video author you like, look through their library of free videos.

2. How do I access the Visual Basic editor if I have a Mac?

The directions for accessing visual basic in Excel 2004 or earlier, and Excel 2011 are the same as

on a PC. However, if you have Excel 2008 support for visual basic was removed, there you will

need to use the lab Windows PC computers or acquire a copy latest Excel 2011.

3. How do I access the analysis tool pack/Solver in Office 2008?

The Analysis Tool Pack and Solver were removed from Mac Office 2008 because it relies on

Visual Basic. Therefore, Excel 2008 for the Mac is not recommended for TIM-125/225, due to

lack of Visual Basic functionality.

In Excel 2011 for Mac, it was added back. You will need Service Pack 1 for Excel 2011.

Therefore, to use these functions you will need to:

1. Use the lab computers at UCSC or 2. Acquire a copy the latest Excel 2011 and install Service Pack 1.

Download Service Pack 1 for Excel 2011: http://support.microsoft.com/kb/2525412

4. How do I add Graphical User Interface elements to a spreadsheet?

On a Mac the toolbox is located in a different location than on a PC. The toolbox can be

accessed through View  Toolbars  Forms.

TIM-125/225: MOT II Supply Chain Management

Getting Started with Excel

Page 7

Additional Web References:

http://excelsemipro.com/2010/11/excel-2011-ribbon-screen-shots/

http://www.youtube.com/watch?v=8DjUCUY9xOg

http://www.sjsu.edu/depts/geography/resource/swdocs/xl-guide/edit/celref.htm

http://peltiertech.com/Excel/SolverVBA.htm