• Data isn’t just for statisticians, accountants, and economists.
• Anyone and everyone, even beginners, can learn how to make better (and faster!) use of Excel.
• 99.9% of us use Excel
• Sample uses: home budgeting, loan information like mortgage amortization tables, responses to your company’s latest consumer satisfaction survey, contact information for people who attended your organization’s conference
• Excel has myriad uses, but one rule’s for certain: there’s always a faster, easier way to do tasks in Excel
• Here are a few of those strategies that will make your life easier

Getting Started

Excel novices are often pleasantly surprised to learn how many similarities exist between Microsoft products like Excel, Word, and PowerPoint.

Similarities between Excel, Word, and PowerPoint

Formatting Text and Numbers

You’re not stuck with regular ol’ text inside of Excel. You can adjust some or all of your spreadsheet’s contents to bold, italic, or underlined numbers and letters.

http://g.recordit.co/3f7PLKUacd.gif

You can also format your numbers, dates, percentages, and financial information nearly any way you want. For example, if your spreadsheet contains dates, you can select either the Short Date or Long Date format. If your spreadsheet deals with something money-related, you can select either the Currency or Accounting format.

Screencast: Demonstrating Number Format drop-down window.

You’ve also got control over how many decimal places are shown. Pretend you’re dealing with numbers with loooooooots of decimal places. Rather than deleting the extra decimal places by hand, just click on the Increase Decimal or Decrease Decimal buttons and let Excel do the heavy-lifting for you.

Screencast: Long numbers and increasing/decreasing the number of decimal places shown.

Adjusting Colors

Colors are highly customizable in Excel, just like in Word and PowerPoint.

Change your font’s color by selecting the cell(s) you want to re-color and then clicking on the Font Color button on the Home tab. Or, fill in your cells completely by selecting the cell(s) you want to re-color and then clicking on the Fill Color button on the Home tab.

Screenshot: The Font Color and Fill Color buttons.

SmartArt

I primarily use SmartArt within my PowerPoint slides or Word documents, but sometimes the occasional diagram is useful in my Excel file, too. Head over to the Insert tab and click the SmartArt button to see all your options. I typically tweak the colors and fonts used in SmartArt to make my graphics appear more customized and less, well, generic.

Screenshot: SmartArt menu

Images

Screencast: Inserting the Udemy logo into the Excel sheet

Hyperlinks

Screencast: Inserting a hyperlink

Key Terms Unique to Spreadsheet Programs Like Excel

Screenshot with arrows pointing to the various feature

• Sheet vs. tab vs. workbook
• Row, column
• Cell
• Table
• Functions / formulas
• Formula bar

Organizing Your Spreadsheet

When you’re staring at a spreadsheet for hours on end, a little organization can go a long way.

Designing Your Dataset in a Tabular Format

First, make sure your data is arranged in a tabular format. You’ll want contiguous cells (aka touching cells). Try not to skip any rows or columns if typing in data by hand. If you need more space, try wrapping text or re-sizing the row or column to make it a little larger, rather than skipping a couple columns altogether. This layout will make it easier for you to use functions or pivot tables later on.

 Images:

• What NOT to do: Data skipping columns because text is too long
• Instead: Contiguous cells with re-sized columns and wrapped text

Columns and Rows

Inserting

Deleting

Hiding/unhiding

Re-sizing

By hand

Automatically to fit the cell’s contents

Freezing Panes So That Some Information Stays in View While Scrolling

Freezing the Top Row

When I’ve got more than 30 or so rows of information in my spreadsheet, I have to scroll up and down to see everything. I like to keep my column headers in view even while I’m scrolling down the bottom of the dataset so I can remember what’s what.

Instructions:

• Click on the important row that you want to keep in sight (e.g., click on the 1 to the left of the first row to highlight that entire row).
• Go to the View tab.
• Click on the Freeze Panes button.
• Select Freeze Top Row from the drop-down menu.
• Now, scroll down to the bottom of your dataset. Your top row now magically stays in place so you can read your labels while scrolling!

Screencast: Freezing the first row

Freezing the First Column

Similarly, when I’ve got lots of columns in my spreadsheet, I like to keep the left-most column in view while I’m scrolling over to the right in my dataset (since the first column usually contains important details like the name of a person or organization).

Screencast: Freezing the first column

Freezing Both Rows and Columns

Bonus! The best of both worlds is freezing both your rows and columns.

Screencast: Freezing the first row and the first column

Sheets

Inserting

Deleting

CAREFUL, there’s no way to un-do this action.

Hiding and Unhiding

Password-Protecting

Re-Naming

Excel assigns generic, forgettable names to new sheets, like Sheet 1, Sheet 2, and Sheet 3. To keep myself organized, I rename my sheets. I choose simple names that describe the contents of each sheet, like Dataset, Tables, and Charts. To re-name your sheets, simply right-click on the sheet and select Rename.

Screencast: Right-clicking on “Sheet 1” and renaming to something more memorable.

Color-Coding

When I’m sending spreadsheets to colleagues, I want to make sure they spot the most important sheet as soon as they open the file. I’ll turn that crucial sheet bright yellow or bright red to draw extra attention to it.

