Excel Training Slide Deck & Worksheets

This was a 2hr all staff training I conducted on Excel. Each slide is designed to be usable as a one-page reference.

The first half is appropriate for the most basic users (and more advanced users can benefit from refreshers and the shared language). The second half is appropriate for more advanced users (and basic users can benefit from seeing what is possible, if not understanding how to do it).

Please feel free to share these materials with others, and let me know if you have any questions.

Download all Files: Slides & Worksheets (FREE or Donation)

Today’s Date in a Cell

Need to quickly put today’s date in a cell? There are two ways depending on the desired result.

1) =TODAY() will update the date in the cell every time the file is opened. If today is March 12, 2014, that is what the cell will read. However, when the file is opened again in three days, it will read “March 15, 2014” because the function will update the contents of the cell.

2) Ctrl + ; will insert today’s date as text into a cell. This is helpful for documents that track when things were completed, as the date will not change when the document is re-opened at a later time.

Visualizing and Tracing Formulas

Tracing formulas through the Formula Auditing tools in Excel can be invaluable in navigating complex formulas and diagnosing unexpected results. This guide reviews methods of visualizing and tracing formulas, including Trace Precedents, Trace Dependents, and Evaluate Formula.

Click to download the Tracing Formulas Guide (FREE)
Click to download the Tracing Formulas Guide (Donation)

Tracing Formulas 150 x 150

Multiple Windows & Arranging Views in Excel

Using appropriate “view” tools in Excel can help you better navigate worksheets and files. This guide shows how to freeze rows and columns, split a worksheet into panes that scroll separately, and how to show multiple windows side by side.

Click to download the Arranging Views Guide (FREE)
Click to download the Arranging Views Guide (Donation)

Arranging Views 150 x 150

Data Analysis Toolpak – Statistics in Excel

The Data Analysis Toolpak in Excel provides an interface to the native statistical functions. Using simple graphical dialogs to select and input data, the tools produce reports for common statistical tests.

This guide covers the Descriptive Statistics tool and the Regression tool, as well as an overview of showing a linear regression in a scatterplot.

Click to download the Data Analysis Toolpak Guide (FREE)
Click to download the Data Analysis Toolpak Guide (Donation)

Data Analysis

Alert: Adobe Flash update auto-installs McAfee Security Scan

Unfortunately, Adobe’s latest Flash Player update (which runs videos and interactive things on the web) automatically includes an installation of “McAfee Security Scan.”

While this will not harm your computer, it can be confusing to see or find it installed without your acknowledgement. Shame on Adobe for this terrible practice!

If I am connected to your computer via Soluto (join my Soluto!), I have likely already updated Flash for you in a way that will not install the McAfee “crapware.” Otherwise, please DO update Flash if/when you are prompted to do so because updates often close important security holes/exploits.

If you have already updated Flash, the “version information” box at the following link should read “11.9.900.152”: https://www.adobe.com/software/flash/about/

To uninstall McAfee Security Scan or to see if it is installed, follow the steps here: http://service.mcafee.com/FAQDocument.aspx?lc=1033&id=TS100783

Happy computing!

Change all to Title Case

I’ve covered the quick and dirty way to change all text to UPPERCASE or lowercase in Excel by using Word and the shift + F3 trick, but that will not work for Title Case.

Say you have a list (or column) in Excel that looks like this:

To kiLL a MockinGBird
RoMeo and julieT
The GReat gatsBY
the CATCHer in the RYE

To make the first letter of every word in a cell uppercase (Title Case), use the PROPER function.

  1. In a blank column, type =PROPER(A1) where “A1” is the first cell of the list you are trying to change.
  2. Fill that formula down to the bottom of the list.
  3. Then, you can copy the now properly formatted list and “paste as values” into a new column OR over your original data.

Proper Case Function

Other Useful Text Case Functions in Excel

  • =UPPER(A1) – changes all text in a cell to UPPERCASE
  • =LOWER(A1) – changes all text in a cell to lowercase

Change all to UPPERCASE (or lowercase) – the quick and dirty way

Changing text case in Excel can be accomplished in many ways. For this quick and dirty tip, I actually recommend using a feature of Word. There’s probably a Very Good Reason why Microsoft included this handy shortcut in Word and not in Excel, but I have no idea what that Very Good Reason might be.

Say you have a list (or column) in Excel that looks like this:

To kiLL a MockinGBird
RoMeo and julieT
The GReat gatsBY
the CATCHer in the RYE

While you might be tempted to just retype each item in all uppercase or all lowercase, that would be a nightmare for longer lists!

Instead, open an empty Word document.

  1. Copy (ctrl + c) your list from Excel and paste it (ctrl + v) into Word.
    Hint: You can paste it as a table if that will help keep layout or formatting.
  2. In Word, select all (ctrl + a), and press shift + F3 on your keyboard.
  3. Keep pressing shift + F3 until your list is all in the case you want it in.
  4. Copy (ctrl + c) and paste (ctrl + v) the list back into Excel, and you’re done!
ORIGINAL SHIFT + F3 once SHIFT + F3 twice SHIFT + F3 thrice
To kiLL a MockinGBird TO KILL A MOCKINGBIRD to kill a mockingbird To kill a mockingbird
RoMeo and julieT ROMEO AND JULIET romeo and juliet Romeo and juliet
The GReat gatsBY THE GREAT GATSBY the great gatsby The great gatsby
the CATCHer in the RYE THE CATCHER IN THE RYE the catcher in the rye The catcher in the rye

Data Tables – A Printable Guide to 1 and 2 Variable Data Tables

Data Tables answer the question of “What happens to Y when I change X?”

This printable guide demonstrates both one variable and two variable data tables.

Download the Guide: Data Tables (FREE)
Download the Guide: Data Tables (Donation)

Data Tables