1 / 20

Excel Basics

Excel Basics. Agenda. Definition Uses Features Proficiency (self test) Practical examples Spreadsheet best etiquette Excel shortcuts. Definition.

foerster
Download Presentation

Excel Basics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Basics

  2. Agenda • Definition • Uses • Features • Proficiency (self test) • Practical examples • Spreadsheet best etiquette • Excel shortcuts

  3. Definition • Microsoft Excel is a spreadsheet program included in the Microsoft Office suite of applications. Spreadsheets present tables of values arranged in rows and columns that can be manipulated mathematically using both basic and complex arithmetic operations and functions

  4. Important Features of Excel • Useful Functions • COUNTIF, COUNTIFS, SUMIF, SUMIFS • Data manipulation • Data importing & conditioning • Text to Columns • Sorting • Filtering • Grouping, Subtotaling • What if analysis • Data Table • Goal Seek, Solver • Visualization • Conditional Formatting • Charts • Pivot Tables • Macro recording & editing

  5. Excel Proficiency (self test) • Novice • OK with Data Entry tasks. • Novices formulas: SUM, IF, AVERAGE, COUNT, ROUND • Intermediate • Comfortable building formulas to manipulate text and dates • This category includes most users who claim to know Excel very well. • Intermediate Formulas: SUMIF, COUNTIF, VLOOKUP, CHOOSE, MID • Advanced • Knows what array formulas are and how to use them. • Can create and modify macros with or without the macro recorder. • Advanced Formulas: SUMPRODUCT, INDIRECT, INDEX, MATCH, OFFSET • Expert • Can build add-ins for distribution and widespread use. • If you can think it, they can build it with Excel. • Expert Formulas: A true expert knows the strengths & weaknesses of every single function • Guru • Probably just a handful of them in the world. • If you're an Expert, you probably know their names.

  6. Condition Imported Data • Web or mainframe data may not fall into neat columns when pasted into Excel • Data...Text to Columns menu item is primary tool for splitting data into columns • Dates and numbers are stored as text • Copy a blank cell, select the data and Paste Special...Add • Use the CLEAN function to remove non-printing characters • Use SUBSTITUTE to remove specific characters. Nest formula. • Use TRIM to remove leading and trailing spaces

  7. Counting & Summing with Criteria • SUMIF, COUNTIF, SUMIFS and COUNTIFS • Critieria are case insensitive—CIGNA vs. cigna. • Only the used cells will be searched. This makes these functions much more efficient than SUMPRODUCT or array formulas. • Wildcard characters in the criteria. • ? matches any single character • * matches 0 or more characters • Tricks with criteria • Concatenate operators with a cell value in the criteria • Put the date in a cell, use the DATE or DATEVALUE function, or else preface a string with two minus signs

  8. SUMPRODUCT & Array Formulas • Array formulas have array arguments where normally a single value would be used • Embedded IF function • Boolean expression • Array formulas avoid need for helper columns • Control + Shift + Enter • Array formulas are surrounded by curly braces { } • SUMPRODUCT uses Boolean expressions, but does not need to be array-entered • Less important now because of COUNTIFS & SUMIFS

  9. Filtering • AutoFilters provide both filtering and sorting functionality. • Tables inherit AutoFilters on each column • Custom filter, which affords some simple logic to the filtering.

  10. Sorting • The Data...Sort menu item sorts one or more columns • Options button allows sorting horizontally as well • You may sort in numerical or alphabetical order • You may also sort according to a Custom List • Sorting also possible using AutoFilter, Table, or PivotTable

  11. Conditional Formatting • Change appearance of cells based on the values they contain • Display a bar graph (data fill) proportional to cell value compared to other cells • Add a highlight color to cells containing an unusual value • Change formatting based on the results of a formula • Formula should return TRUE or FALSE. TRUE is 1, FALSE is 0 • May have more than one conditional format, either singly or in combination

  12. Charting • Many chart types possible. Choose wisely! • Scatter chart vs. Line chart • Bar & Column charts • Eschew 3D and pie charts • Text on a chart can be linked to a cell via a formula • Trend lines can also show equation & R-squared • Microsoft changed charting in Excel 2007. No macro recording (restored in Excel 2010) • Improved discoverability with Excel 2013 chart wizard

  13. Data Tables • One-variable and two-variable data tables perform repetitive calculations. • Better to think of them as taking one or two direct inputs • Many input/many output problems using indirect inputs in a lookup table • Underutilized way of performing repetitive calculations • Especially useful when the "calculation engine" is complex • Details & formulas need be defined only once • Calculation engine may include worksheet formulas and VBA functions • Can be integrated with Solver

  14. Goal Seek & Solver • Goal Seek • Drive one target cell towards a specific value • Change only one cell in model • Solver • Drive one target cell to a minimum, maximum or specific value • Change one or more cells in model • May add constraints like integer, binary, non-negative • Optional install of Solver add-in • Solver.com for tech support

  15. Grouping & Subtotals • Group sorted data by one or more fields • At each change in value, one or more fields in each group will be counted, summed, etc. using the SUBTOTAL function. • SUBTOTAL can also be used to find the minimum, maximum, average, variance of the group • SUBTOTAL functions 1 through 11 will ignore values hidden by a filter • SUBTOTAL functions 101 through 111 will ignore values hidden either by a filter or by manual user action

  16. PivotTables • PivotTables summarize numeric data, typically by count or sum • Examine different alternatives with slicers or making dropdown selections • Raw data must be normalized • Each column must have a header label • Use either a Table or a dynamic named range as the source for your PivotTable • Formatting changes to PivotTable get overwritten after Refresh. Don’t fight it!

  17. VBA Code • Subs do things (e.g. formatting or data manipulation) • Functions return values • VBA code is readable, even by people with little experience • Need to display Developer menu • “Disable all macros with notification” • ALT + F11 toggles between VBA Editor and worksheet • ALT + F8 displays macro selector • May record a macro (sub) • Good way to learn objects, methods and properties • After editing, a recorded macro can be reused in future

  18. Spreadsheet etiquettes’ • Always separate out three stages : Obtaining data, analyzing the data, presenting the data • Avoid hardcoding numbers in formulae – always enter a variable in the cell and refer to the cell in a formula • Don’t fiddle with data – when using data from elsewhere • Use data validation where possible to minimize data entry errors, an to make it easier to analyze and report on the data • Always think, “ Is there a way I could do this that would save me time next time? • Lay out data to suit excel not to suit the eye. Every heading, subtotal or empty column you place among the data makes it far less useable

  19. Spreadsheet etiquettes • Get used to using the dollar signs when you refer to cells. Used well you can write a formula once and use it everywhere • Protect cells with formulae in them – it can be difficult to find an incorrect formula caused by accidental typing • Use color and formatting ( and descriptions) to make it obvious where data entry is required • Avoid merging cells unless absolutely necessary, merged cells make a spreadsheet far harder to edit.

  20. Excel shortcuts • https://exceljet.net/keyboards-shortcuts

More Related