How to Learn Advanced Excel

Excel is a commonly used spreadsheet editor that is part of the Microsoft Office Suite. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It is not as powerful as Stata or SAS, but it is more widely used. Learning not only Excel basics, but advanced techniques, will likely be extremely worthwhile for public policy researchers and students, in general.

How can I learn Excel?

The best way to learn Excel is with a spreadsheet and good example data. Students at Penn come from very different backgrounds with regards to familiarity to Excel, and even more advanced students may have holes in their understanding. Accordingly, the following list is a set of skills from Penn Libraries that we recommend learning.These pages include sample data, tutorials, videos, cheat sheets, and workshop plans.

“Excel can produce a variety of charts including pie charts, line graphs, stock and ticker charts, surface charts and scatter plots. Explore a variety of chart types with the same dataset to see what visual insights you can inspire!”

“You can sort data, including multiple levels of sorting (state, then city for example) and you can sort by format or color. Filters allow you to quickly look at parts of your data and collect subtotals; they are also an excellent tool for data cleaning and validation. Pivot tables provide automation of higher-level analysis, answering “what-if” questions and helping you to make sense of your data.”

“Excel has hundreds of formulas and functions. Excel can be used for manipulating data, correcting errors, reformatting data that is brought in from a website, Word document or external database, creating mailing labels and certificates, and much more.”

“Although Excel is not as powerful as formal statistical analysis software (see guide), you can use Excel to run regression analysis, T-tests, compute standard deviations, create histograms. etc.”

Resources on Campus

Weigle Information Commons, in Van Pelt Library, provides high-quality in-person workshops, where beginners can learn how to create spreadsheets, enter and format data, use mathematical calculations, and more. WIC also offers office hours where you can ask for help.

During the academic year, WIC teams up with Penn Wharton PPI to offer an Excel for Public Policy course in the new Collaborative Classroom in Van Pelt Library. Check our workshop schedule for any upcoming sessions.

Other Helpful Resources

Some Penn students (depending upon school/program) receive a free Lynda account (must log in with PennKey). Lynda offers professionally made video tutorials on a wide range of subjects, and its Excel offerings are particularly strong.

<h3>Federal Reserve Economic Data (FRED®)</h3><p><strong><img width="180" height="79" alt="" src="/live/image/gid/4/width/180/height/79/481_fred-logo.rev.1407788243.jpg" class="lw_image lw_image481 lw_align_right" data-max-w="222" data-max-h="97"/>An online database consisting of more than 72,000 economic data time series from 54 national, international, public, and private sources.</strong> FRED®, created and maintained by Research Department at the Federal Reserve Bank of St. Louis, goes far beyond simply providing data: It combines data with a powerful mix of tools that help the user understand, interact with, display, and disseminate the data.</p><p> Quick link to data page: <a href="http://research.stlouisfed.org/fred2/tags/series" target="_blank">http://research.stlouisfed.org/fred2/tags/series</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>

<h3>The Penn World Table</h3><p> The Penn World Table provides purchasing power parity and national income accounts converted to international prices for 189 countries/territories for some or all of the years 1950-2010.</p><p><a href="https://pwt.sas.upenn.edu/php_site/pwt71/pwt71_form.php" target="_blank">Quick link.</a> </p><p>See all <a href="/data-resources/">data and resources</a> »</p>

<h3>National Center for Education Statistics</h3><p><strong><img width="400" height="80" alt="" src="/live/image/gid/4/width/400/height/80/479_nces.rev.1407787656.jpg" class="lw_image lw_image479 lw_align_right" data-max-w="400" data-max-h="80"/>The National Center for Education Statistics (NCES) is the primary federal entity for collecting and analyzing data related to education in the U.S. and other nations.</strong> NCES is located within the U.S. Department of Education and the Institute of Education Sciences. NCES has an extensive Statistical Standards Program that consults and advises on methodological and statistical aspects involved in the design, collection, and analysis of data collections in the Center. To learn more about the NCES, <a href="http://nces.ed.gov/about/" target="_blank">click here</a>.</p><p> ﻿Quick link to NCES Data Tools: <a href="http://nces.ed.gov/datatools/index.asp?DataToolSectionID=4" target="_blank">http://nces.ed.gov/datatools/index.asp?DataToolSectionID=4</a></p><p> Quick link to Quick Tables and Figures: <a href="http://nces.ed.gov/quicktables/" target="_blank">http://nces.ed.gov/quicktables/</a></p><p> Quick link to NCES Fast Facts (Note: The primary purpose of the Fast Facts website is to provide users with concise information on a range of educational issues, from early childhood to adult learning.): <a href="http://nces.ed.gov/fastfacts/" target="_blank">http://nces.ed.gov/fastfacts/#</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>

<h3>Internal Revenue Service: Tax Statistics</h3><p><img width="155" height="200" alt="" src="/live/image/gid/4/width/155/height/200/486_irs_logo.rev.1407789424.jpg" class="lw_image lw_image486 lw_align_left" srcset="/live/image/scale/2x/gid/4/width/155/height/200/486_irs_logo.rev.1407789424.jpg 2x" data-max-w="463" data-max-h="596"/>Find statistics on business tax, individual tax, charitable and exempt organizations, IRS operations and budget, and income (SOI), as well as statistics by form, products, publications, papers, and other IRS data.</p><p> Quick link to <strong>Tax Statistics, where you will find a wide range of tables, articles, and data</strong> that describe and measure elements of the U.S. tax system: <a href="http://www.irs.gov/uac/Tax-Stats-2" target="_blank">http://www.irs.gov/uac/Tax-Stats-2</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>

<h3>Congressional Budget Office</h3><p><img width="180" height="180" alt="" src="/live/image/gid/4/width/180/height/180/380_cbo-logo.rev.1406822035.jpg" class="lw_image lw_image380 lw_align_right" data-max-w="180" data-max-h="180"/>Since its founding in 1974, the Congressional Budget Office (CBO) has produced independent analyses of budgetary and economic issues to support the Congressional budget process.</p><p> The agency is strictly nonpartisan and conducts objective, impartial analysis, which is evident in each of the dozens of reports and hundreds of cost estimates that its economists and policy analysts produce each year. CBO does not make policy recommendations, and each report and cost estimate discloses the agency’s assumptions and methodologies. <strong>CBO provides budgetary and economic information in a variety of ways and at various points in the legislative process.</strong> Products include baseline budget projections and economic forecasts, analysis of the President’s budget, cost estimates, analysis of federal mandates, working papers, and more.</p><p> Quick link to Products page: <a href="http://www.cbo.gov/about/our-products" target="_blank">http://www.cbo.gov/about/our-products</a></p><p> Quick link to Topics: <a href="http://www.cbo.gov/topics" target="_blank">http://www.cbo.gov/topics</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>

<h3>National Bureau of Economic Research (Public Use Data Archive)</h3><p><img width="180" height="43" alt="" src="/live/image/gid/4/width/180/height/43/478_nber.rev.1407530465.jpg" class="lw_image lw_image478 lw_align_right" data-max-w="329" data-max-h="79"/>Founded in 1920, the <strong>National Bureau of Economic Research</strong> is a private, nonprofit, nonpartisan research organization dedicated to promoting a greater understanding of how the economy works. The NBER is committed to undertaking and disseminating unbiased economic research among public policymakers, business professionals, and the academic community.</p><p> Quick Link to <strong>Public Use Data Archive</strong>: <a href="http://www.nber.org/data/" target="_blank">http://www.nber.org/data/</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>