Highline Excel 2016 Class 01


Excel Fundamentals: Efficiency, Data, Data Sets, Formatting

DESCRIPTION

In this video learn about these Excel Fundamentals for Excel Highline Excel 2016 Class:
(00:33) Prerequisites for class
(01:04) Excel Professional 2016
(01:50) Install Com Add-in Power Pivot
(02:54) Examples of Effective and Efficient Solutions in Excel
(09:20) What Excel Does: 1) Make Calculations & 2) Data Analysis
(11:25) Structure of Excel: Cells, Worksheets, Sheet Tabs and Workbooks
(13:00) Keyboard Shortcuts are efficient because they help you to accomplish tasks quickly
(15:06) Data Analysis and Business Intelligence terms
(16:31) What is Raw Data?
(17:16) Excel Proper Data Sets
(20:18) Terminology: Cleaning, Transforming, Importing Data Data
(22:40) Data Types and Default Alignment in Excel
(29:05) Excel Table feature
(34:45) Number Formatting is a Façade
(39:13) Date Number Formatting and Date Math (Date Formulas)
(43:51) Time Number Formatting and Time Math (Time Formulas)
(46:35) Percentage Number Formatting
(53:10) Custom Number Formatting
(01:03:00) Style Formatting allows you to present information in an effect way
(01:10:00) Cell Styles
(01:13:45) Page Setup allows you to print information in an effect way
(01:19:24) Summary
(01:20:25) Homework Problems

Highline Excel 2016 Professional Class taught by Michael excelisfun Girvin

WORKBOOKS

Highline Excel 2016 Class 01 Workbook

WRITTEN NOTES

Topics:

1) Excel Professional 2016
2) Install Power Pivot
3) Develop Effective and Efficient Solutions in Excel
4) What Excel Does: 1) Make Calculations & 2) Data Analysis
5) Structure of Excel: Cells, Worksheets, Sheet Tabs and Workbooks
6) Keyboard Shortcuts are efficient because they help you to accomplish tasks quickly
7) Data Analysis and Business Intelligence terms
8) Excel Proper Data Sets, Data Types, Alignment and the Excel Table feature
9) Number Formatting is a Facade
10) Style Formatting allows you to present information in an effect way
11) Page Setup allows you to print information in an effect way

Excel Professional 2016

Computer must have Professional Excel 2016 for PC.
Excel 2016 Stand Alone program
Office 2016 Professional
Office 365 ProPlus

Install Power Pivot

Click on File Ribbon Tab
Click on Options
Click on Add-ins
From Manage drop down select Com Add-ins
Click Go
Check the option for Microsoft Power Pivot for Excel
Click OK
The new Power Pivot Ribbon Tab will appear.

Develop Effective and Efficient Solutions in Excel.

Effective:
1. Define: Accomplish the stated goal.
2. Examples:
i. Use COUNTIFS to count the correct number for how many Ford cars were sold.
ii. Use the correct Number Format to display the same number as the underlying number in the cell.
Efficient
1. Define: Accomplish the goal with the minimum number of resources and have the accomplished goal have the ability to adapt to future changes.
2. Examples of tasks that accomplish the goal with the minimum number of resources (where the resource is time to create a solution) are:
i. Use Mixed Cell References and the COUNTIFS function to build a cross tabulated table with a single formula rather than many formulas.
ii. Use keyboard shortcuts to accomplish most tasks, rather than slower methods like using the Ribbon Tabs, Menus or scroll bars.
3. Examples of tasks that accomplish the stated goal and have the ability to adapt to future changes are:
i. For a formula that calculates a tax deduction you must place the tax rate in a cell, label it and refer to the tax rate in the formula with a cell reference. In this way if the tax rate changes, it is easy to update it later. Hard coding formula inputs into formulas makes it time consuming and difficult to update the formula later (and is the #1 cause of spreadsheet error). Formulas like: =ROUND(A44*0.0765,2) are hugely inefficient and error prone.
ii. Formula like =SUM(A1:A5) rather than =A1+A2+A3+A4+A5 will update if a row is inserted at row four.

Goal of Class: Develop Effective and Efficient Solutions in Excel for Making Calculations Performing Data Analysis

What Excel Does:

1) Make Calculations: Calculations such as numeric, logical and text calculations.
2) Data Analysis: Data Analysis = Convert Raw Data into Useful Information for Decision Makers.

