Microsoft Excel 2016 n Spreadsheet program n n

Microsoft Excel 2016 n Spreadsheet program n n n Part of Microsoft Office package Organizer of data Big computing power Display charts and graphs Small databases Differences from previous versions n n 19 Aug 2016 Excel 2003: menus are completely reorganized Excel 2007 2010 2013 2016 Mac: almost none 1

Cells Work-book n n Work-sheets, cells Cells n n n References, columns and rows Name box and formula bar Sheet tabs Autocalculate Sheet operations n Ø Ø Right-click 19 Aug 2016 Insert… Rename Tab Color… Move or Copy… 2

Types n Numbers’ formats Ø n n File Options Advanced Editing options Mac: does not exist, you must change configuration for entire computer in Ø System Preferences -> Language & Regions -> Advanced… Types Ø Ø 19 Aug 2016 Home Numbers Mac: Format menu Cells… 3

Types n Value (aligned to the right) n n n Formula n n Everything which begins with = Text (aligned to the left) n n 19 Aug 2016 Number: 23 or 56, 45 or -0, 4 or , 23 or (24) Currency: € 34, 2 or 9€ or 0, 7 € Date: 3 Mar or 7/10/05 or Jun 2005 or 3 -5 Time: 2: 34 am or 17: 28 Percentage: 34, 5% Everything else: alphanumeric characters and symbols Use apostrophe to convince Excel 4

Data entry suggestions n n n Use the cells format dialog box before typing values! Pay attention to what Excel understands, especially dates! Pay special attention when converting between numbers and percentages! n n n 19 Aug 2016 0, 02 2% 2 200% 0, 56 % 0, 0056 5

Autotasks n Autofill Ø drag Repeat the same value n Increment the value When not satisfied of Excel behaviour: Ø CTRL + drag Ø Mac: ALT + drag n n 19 Aug 2016 6

Importing data Ø Home Clipboard Paste Ø Ø Paste Value Paste Special… Ø Data Get External Data From Text n Fixed fields or Delimited fields Restoring the Get External Data in the ribbon n 19 Oct 2017 7

Formula n Choosing the cells n n n Operations n n n Cell pointing with mouse Cell name with keyboard Mathematical operations: + - * / ^ Parenthesis Errors n n n 19 Aug 2016 #DIV/0! #NAME? #REF! #NULL! #NUM! #VALUE! Division by zero Wrong cell name Wrong cell reference Function’s arguments missing or too many Wrong number in a function or too large number Wrong type in a function 8

Formula references n n Relative A 1 Dragging the formula n n Ø n Absolute $A$1 Partial absolute $A 1 A$1 F 4 References from other sheets n n 19 Aug 2016 sheetname!A 1 [filename. xls]sheetname!A 1 9

Formulas Function Library Warning: names are language dependent! Use the conversion table if studying in other languages Ø Math & Trig Ø Ø Ø 19 Aug 2016 SQRT, EXP, PI, LN, LOG ROUND, ROUNDDOWN, ROUNDUP ABS, RANDBETWEEN MAX, MIN, SUM, AVERAGE SUMIF, COUNTIF, AVERAGEIF 10

Functions Ø Logical Ø Ø IF(test; value if true; value if false) AND is ( ) * ( ) , OR is ( ) + ( ) NOT Text Ø Ø 19 Aug 2016 LEN RIGHT, LEFT, CONCATENATE, REPT 11

Slide only for ISDM 27006 Date functions Ø Date & Time Ø Ø n n NOW, TODAY WEEKDAY Serial number format and +/- operations Date exact calculation Ø Ø Ø 19 Aug 2016 DAY, MONTH, YEAR DATEDIF (the undocumented function) 12

Slide only for ISDM 27006 Financial functions Ø Financial n net present value Ø n XNPV internal rate of return XIRR n existence and uniqueness n n Loan n n 19 Aug 2016 T. A. N. Tasso Annuale Netto (Yearly Net Rate) T. A. E. G. Tasso Annuale Effettivo Globale (Yearly Real Global Rate) 13

Slide only for ISDM 27006 Mortgage loan n constant payments and constant interest rate Ø n PMT, IPMT, RATE, NPER adjustable interest rate n n n 19 Aug 2016 Must build the table Constant payments and adjustable length Adjustable payments and constant length 14

Functions Ø Statistical Ø Ø 19 Aug 2016 AVERAGE, VAR. S, STDEV. S NORM. DIST, NORM. INV 15

Slide only for ISDM 27006 Visual Basic for Applications n Easy functions to expand Excel n n n User-defined functions If Then Else Working with ranges Save the file as. xlsm ALT+F 11 n 19 Aug 2016 on notebook you might have to press Fn 16

Slide only for ISDM 27006 Visual Basic for Applications Ø Ø Insert Module Mac: Ø Open Microsoft Excel Objects, click on any sheet, go with the mouse away from the sheets, right-click -> Insert -> Module. Then open Module, click on it and write code in module’s sheet on the right. Ø Do not write code in sheets Ø Reset button 19 Aug 2016 17

Slide only for ISDM 27006 Visual Basic for Applications n Function declaration n n Optional parameters Comments Msg. Box If Then Else n n 19 Aug 2016 Else. If Nested Ifs 18

