MS Excel 2016 Concepts What is Excel Excel

MS Excel 2016 Concepts

What is Excel • Excel is a spreadsheet for storing, organizing and analyzing information • Primarily numeric, but also textual and graphic • A spreadsheet is a layout consisting of a grid of rows and columns of cells • A cell is the intersection of a row and a columns • Why use Excel? • • • Excel has very powerful computational capabilities Excel has very good data visualization (graphics) capabilities Extremely well suited for working with and understanding numbers AP/ITEC 1010 – Information and Organizations January 8, 2017 2

The Excel Interface • • As a member of the MS Office family of tools, Excel shares the same approach to UI we already looked at when learning about Access See below the Home tab of the Ribbon Because some groups have many options, a tiny arrow in the bottom right corner of the group indicates more options are available Remember, you can customize the UI by adding tools you use very often to the Quick Access Toolbar AP/ITEC 1010 – Information and Organizations January 8, 2017 3

Excel Views • Excel offers three views (selected from the View tab): • • Normal View – used for most work in Excel Page Layout – shows the data as it would appear in print (includes margins, headers, footers, etc. • • Very useful for seeing how your output would look like Page Break Preview – similar to Page Layout, but shows only where page breaks occur • • Very useful for ensuring page breaks are in a reasonable place If not, you can adjust cell sizes, fonts, etc. so that printed output makes sense AP/ITEC 1010 – Information and Organizations January 8, 2017 4

• • Identifying Cells The grid has: • • rows numbered (1 -1, 048, 576) and columns labelled alphanumerically • A-Z, AA-AZ, BA-BZ, …ZA-ZZ, AAA-AAZ, etc. Cell references are: • • • Relative: B 5 Absolute: $B$5 Mixed: $B 5 or B$5 Sometimes a group of cells must be referenced • • • A 1: A 5 is a range of cells Ranges can span multiple columns and rows Ranges can be disjoint (not adjacent) • selected holding down the CTRL key Cells on a different sheet: Sheet 2!B 5 AP/ITEC 1010 – Information and Organizations January 8, 2017 5

Cell Content • A cell can contain four kinds of content: • • A value (numeric or other) A label (text to help understand the meaning of adjacent cells) A formula (starts with = and produces a calculated value) A function (starts with = followed by the function name, and its arguments) • Cells normally show the end result of whatever is in the cell • You can show the formula or function vs. the result by toggling CTRL` AP/ITEC 1010 – Information and Organizations January 8, 2017 6

Values and Labels • Values are numbers, text or dates • By default, they have no specific format • Since numbers represent different concepts, such as money, time, statistics, accounting values, etc. there are many ways to show (format) a number • More about formatting later • Labels are really text values • • Difference is they explain other cells You can also change how text looks (font, color, size, etc. ) AP/ITEC 1010 – Information and Organizations January 8, 2017 7

Formulae • Start with = • Contain arithmetic expressions • Operators: • • • + for addition • Instead of typing in a relative cell reference, you can click on that cell - for subtraction * for multiplication / for division ^ for exponentiation • Operands can be numeric values, functions, or cell references AP/ITEC 1010 – Information and Organizations January 8, 2017 8

Functions • Functions are essentially pre-written formulae • Syntax is: =FUNCTIONNAME(arg 1, arg 2, …) • Arguments can be values, formulae, functions or cell references • Click the fx symbol on the cell edit toolbar • You get the screen at right • • • It is a wizard guiding you to write the correct syntax Provides explanations for the functions When cells or ranges are called for, you can select them in the spreadsheet or type them in AP/ITEC 1010 – Information and Organizations January 8, 2017 9

Function Arguments • After selecting a function you see a screen for entering the arguments • Arguments must match the criteria on the screen • Click the link at bottom left for online help with the selected function AP/ITEC 1010 – Information and Organizations January 8, 2017 10

Frequently Used Functions • • • Financial • E. g. : ACCRINT, NPV, PMT Date & Time • E. g. : DATE, DAY, MONTH, YEAR… Math & Trig • E. g. : SIN, COS, EXP, SUM, ROUND Statistical • E. g. : AVERAGE, MEDIAN, COUNT Text • E. g. : LOWER, UPPER, TRIM Logical • E. g. : IF, AND, OR, NOT AP/ITEC 1010 – Information and Organizations January 8, 2017 11

Cell Comments • When a label does not provide enough clarity, you can add a cell comment • Right click the cell and select Insert Comment • The Windows user name is automatically added to help attribute comments • You can delete it, of course • A cell with a comment has a little red triangle at top right to identify the presence of a comment AP/ITEC 1010 – Information and Organizations January 8, 2017 12

Formatting Basics • Formatting allows users to customize the appearance of the spreadsheet • We have five kinds of formatting • • • Number formatting Fonts Borders Alignment Cell, column and row formatting AP/ITEC 1010 – Information and Organizations January 8, 2017 13

Formatting Numbers • • • By default any cell is categorized as “General” The drop down shown right allows you to pick different ways of formatting numbers Formatting refers to presentation, not content • • E. g. : you enter 123 and format as currency • • • Content remains 123 Cell shows $123. 00 Toggle using CTRL` to see the difference A few shortcuts for number formatting are in the Numbers toolbox AP/ITEC 1010 – Information and Organizations January 8, 2017 14

Fonts & Borders • Font changes apply both to text and numbers • Because numbers are ultimately shown in their text form • You can change: • • • Font face and size Font color Font attributes (such as bold, italic, underline) • You can place a border around a cell or a group of adjacent cells • The grid you see in Excel is not printed, so if you want so see some or all of the grid in print, you need to set borders AP/ITEC 1010 – Information and Organizations January 8, 2017 15

Alignment • • Alignment refers to how text is positioned vis-à-vis cell borders • • • Left, center, right Top, middle, bottom Angled You can indent By default, if cell content is larger that cell size, you see only what fits • • The cell acts like a moving frame, the entire content is still there, but not visible • When the cell height changes, it does so for the entire row; the spreadsheet always remains a grid You can set a cell to wrap text; that will increase the cell height but not width, such as to show the entire content You can merge multiple adjacent cells, as long as they form a rectangle AP/ITEC 1010 – Information and Organizations January 8, 2017 16

Cell, Row & Column Formatting • You can change the size of cells as follows: • Change the height of a row – changes the height of every cell in the row • • • Drag the line separating row labels, or Select the row, right click and select Row Height, in points (=1/72 in), max 409 Change the width of a column – changes the width of every cell in the column • • Drag the bar separating column labels, or Select the column, right click and select Column Height, in characters, max 255 • You can also change the fill color and pattern AP/ITEC 1010 – Information and Organizations January 8, 2017 17

All Formatting in One Place • Over and above the methods indicated before, Excel has a single dialogue screen for all formatting needs: • • • Click Format in the Cells toolbox Many formatting options are in the menu Click Format Cells for the full set of options • In the same toolbox you have tools for inserting and deleting cells, rows, and columns • • Usually we delete entire rows or column When you delete individual cells, adjacent cell content will be shifted in place of the existing cell – handle with care! • Deleting cells is different from deleting cell contents AP/ITEC 1010 – Information and Organizations January 8, 2017 18

Cell Format Window • Here is the window for changing all aspects of formatting for a group of cells • Select the cell (or group of cells) before opening this window • Image on the right shows the Fill tab • Hint: use color filling as an indication of cell content (different colors formulae, parameters, calculated fields, data entry fields, etc. AP/ITEC 1010 – Information and Organizations January 8, 2017 19

Clearing Cell Content • What shows in a cell is the result of both content and format • Deleting cell content (as opposed to the cell itself) is called Clearing • • Select Clear from the Editing toolbox You can then chose to delete everything, or specific elements of the content AP/ITEC 1010 – Information and Organizations January 8, 2017 20

Copying Cells • Copying cell contents is done multiple ways: • • • Select source cell(s), and use Copy in the Clipboard toolbox Select source cell(s) and use CTRL/C to copy Select source cell(s), right click and select Copy • You can copy multiple cells at the same time • Select by starting in the top left cell and dragging the cursor down and right • After selecting Copy (any method) the copied cell(s) are surrounded by a dotted line border until you paste • Click ESC to cancel the selection to be copied AP/ITEC 1010 – Information and Organizations January 8, 2017 21

Pasting Cells • Because cells contain values, formulae and formatting, when you paste copied data, you can do any of the following: • • • Paste only the cell formatting, such as font color or fill color (and not the contents of the cells). Convert any formulas in the cell to the calculated values without overwriting the existing formatting. Paste only the formulas (and not the calculated values). Several other variations, such as pasting as image, or transposing If content includes cell references, pasting transposes the relative part of the reference • • Copying cell A 3 to B 3 changed references to A 1 and A 2 That is why we have absolute cell references • to allow copy without transposing AP/ITEC 1010 – Information and Organizations January 8, 2017 22

Pasting Multiple Cells • When pasting multiple cells, if the destination selection is different in size than the source, the result will occupy the same number of cells as the source, matching the top left corner • In practice you can paste selecting only the top left cell of the destination • Note that cell size is not part of the formatting • Special option for pasting with changing the cell size AP/ITEC 1010 – Information and Organizations January 8, 2017 23

Charts • One of the strong points of Excel is the ability to create graphical charts to show numbers in a graphical format • The Excel capabilities are quite extensive in this sense, but we will cover only the basics • A chart is a visual representation of data • • Numbers are represented by graphical artefacts such as bars, lines, pie slices, etc. Colors are used to distinguish values • • A legend is included to help associate numbers with values If rows and columns have labels, the chart wizard uses them in the legend AP/ITEC 1010 – Information and Organizations January 8, 2017 24

Create a Chart in 5 Easy Steps • The easiest way to create a chart is: • Select the data you wish to show graphically • • • Click on the Insert tab in the ribbon • Click OK Click the Recommended Charts button From the left pane of the pop up window click on kind of chart that you find most suitable • You can edit various aspects of the chart later AP/ITEC 1010 – Information and Organizations January 8, 2017 25

Graphs for a Sequence of Numbers • There are three basic ways of showing a sequence of numbers (without totals) • • Bar Chart • • Each number is a bar with a length proportional to the value Bar can be vertical or horizontal Line Chart • • Each number is a graph point at a height proportional to the value, and the points are connected with a line Space below line can be filled Pie Chart • Each number is a sector of a circle, with the size of the sector proportional to the value When totals are included, the line chart does not work AP/ITEC 1010 – Information and Organizations January 8, 2017 26

Graphs for a Table of Numbers • Two dimensional number sequences benefit even more from graphs • There are two basic representations: • Clusters, where bars representing the values are shown side by side • Stacks, where bars representing the numbers are shown on top of each other • Both can be horizontal or vertical AP/ITEC 1010 – Information and Organizations January 8, 2017 27

Totals • Totals do impact the graph in multiple ways: • • Certain representations do not make sense for totals Totals significantly increase the scale, making it harder to see the values for the individual data points • Should we include totals in the graph? • It depends on what you want the graph to show • When the focus is on comparing values along one axis between themselves, and columns (or rows) to each other, we tend to eliminate totals • When we are interested in how each set of values contributes to the total, we have to include totals AP/ITEC 1010 – Information and Organizations January 8, 2017 28

Editing a Chart • Once the chart was created, you can click anywhere in the chart area to select it • The chart will get round handles, and three little icons will appear • • • Handles are for resizing • Funnel icon allows you to change the data points included in the chart + icon allows you to add and remove elements Brush icon allows you to change the style and color palette of the chart (within the same chart type) AP/ITEC 1010 – Information and Organizations January 8, 2017 29

Chart Elements • • Axes: labels describing the values Axis Title: Text describing the axis Chart Title: Text describing the chart Data Labels: superimposes the values over the graphical representation • Usually causes a very crowded chart Data Table: adds the actual table of data that is the source for the graph Error Bars: for statistics Gridlines: Lines helping to evaluate values Legend: Correlates values to graphics AP/ITEC 1010 – Information and Organizations January 8, 2017 30

Style & Color • Style changes are variations of the same kind of chart • Color changes are done in correlated groups (palette) AP/ITEC 1010 – Information and Organizations January 8, 2017 31

Filtering Data • You can focus on specific subsets of data by using the filter tool to turn on and off certain data points AP/ITEC 1010 – Information and Organizations January 8, 2017 32

Other Edits • • When a chart is selected, you can edit the chart title and axis titles by clicking on them and editing directly You can select the legend, axis labels and data graphs and right click for additional edit options: • • You get an option to format the selected item You get other options applicable to the entire chart • • • Delete the chart Change the font Reset chart design to basic style Change the chart type Select different data range AP/ITEC 1010 – Information and Organizations January 8, 2017 33

Modeling in Excel • When you change the content of a cell in Excel, the moment you hit the Enter key any other cell who is dependent on the changed cell also changes value • This feature makes Excel an excellent modelling tool • Instead of using numbers for calculations, where reasonable you use values stored in another cell (i. e. , you use a parameter) • Parameter cells are usually absolute references, because the parameter is at a fixed location and using its value anywhere in the spreadsheet needs to find the parameter in the same place • We usually highlight parameter containing cells in some visible way to make it clear its content is used throughout the spreadsheet • To model a different scenario, we simply change one or more parameters AP/ITEC 1010 – Information and Organizations January 8, 2017 34

A Modeling Example AP/ITEC 1010 – Information and Organizations January 8, 2017 35