Structure of Excel:

Columns (Represented by Letters).
Rows (Represented by Numbers).
Cells = Intersection of Column and Row = Name or Address like B5 or A1.
Worksheet = Sheet = All the Cells.
Sheet Tab = Name of worksheet.
Workbook = All the sheets = file.
Navigation of sheets:
1. Ctrl + PageDown =expose next sheet to right.
2. Ctrl + PageUp =expose next sheet to left.
3. Right-click Sheet Navigation Arrows to get a pop-up for sheet names.

Keyboard Shortcuts are efficient because they help you to accomplish tasks quickly.

Ctrl + Arrow: jumps to the bottom of the “Current Region”, which means it jumps to the last cell that has data, right before the first empty cell.
Ctrl + Home = Jump to cell A1.
Ctrl + End = Go to last cell used.
Ctrl + Shift + Arrow = Highlight column (Current Region). Current Region is defined as all the data up to the first empty cell.
SUM: Alt + =
Ctrl + Backspace = Jump back to Active Cell.
Ctrl + 1 = Format Cells dialog box, or in a chart it opens Format Chart Element Task Pane.
Ctrl + Z = Undo, Ctrl + Y = Undo the Undo, Ctrl + C = Copy, Ctrl + V = Paste, Ctrl + X = Cut.
Page Setup: Alt, P, S, P
Put “things” (formulas, text, numbers) in cell with:
1. ENTER = Put thing in cell and move selected cell DOWN.
2. CTRL + ENTER = Put thing in cell and keep cell selected.
3. TAB = Put thing in cell and move selected cell RIGHT.
4. SHIFT + ENTER = Put thing in cell and move selected cell UP.
5. SHIFT + TAB = Put thing in cell and move selected cell LEFT.

Data Analysis and Business Intelligence terms:

Data Analysis = Convert Raw Data into Useful Information for Decision Makers.
Business Intelligence = Convert Raw Data into Useful/Actionable Information (often times in the form of a Dashboard) for Decision Makers in a Business Situation.
Raw Data = data in its smallest form that allows Excel Data Analysis features and Excel data analysis techniques to work.
e.g. Don’t put WA, 98654 is a single cell, break it apart into two cells. For data analysis we need state and zip code to be in separate columns so we can sort, filter or add the column as criteria to a PivotTable.
Proper Data Set = Proper Table Format = Field Names in first row and Records in rows.
Clean Raw Data = Fix unusable raw data so that it can be used to perform data analysis.
Examples:
i. Remove unwanted charters.
ii. Add needed characters.
iii. Split data apart into desired data.
iv. Join data together to get desired data.
Transform Data Sets = Fix unusable data set so that it can be used to perform data analysis.
1. Examples:
i. Filter, combine, merge, append or unpivot data sets.
ii. Add, remove or filter columns in data sets.
Import Data = import data from external sources (single or multiple sources) into Excel or Power Pivot’s Data Model; optimally, the import will allow refreshes so that when source data changes the report output resulting from the import action will update reflecting the changes in the source data.
Goal of Data Analysis and Business Intelligence: Create useful, update-able, actionable information for decision makers.

Excel Proper Data Sets, Data Types, Alignment and the Excel Table feature:

