1. Put your name in cell B1 of this tab.
2. On the Problem 1 tab, use Left, Right, Mid, Concatenate and Text to Columns to
reformat the given information in columns A and B into the below formats in columns H and I.
Cell Formats and Styles
Formatting can be applied to a cell or its contents (or even part of its contents) in
Microsoft Excel. This formatting can be applied using the same normal text settings found
in Microsoft Word, but also less commonly
Splitting Windows and Freezing Panes
So far we have worked in one worksheet and window at a time. Excel has expanded
perspective capabilities so that you can either improve the view while working in one
worksheet, or show more than one worksh
Series and Custom Lists
When performing calculations in Excel, you can translate the formula across a series of cells
the fill handle, which is a small black plus sign that shows at the bottom right hand corner
of a cell. As this fill handle
Its important when building an Excel worksheet to know your audience. Who will see and
use the spreadsheet, and for what purpose(s)? Are you creating an Excel spreadsheet that
contains formulas that you dont want anyone to be able to
Microsoft Excel functionality includes a mechanism to add notes into a worksheet. The uses
for this are myriad, and include adding information relevant to the cell noted for other
worksheet users; adding notation that does not readil
Cell Formats and Styles
Now that youve formatted the contents of cells, now we will format the cells themselves.
Buttons within the Ribbon let you set formatting to a cell you can justify the layout, choose
the color that fills the cell o
Macros - continued
The counter that we used in the assignments in Lecture 19 (k) is what is known as a variable in
programming. It varies between a starting value and ending value we used 1 and 9 or 1 and
21 in the assignments.
Lets look a
Sometimes you have a larger set of data in your Excel sheet than you need to examine or work with.
Excel has filtering options to be able to reduce your data to a subset. It is also possible to filter a filter to
further refine your s
We continue our look at functions with logic.
IF gives you alternative results depending on a condition (also SUMIF and COUNTIF)
AND allows you to check that several criteria are all true
OR checks whether any cr
Microsoft Visual Basic
Weve seen some brief examples of Visual Basic in our previous lectures. Visual Basic is the
programming language used for writing macros. If you record a macro like we learned in
Lecture 19, the code behind the macro is
Microsoft Visual Basic continued
Open the Assignment 21.[Your Last Name].xlsm file that you saved to your computer. Click on
the File tab, then choose Save As. Save the file to your computer as Assignment 22.[Your Last
Name].xlsm. Were doing this now to a
A macro is a series of instructions that Excel follows from a single command.
The macro can be run, or started, in various different ways. You can set it to run from the
keyboard by giving it a control-plus key combination; by insertin
The traditional way of displaying data in Excel is, of course, in worksheet format.
With very large datasets, this format can problematic trying to see all the data for a row or
overwhelming trying to view one row in the midst of all th
Animation & Iteration
In Excel, if you write a formula and refer to the cell that youre writing the formula in, you
create what is called a circular reference. So if you were in cell A5, and wrote a formula such as
=A5+3, you would be telling
Excel allows you to create cross-tab (or cross-tabulation) analysis in what it calls a Pivot
Pivot tables can be one, two or three-dimensional. You can use multiple statistical analysis
and summary options tools. You can i
Goal Seek and Solver
You can use Excel to solve optimization problems or mathematical problems.
For simple problems, Goal Seek is used, while Solver is used for more complex problems.
1. Open a new, blank Excel worksheet.
2. Click in cell A1
Sorting, Subtotals and Outlines
One of the most functional uses in Excel is the ability to analyze the data. This lecture
discusses some of the analytical tools of Excel, included simple and advanced sorting
techniques as well as the use of s
Weve looked at PivotCharts, now well look at regular charts. Well demonstrate how you
can use Excel to store data and then create graphs, and well also look at some more
obvious features (such as Chart Type) as well as some of Excels m
Excel allows you to restrict data entry to cells to numbers, dates, or values from a list, then
lets you validate the information being entered to ensure it matches the restrictions set.
Setting a Validation Rule on a Cell
We continue our look at functions with lookup and reference functions.
Lookup & Reference Functions
The final group of Excel functions dealt with in this lecture series are called Lookup &
Reference. These let you search for a value
We continue our look at functions with nesting.
One function can be used inside another. This is called nesting.
For example, in the last assignment we used NOT to reverse the values in the adjacent column.
Functions are used in Excel to perform calculations automatically, without it being
necessary for the user to know they are done.
Excel functions have a set structure beginning with an equals sign, followed by the
function name and