Screencast: Turning one sheet bright yellow.

Sorting and Filtering

For alphabetizing, arranging numbers from greatest to least or least to greatest, or for putting a temporary filter on your information so that you can just pay attention to a subset of your spreadsheet at one time

Sorting Data

This technique is familiar to most people I speak with.

Instructions:

• Highlight or select the data that you want to re-order.
• Go to the Data tab.
• Select the Sort button.
• In the pop-up window, choose how you want to organize your data, e.g., alphabetically or from greatest to least.
• Bonus! You can also create multiple layers of sorting by clicking the Add Level button within that same pop-up window.. For example, you might first sort by gender and then by age.

Screencast: Demographics file; sort by gender and then by age.

Filters

Have you seen the Excel’s Filters option yet? It’s hiding within plain sight on the Home tab. Filters are one of my all-time favorite features of Excel because, for me, they’re even easier to use than sorting, and they save valuable time as I’m wading through so many numbers.

Instructions:

• Highlight or select the top row of data (or whichever row has your labels in it – it might be the second or third row if you’ve got any empty space at the top of your spreadsheet).
• Go to the Home tab.
• Click on the Sort & Filter button and then select Filter.

• Tiny boxes with tiny arrows will appear on each of the cells in the top row of your dataset. Congratulations, you’ve got filters!
• Click on the arrows to sort the data from smallest to largest or largest to smallest, to sort from A to Z or from Z to A. Or, use the checkboxes to filter the data. For example, you might only want to look at males, or only at females. The filtered-out data hasn’t been deleted and you can make it reappear at any time by simply re-checking the filter’s checkbox.

Screencast: Inserting filters in demographics file.

Bonus! Use your new filters to arrange your dataset by colors. Sometimes I use red, yellow, or green colors to indicate whether items are high, medium, or low priorities for me. I can filter my dataset so that the reds appear on top, followed by the yellows, followed by the greens.

Screencast: To-do list with items prioritized and color-coded, and then sorted by color.

Time-Saving Strategies

I love Excel but that doesn’t mean I want to spend more time than necessary using it. Instead, I use these time-saving strategies to save my mental energy for tasks that are more important. Here are my top __5__ techniques everyone should know.

Crucial Excel Shortcuts

• Copy: CTRL + C
• Paste: CTRL + V
• Highlighting entire rows or columns at once: CTRL + Shift + Arrow

Automatically Color-Coding Certain Cells Based on their Content

• Color-coding text and cells by hand – easy for small datasets, but time-consuming after a while, and prone to errors
• Show 3-4 conditional formatting techniques
  • Duplicates
  • Greater than a certain value
  • Text that contains a certain word
  • Top 10% of items

Dragging Formulas

• Example: Meeting attendance; calculate meeting attendance once and drag formula over to the left

Auto-Fill

• 1, 2, 3
• A, B, C or a, b, c
• Jan, Feb, Mar

Formatting Text, Like Peoples’ Names

Sample dataset: Names

Lower

Upper

Proper

Formatting Dates

Use the buttons to format your dates consistently and in the format of your choosing, e.g., Short Dates or Long Dates.

Month

Day

Year

Days365

Combining Content from Multiple Cells into One Cell

Sample dataset: Names

Two options: concatenate and &. Use either strategy – it’s your choice. They do the same thing so it’s more of a personal preference than anything else.

Concatenate

Use this term to impress your friends at cocktail parties. Literally means combining things into a chain.

&

“The and operator,” as it’s called, is nearly identical to the concatenate function.

Transposing (Flipping) Your Rows and Columns with Paste Special

Instructions:

• Highlight or select the table you want to flip
• Click copy (CTRL + C)
• Go to Paste Special and select Transpose
• Paste in a new area (e.g., below the original table)

Functions and Formulas Everyone Should Know

Define “function” and “formula” and then explain that the difference isn’t too important because so many people use these terms interchangeable anyway

Figuring Out Which Function to Use

• Autosum button
• Inserting Excel Tables
• Clicking on the Function Bar for a full menu/glossary of formulas

Simple Math Formulas

• Every formula starts with = sign
• Show basic addition, subtraction, multiplication, and division; either enter the value by hand OR click the cell
• Sum (a total) vs. count (a tally)
• Countif
  • Meeting attendance: Countif the value above is “yes”

Formulas Unique to Certain Industries

Like I mentioned above, I use Excel’s full menu of formulas to get ideas for formulas I might want to use in industry-specific projects

Common Statistical Formulas

• Mean, median, mode, standard deviation, quartiles, etc.

Common Budgeting and Accounting Formulas

Intermediate and Advanced Techniques to Score that Promotion

“Formulas that help you get promoted at your job” or something along these lines

Pivot Tables

Tell story of how I was tested at LAYC; used pivot tables, solved the practice problems with 5 minutes, and received a job offer later that same day.

Insert a Pivot Table

Drag-and-Drop Variables

Refreshing

Double-Click to Explore Oddities

Text to Columns

Bikeshare example?

Vlookup and Hlookup

Explain purpose: Combining two datasets (example: demographics plus mental health counseling scores)

Step 1

Step 2

Step 3

Step 4

Show difference between vlookup and hlookup