Proper Data Set and Proper Table Format are synonyms.
The following Excel features do not work properly unless the raw data is stored in a Proper Data Set:
Sorting, Filtering, Advanced Filter, D Functions, PivotTables, Excel Table feature, Power Query, Power Pivot and Power BI Desktop.
Definition of Proper Data Set in Excel:
Field names in first row.
Records in subsequent rows.
Empty cells or Excel Row or Column Headers must surround data set.
Data Types and Default Alignment in Excel:
Numbers are aligned right.
Text values are aligned left.
Boolean Values (TRUE or FALSE) are aligned center and ALL CAPS.
Error Values are aligned center.
Empty Cells.
The Default Alignment in Excel gives a visual cue of what type of data it is.
If Dates, Times or Numbers are aligned left and are considered text your formulas and other Excel features may not work as intended. Example: SUM function cannot add a column of Text Numbers.
Reasons that Dates, Times or Numbers are aligned left and are considered Text:
Often times Dates, Times or Numbers exported from databases or text files are considered text.
If you apply the Text Number Format.
Using a lead apostrophe before the Date or Time or Number.
Miss typing a date or time or number (US system):
i. 15/2/2016 (there is no 15th month).
ii. 8:00AM (no space between time and AM).
iii. 20..56 (too many decimals).
iii. Rule for when to manually change the alignment:
1. Only change the alignment when you are preparing the final report and the data will not be used by formulas or features.
2. Do NOT change alignment for raw data that will be used by formulas or other features.
Excel Table feature:
1. What is does:
i. The Excel Table is considered a “database table” which means it has dynamic ranges that allow you to add or remove new records or columns to the table and all Formulas, Charts, PivotTables, Power Queries and Power Pivot Data Models will automatically update (PivotTables, Power Queries and Power Pivot Data Models require you to click the Refresh button).
ii. Adds formatting to the entire table.
iii. Adds Sorting and Filter buttons to each field (at the top of each column).
How to convert Proper Data Set to an Excel Table:
i. With a single cell selected in the Proper Data Set:
1. Insert Ribbon Tab, Table group, Table button
2. Keyboard ===> Ctrl + T
How to name your Table:
i. With a single cell in the Excel Table:
1. Table Tools Ribbon Design Tab, Properties group, Table Name Textbox.
2. Keyboard: Alt, J, T, A
When you highlight a range from an Excel Table in your formula or a dialog box, the range shows up with the Table Name and Field Name:
i. Example if the Table Name is Transactions and the Field Name is Sales the range looks like this:
1. ProdSales[Sales] i. Table names appear as the actual name: ProdSales.
ii. Field names are in Square Brackets: [Sales].
How to add new records to a table:
i. Type new record in first row below table.
ii. In last cell in last record in table, hit Tab.
How to convert an Excel Table back to a non-Excel Table:
i. Right-click, Table, Convert to Range.

Number Formatting is a Façade:

