Excel Techniques for Data Visualization

Preparing to load PDF file. please wait...

0 of 0
100%
Excel Techniques for Data Visualization

Transcript Of Excel Techniques for Data Visualization

Excel Techniques for Data Visualization

Kate Watkins
Chief Economist Colorado Legislative Council

NCSL Fiscal Analyst Seminar
October 11, 2018 Portland, Oregon

Why Excel?
Widely used: Wealth of online tutorials Accessible: Cost, ease of use Stepping stone: Tableau, R, ArcGIS
It’s more powerful than you think…
2

https://www.wsj.com/articles/ the-first-rule-of-microsoftexceldont-tell-anyone-youregood-at-it1538754380?mod=searchres ults&page=1&pos=2
3

Demo/Workshop
• Exploratory analysis
(for ourselves)
• Choosing and building effective visuals (for others)
Nonfarm Employment Percent Change, Year-over-Year
• Building dynamic tools VT

Since 2004 Colorado Populatio20n1b6y Age a2n0d17Gende2r 018

Food and
90+ 85 80 75 70
H6o5using
60 55 50 45 40
A3p5parel
30 25 20 15 10
5 0

beverages Male H/H

2020

Y/Y Annual

H/H Y/Y Annual

H/H Y/Y Annual

H1 H2 0.5 -0.2 0.4 0.3
0.3

H1 H2 H1 H2 Female
2.2 0.9 1.7 #N/A

2.0 3.2 2.6 #N/A

2.6

#N/A

3.4 2.6 5.7 6.1
5.9

2.5 2.0 5.1 4.5
4.8

1.4 #N/A 3.4 #N/A
#N/A

1.6 -3.1 4.4 -1.5
1.4

-3.9 7.6 -6.8 3.4
-1.8

-3.7 #N/A 3.7 #N/A
#N/A

60,000 40,000 20,000

0

20,000 40,000 60,000

NH

CHART PARAMETERS Adjust Date
• Leveraging automated data collection

MA CT RI NJ

DE MD

D.C.

UNRATE lin

Percent

U4RATE lin

Percent

M

Monthly

M

Monthly

01/01/2008 1948-01-01 to 201081-/0081-/021008 1994-01-01 to 2018-08-01

Civilian Unemployment Rate Special Unemployment Rate: Unemployed and Discouraged W

U.S. Bureau of Labor StatisticUs.S. Bureau of Labor Statistics

date

value

date

value

01/01/2008

5.0 01/01/2008

5.3

02/01/2008

4.9 02/01/2008

5.1

03/01/2008

5.1 03/01/2008

5.3

04/01/2008

5.0 04/01/2008

5.2

05/01/2008

5.4 05/01/2008

5.7

06/01/2008

5.6 06/01/2008

5.8

07/01/2008

5.8 07/01/2008

6.1

08/01/2008

6.1 08/01/2008

6.3

09/01/2008

6.1 09/01/2008

6.4

10/01/2008

6.5 10/01/2008

6.8

4 11/01/2008

6.8 11/01/2008

7.2

12/01/2008

7.3 12/01/2008

7.7

Which chart should I use?
5

Resources & Tools
Keyboard Shortcuts
Save time and clicks: https://exceljet.net/keyboard-shortcuts
Tutorials
Google is there for you. Also, try: https://digitaldefynd.com/best-exceltutorial-training-course-classes/
Build a Map
Tableau Public: http://public.tableau.com/en-us/s/
Data Automation
FRED: http://fred.stlouisfed.org/ FRED Excel Add-in: http://research.stlouisfed.org/fred-addin/
Inspiration
Read Hans Rosling’s Factfulness or Edward Tufte’s works
6

Summary and Takeaways
Goal: Make data easy to understand
• Simplify and beautify visual information • Tell a true story
Longer-term goals
• Build dynamic analytical tools • Leverage data automation and reporting
7

Questions?
Kate Watkins
Chief Economist • Legislative Council Staff [email protected] • (303) 866-3446
www.leg.colorado.gov/lcs
8
TechniquesUnemployment RateBureau