GET 236: ENTERPRISE DATA ANALYSIS: TOOLS AND TECHNIQUES

WEEK 01: INTRODUCTION

GARY FRIEDMAN8/31/15 5:15 – 8:05 PM

EXCEL BASICS – CIRCULAR REFERENCE

Chapter 11 Excel does a nice job of informing you when make a

mistake. Circular referencing is essentially creating a loop, and therefore the calculation cannot be performed.

How do you get Circular References?When Computing 2 or more cells in the formula

Depend on each other They “influence” the outcome of another cell There is a looping relationship Excel gets confused and will not proceed

EXCEL BASICS – CIRCULAR REFERENCE

Two common examples: Go to spreadsheet

A cell is included in its own formula. For example, the formula "B6 = SUM(B2:B6)". The fix is to change the formula to "B6 = SUM(B2:B5)"

Two cells refer to each other. Cell B13 has a formula "=B15" and B15 has a formula "=B13". One formula needs to change. Notice the graphic excel provides.

EXCEL BASICS – RANGE NAMES

Chapter 02 Situation: Have a formula of =SUM(A30:A35)

Now you have to go look at cells A30:A35 to remember what you are trying to summarize

An alternative is to name cells A30:A35 something like Sales Then, you can do use this formula =SUM(Sales)

How to create Name box or Formula tab Use Name Manager

EXCEL BASICS – RANGE NAMES

Is this required? No. It is personal preference and up to the user on if it is

needed or not. Alternate approach

be more detailed in documenting procedures use more labels and explanations in your

spreadsheets

EXCEL BASICS – PASTE SPECIAL

Chapter 1 page 8 Paste – what does it normally do?

Allows other optionsPaste only some of a copied or cut cell's properties

Paste only the cell content – no formula – when used? Paste the formula and not the content – when used? Perform Math calculations on cells Move columns to rows and vise versa

EXCEL BASICS – PASTE SPECIAL

Chapter 1 page 8

Most common uses: 123 means values. If you had formulas, they disappear and

only the “answer” remains means transpose. Vertical to horizontal or vice versa

EXCEL BASICS – CELL REFERENCING Cell referencing refers to “anchoring” a cell

Absolute referencing: both column and row STAY Ex. $A$4

Mixed cell address: one STAYs – either row or column Ex. $A4 Column stays Ex. A$4 Row stays

Relative cell reference: nothing stays Ex. A4

Essentially when you copy or drag an “anchored” cell, whatever you anchor will STAY. If the column of A2 is anchored (=$A2), and you copy that formula to cell ZZ875, it will reference A due to the $ and appear as =$A875.

Font:

Basic MS Word formatting applies: Bold, Italics, Font size, Font color, etc.

Alignment:

Wrap means to display longer text within the cell. Merge multiple cells into 1.

Number:

Quickly format currency, percentage, count, increase or decrease decimals

Clipboard:

Apply formatting of one cell to another. Helpful for $, bold, etc. Can be applied to entire rows and or columns

EXCEL BASICS – OTHER USEFUL THINGS

Home Tab

Cells:Insert row / column Delete row / columnFormat cell

Hide / UnhideRenameTab color

Editing:AutoSumClearSort & Filter

EXCEL BASICS – OTHER USEFUL THINGS (CONT.)

Home Tab

EXCEL BASICS – OTHER USEFUL THINGS (CONT.)

Review Tab

Just because we are in excel, doesn’t give us an excuse to misspell words. Use spellcheck, thesaurus, etc.

Comments are widely used in the business setting

Protection and sharing is common for sensitive workbooks or workbooks to be used by multiple people

EXCEL BASICS – OTHER USEFUL THINGS (CONT.)

View Tab

Show: Can check or uncheck Gridlines I often uncheck gridlines for a client deliverable to make it

look better

Freeze Panes is a must use feature in the business setting. Can view the field headings / names in row 1 when you are in row 10,000

QUESTIONS?

NEXT CLASS INFORMATION

Week 02: No Class: Holiday Week 03: Class on Importing and Validating data

Download the slides and excel template prior to class. The following chapters will be discussed in class:

38: Importing Data from Text Files 39: Importing Data from the Internet 40: Validating data 25: Sorting in Excel 46: Filtering and Removing Dups