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