Slide only for ISDM 27006 Visual Basic for Applications n Internal functions: Ucase, Left, Sqr n Range as parameter n n n . Cells. Count. Rows. Count. Columns. Count For Each Next Summing 19 Aug 2016 19

Data Forecasting What-If Analysis Ø Ø Goal Seek… Scenario Manager… Ø Ø Add Summary Assigning cell’s name Data table… n Select entire table n One parameter: Column input cell n Two parameters n Ø 19 Aug 2016 20

Solver example n My profession is writing books and giving seminars. n n n I want to maximize my earnings. The obvious choice would be to give as many seminars as possible. However, in order to be called by universities for seminars I need to write at least one book every year! Moreover, I have further constraints: n n Each book requires 300 working hours and I earn 15000 euro. Each seminar requires 20 working hours and I earn 10000 euro. I do not want to work more than 1600 hours per year I must give at least 4 seminars per year in order to advertise my books I have only 18000 euro funding and each book uses 500 euro, while each seminar uses 2500 euro How to maximize my earnings satisfying my constraints? 19 Aug 2016 21

Solver Ø File Excel Options Add-Ins Go Solver § § n n Ø Solver – Risolutore Mac users: Tools Add-Ins Write variables, possibly using cell names Write objective and constraints Data Analysis Solver Ø Ø Ø 19 Aug 2016 set variables (Variablenzellen, Celle variabili) set objective (Ziel, Obiettivo) set constraints (Nebenbedingungen, Vincoli) set constraints for integer and binary variables Solve (Lösen, Risolvi) Answer report (Antwort, Valori) 22

Printing Ø Page Layout Page Setup Ø Page Ø Ø Ø Margins Ø Ø Print area: Print Gridlines View Workbook Views Page Break Preview v Ø Center on page Header/Footer Sheet Ø Ø Orientation Scaling For Mac: Page Layout Breaks File Print 19 Aug 2016 23

Portable Document File format n Portable Document File. pdf Ø PDF advantages Adobe Acrobat Reader and Adobe Acrobat File Save As… File Print choose printer Adobe PDF n Protecting documents with Adobe Acrobat n n Ø n 19 Aug 2016 … is removed from this course! 24

Saving in other formats n Excel document. xlsx Macro-enabled workbook. xlsm n Excel 97 -2003 document. xls n Plain Text. txt. csv n n 19 Aug 2016 Always close Excel after saving in plain text 25

Automatic format Ø Home Styles Format as Table Ø Ø My table has headers Design Table styles Clear Data Filter Home Styles Conditional Formatting n Ø Apply a rule which looks good Manage rules… Edit Rule… Ø 19 Aug 2016 Reverse Icon order 26

Chart building n Ø Select cell range Insert Charts Choose chart type and subtype Chart Tools Design Ø Ø Ø 19 Aug 2016 Data Switch Row/Column Data Select Data Add/Edit Chart Layouts Chart Styles Location 27

Chart Tools Design Add Chart Element Ø Axes More axis options… Ø Ø Ø Text Options Axis Titles / Chart Titles Legend / Gridlines Data Labels / Data Table double-click on an element Ø Ø Ø 19 Aug 2016 Plot area Series Element of a series 28

Chart types n n Multiple selection: CTRL/cmd + mouse 1 data series n n Columns and bar plots Line and Area (not a math graph!) Pie Multiple data series n n n 19 Aug 2016 Clustered and stacked columns/bar/lines/area Scatter (only tool for mathematical graphs) Bubble 29

Extra Ø n Insert Illustrations Shapes Insert Shapes n Non trasparent text box with arrow Sheet protection except some cells Ø Review Changes Ø Ø Allow Users to Edit Ranges New Mac: Ø Ø 19 Aug 2016 select free range and Format Cells Protection uncheck Locked Protect Sheet… 30

Extras n Hide and unhide Ø Ø n Home Cells Format Visibility Hide & Unhide… right-click Hide/Unhide… Split and freeze pane n Ø Ø Put cursor in the top left of the lower pane View Window Freeze Panes Review Comments New Comment 19 Aug 2016 31

Extras n List (small database) Ø Data Sort Ø Ø n Add Field Data Filter Hyperlink Ø Ø Select area, right-click Remove Hyperlink Mac: either select exactly the cells with hyperlinks, or copy, paste values on another column and remove original column 19 Aug 2016 32

Pivot. Table n Ø n select a well-structured table Insert Tables Pivot. Table Drag items from Pivot. Table Field List Ø n Ø Ø Ø Left-click Value Field Settings Filter and Sort Options Group Field To restore fields’ pane: click on pivot table Analyze press Field List Options Tools Pivot. Chart Ø 19 Aug 2016 Mac: Pivot. Chart does not exist 33

Statistics with Excel Add-ins Ø File Excel Options Add-Ins Go Analysis Tool. Pak § Ø Analyse-Funktionen – Strumenti di Analisi Data Analysis Data Analysis Ø Ø Descriptive Statistics Histogram Ø Ø n 19 Aug 2016 (Popolationskenngrößen) Display Chart Right-click Format Data Series No Gap Bin range (Klassenbereich) 34