Number Formatting can display numbers on the surface of the cells that can be different than the underlying numbers that sit in the cells.
Formulas make their calculation on the actual numbers in the cells, not the numbers that are displayed on the surface of the cells.
If you are required to round your numbers, like for invoices, payroll, accounting, use the ROUND function (see section later in this document about rounding).
Remember:
1. Formulas can’t see Number Formatting.
2. What you see on the surface of the spreadsheet is not always what is in the cell.
Examples of Number Formatting:
General Number Formatting:
i. General Number Formatting = What you see is what is in the cell.
ii. If you apply General Number Formatting, it will wipe away all of the previously applied Number Formatting to reveal what number actually sits in the cell.
Applying General Number Formatting ERASES all previously applied Number Formatting.
iii. General Number Formatting is the default Number Formatting on all cells.
iv. Ctrl + Shift + ~ ( ` ) = General Number Formatting Keyboard.
Decreasing Decimal Number Format:
i. Decreasing decimals does not remove underlying decimals, but rather, it displays the number with fewer decimals than the actual number that sits in the cell.
ii. When you decrease decimals, the number appears as if it has been rounded, but it is not rounded. If you have invoices, payroll and other numbers that require rounding, you must use the ROUND function to round the numbers in order to get the correct answer.
Accounting Number Format:
i. Fixed dollar sign (left edge of cell).
ii. Negatives are in parenthesis.
iii. Zeros are dashes.
iv. Decimals always line up.
v. When you use Accounting Number Format it may hide decimals by displaying fewer decimals than are actually in the cell. This may lead to formula errors due to fact the formula calculates on the underlying number and not the displayed number.
Currency:
i. Floating dollar sign.
ii. You choose how to show negatives.
iii. Zeros are zeros.
iv. Decimals usually line up.
v. When you use Currency Number Format it may hide decimals by displaying fewer decimals than are actually in the cell. This may lead to formula errors due to fact the formula calculates on the underlying number and not the displayed number.
Date Number Format:
i. Enter dates with forward slashes (there are other methods also) such as: 3/30/2016.
ii. Under the Date Number Format is a Serial Number that represents the number of days since December 31, 1899.
1. Examples:
Jan 1, 1900 = 1
Jan 2, 1900 = 2
Oct 30, 2013 = 41577
Mar 30 = 42459
iii. Some Date Math Formulas:
1. =End Date – Start Date = Number of Days Between Two Dates (Number of days an invoice is late).
2. =End Date – Start Date + 1= Number of Days Between Two Dates Including the Start Date (Number of days for a project that includes the start date).
3. =Loan Issue Date + Number of Days Loan Outstanding = Maturity Date.
iv. Date/Time Keyboards:
1. Ctrl + ; = Keyboard for hardcoding today’s date.
2. Ctrl + Shift + ; = Keyboard for hardcoding current time.
Time Number Format:
i. Enter time as hour, colon, minutes, colon, seconds, then a space, and AM or PM (there are other methods also) such as: 8:00 AM.
ii. Under the Time Number Format is a serial number that represents the proportion of one 24-hour day.
1. Examples:
8:00 AM = 8/24 = 1/3 = 0.333333333333333
12:00 PM = 12/24 = 1/2 = 0.5
3:00 PM = (12 + 3)/24 = 15/24 = 5/8 = 0.625
iii. Some Time Math Formulas:
1. =(End Time – Start Time)*24 = Hours worked in a non-night-shift day.
2. =MOD(End Time – Start Time,1)*24 = Hours worked in a day or night-shift day.
iv. Date/Time Keyboards:
1. Ctrl + ; = Keyboard for hardcoding today’s date.
2. Ctrl + Shift + ; = Keyboard for hardcoding current time.
Percentage Number Formatting:
i. What is a percentage?
1. How many parts out of 100?
i. If the tax rate is 9.95%, this means that you must pay 9.95 pennies out of every 100 pennies (or one dollar).
ii. What Percentage Number Format does:
1. For the number 0.03, Percentage Number Formatting displays the number with:
i. The decimal slid two positions to the right and
ii. A percentage sign.
iii. 0.03 becomes 3.00%
2. For a tax rate of 1.45%, you have to remember that the underlying number is 0.0145.
Custom Number Format:
i. There are four sections to Custom Number Formatting, each separated by a semi-colon: ;;;
ii. In general, in this class we will learn some basic code for Custom Number Formatting and rely on looking up the code when we need it.
iii. We will learn a few basic Custom Number Formatting types:
1. Date.
2. Time.
3. Percentage
4. Significant and insignificant decimals.
iv. List of code for Custom Number Formatting is on the sheet named “CNF-Notes” in the Excel Workbook File named “Busn218-Week01.xlsm”.
v. Link to Microsoft Help for how to code and create Custom Number Formatting:

Style Formatting allows you to present information in an effect way.

Style Formatting involves formatting such as:
1. Bold.
2. Fill Color.
3. Font Color.
4. Borders Alignment.
5. Font Size.
6. Font Type.
7. Word Wrap
i. Alt + Enter = Add Manual Line Break (Word Wrap)
Guidelines for Style Formatting:
1. Use the minimum amount to effectively deliver the message.
2. Be consistent.
3. All numbers should have same number of digits.
4. Units must be indicated either with Number Formatting or Labels.
Two Schools of Thought for Stylistic Formatting:
1. Minimalism School says:
i. Field names or column headers should be bold.
ii. Use Number Formatting sparingly.
iii. Don’t use alignment.
iv. The default gray lines in the spreadsheet are the only borders you need.
v. Sometimes red is used to indicate importance.
2. More Than Minimal School says:
i. Field names or column headers can have Fill Color and Font Color, but be consistent.
ii. Font Color and Fill Color must have a large value difference so that it is easy to read:
1. Dark Blue Fill Color and White Font Color have a large value difference.
2. Red Fill Color and Black Font Color do NOT have a large value difference.
iii. Use Number Formatting Consistently.
iv. Black Borders can be okay for tables of data.
v. Use similar color schemes across tables, charts, PivotTable, Slicers and other objects.
Cell Styles
1. Allow you to save a set of Stylistic & Number Formatting and use it as often as you need.
2. Add New Cell Style:
i. Add style to a cell
ii. Go to Styles group in the Home Ribbon and click on the Cell Styles dropdown arrow or “More button” and click on New Style. This opens the Styles dialog box.
iii. Name the Style
iv. When in the Style dialog box, you can use the Format button to add more styles if you want
3. To use the Cell Style: select cells and apply new style from the Style group in the Home Ribbon Tab.

Page Setup allows you to print information in an effect way

Page Setup dialog box
1. Open Page Setup dialog box keyboard = Alt, P, S, P
2. Page Setup dialog box
Page tab:
1. Orientation:
i. Landscape when the table is wider than it is tall.
ii. Portrait when it is taller than it is wide.
2. Scaling: you can shrink or enlarge the how the information will look when it prints out.
3. Fit to:
i. Allows you to take a table that is slight bigger than the page and shrink it to fit on the page.
ii. If you type “1” for pages wide” and leave the text box empty (hit delete key) for “pages tall”, the information will always fit to one page wide, but it will print out as many pages tall as you need. This is great if you have an expanding table, or you don’t know how many pages tall the information is.
Margins tab:
1. Set margins on edge of each page.
Header/Footer tab:
1. Header 3 sections:
i. Preview: gives you a preview.
ii. Built-in: select from drop down list.
iii. Custom Header: Allows you to type header or select from a number of different options (buttons).
2. Footer 3 sections:
i. Preview: gives you a preview.
ii. Built-in: select from drop down list.
iii. Custom Footer: Allows you to type footer or select from a number of different options (buttons).
Sheet tab:
1. Set Print Area: Select just the range you want to print. This is great when you want to exclude data and calculations that are not part of the final report.
2. Columns to repeat on Left: Prints the highlighted column on each page. This is great for large tables, like when you want to repeat names on the left of each page.
3. Rows to repeat at Top: Prints the highlighted row at the top of each page. This is great for large tables, like when you want to repeat field names at the top of each page.

Cumulative List of Keyboards Throughout Class:

Esc Key:
i. Closes Backstage View (like Print Preview).
ii. Closes most dialog boxes.
iii. If you are in Edit mode in a Cell, Esc will revert back to what you had in the cell before you put the Cell in Edit mode.
F2 Key = Puts formula in Edit Mode and shows the rainbow colored Range Finder.
SUM Function: Alt + =
Ctrl + Shift + Arrow = Highlight column (Current Region).
Ctrl + Backspace = Jumps back to Active Cell
Ctrl + Z = Undo.
Ctrl + Y = Undo the Undo.
Ctrl + C = Copy.
Ctrl + X = Cut.
Ctrl + V = Paste.
Ctrl + PageDown = expose next sheet to right.
Ctrl + PageUp = expose next sheet to left.
Ctrl + 1 = Format Cells dialog box, or in a chart it opens Format Chart Element Task Pane.
Ctrl + Arrow: jumps to the bottom of the “Current Region”, which means it jumps to the last cell that has data, right before the first empty cell.
Ctrl + Home = Go to Cell A1.
Ctrl + End = Go to last cell used.
Alt keyboards are keys that you hit in succession. Alt keyboards are keyboards you can teach yourself by hitting the Alt key and looking at the screen tips.
Create PivotTable dialog box: Alt, N, V
Page Setup dialog box: Alt, P, S, P
Keyboard to open Sort dialog box: Alt, D, S
ENTER = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell DOWN.
CTRL + ENTER = When you are in Edit Mode in a Cell, it will put thing in cell and keep cell selected.
TAB = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell RIGHT.
SHIFT + ENTER = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell UP.
SHIFT + TAB = When you are in Edit Mode in a Cell, it will put thing in cell and move selected cell LEFT.
Ctrl + T = Create Excel Table (with dynamic ranges) from a Proper Data Set.
Keyboard to name Excel Table: Alt, J, T, A
Tab = Enter Raw Data into an Excel Table.
Ctrl + Shift + ~ ( ` ) = General Number Formatting Keyboard.
Ctrl + ; = Keyboard for hardcoding today’s date.
Ctrl + Shift + ; = Keyboard for hardcoding current time.
Arrow Key = If you are making a formula, Arrow key will “hunt” for Cell Reference.
Ctrl + B = Bold the Font
Ctrl + * (on Number Pad) or Ctrl + Shift + 8 = Highlight Current Table.
Alt + Enter = Add Manual Line Break (Word Wrap)
Ctrl + P = Print dialog Backstage View and Print Preview