• <div class="header-image" style="background-image: url(/live/image/gid/4/2206_header_v5n7.rev.1500657916.jpg);">​</div><div class="header-background-color"/>

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 Basics

“Basic use of Excel involves understanding field types; controlling the format and size of cells; entering, copying and moving data; and referring to cells in basic formulas such as sum and average.”

Charts

“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!”

Data Analysis

“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.”

Formulas

“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.”

Statistics

“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.
  • Microsoft’s Excel Help website
  • Google searches of a specific question or topic (e.g., shortcut keys) will often yield a very useful result, and potentially even sample data with which to work. You also can find more useful guides based on your level of familiarity, like this one for beginners or this one for advanced users.

PENN WHARTON PPI
RESOURCE SPOTLIGHT:

  • <h3>USDA Nutrition Assistance Data</h3><p><img width="180" height="124" alt="" src="/live/image/gid/4/width/180/height/124/485_usda_logo.rev.1407789238.jpg" class="lw_image lw_image485 lw_align_right" srcset="/live/image/scale/2x/gid/4/width/180/height/124/485_usda_logo.rev.1407789238.jpg 2x, /live/image/scale/3x/gid/4/width/180/height/124/485_usda_logo.rev.1407789238.jpg 3x" data-max-w="1233" data-max-h="850"/>Data and research regarding the following <strong>USDA Nutrition Assistance</strong> programs are available through this site:</p><ul><li>Supplemental Nutrition Assistance Program (SNAP) </li><li>Food Distribution Programs </li><li>School Meals </li><li>Women, Infants and Children </li></ul><p> Quick link: <a href="http://www.fns.usda.gov/data-and-statistics" target="_blank">http://www.fns.usda.gov/data-and-statistics</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>
  • <h3>MapStats</h3><p> A feature of FedStats, MapStats allows users to search for <strong>state, county, city, congressional district, or Federal judicial district data</strong> (demographic, economic, and geographic).</p><p> Quick link: <a href="http://www.fedstats.gov/mapstats/" target="_blank">http://www.fedstats.gov/mapstats/</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>
  • <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>Federal Aviation Administration: Accident & Incident Data</h3><p><img width="100" height="100" alt="" src="/live/image/gid/4/width/100/height/100/80_faa-logo.rev.1402681347.jpg" class="lw_image lw_image80 lw_align_left" srcset="/live/image/scale/2x/gid/4/width/100/height/100/80_faa-logo.rev.1402681347.jpg 2x, /live/image/scale/3x/gid/4/width/100/height/100/80_faa-logo.rev.1402681347.jpg 3x" data-max-w="550" data-max-h="550"/>The NTSB issues an accident report following each investigation. These reports are available online for reports issued since 1996, with older reports coming online soon. The reports listing is sortable by the event date, report date, city, and state.</p><p> Quick link: <a href="http://www.faa.gov/data_research/accident_incident/" target="_blank">http://www.faa.gov/data_research/accident_incident/</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>
  • <h3>NOAA National Climatic Data Center</h3><p><img width="200" height="198" alt="" src="/live/image/gid/4/width/200/height/198/483_noaa_logo.rev.1407788692.jpg" class="lw_image lw_image483 lw_align_left" srcset="/live/image/scale/2x/gid/4/width/200/height/198/483_noaa_logo.rev.1407788692.jpg 2x, /live/image/scale/3x/gid/4/width/200/height/198/483_noaa_logo.rev.1407788692.jpg 3x" data-max-w="954" data-max-h="945"/>NOAA’s National Climatic Data Center (NCDC) is responsible for preserving, monitoring, assessing, and providing public access to the Nation’s treasure of <strong>climate and historical weather data and information</strong>.</p><p> Quick link to home page: <a href="http://www.ncdc.noaa.gov/" target="_blank">http://www.ncdc.noaa.gov/</a></p><p> Quick link to NCDC’s climate and weather datasets, products, and various web pages and resources: <a href="http://www.ncdc.noaa.gov/data-access/quick-links" target="_blank">http://www.ncdc.noaa.gov/data-access/quick-links</a></p><p> Quick link to Text & Map Search: <a href="http://www.ncdc.noaa.gov/cdo-web/" target="_blank">http://www.ncdc.noaa.gov/cdo-web/</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>
  • <h3>The World Bank Data (U.S.)</h3><p><img width="130" height="118" alt="" src="/live/image/gid/4/width/130/height/118/484_world-bank-logo.rev.1407788945.jpg" class="lw_image lw_image484 lw_align_left" srcset="/live/image/scale/2x/gid/4/width/130/height/118/484_world-bank-logo.rev.1407788945.jpg 2x, /live/image/scale/3x/gid/4/width/130/height/118/484_world-bank-logo.rev.1407788945.jpg 3x" data-max-w="1406" data-max-h="1275"/>The <strong>World Bank</strong> provides World Development Indicators, Surveys, and data on Finances and Climate Change.</p><p> Quick link: <a href="http://data.worldbank.org/country/united-states" target="_blank">http://data.worldbank.org/country/united-states</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>
  • <h3>HUD State of the Cities Data Systems</h3><p><strong><img width="200" height="200" alt="" src="/live/image/gid/4/width/200/height/200/482_hud_logo.rev.1407788472.jpg" class="lw_image lw_image482 lw_align_left" srcset="/live/image/scale/2x/gid/4/width/200/height/200/482_hud_logo.rev.1407788472.jpg 2x, /live/image/scale/3x/gid/4/width/200/height/200/482_hud_logo.rev.1407788472.jpg 3x" data-max-w="612" data-max-h="613"/>The SOCDS provides data for individual Metropolitan Areas, Central Cities, and Suburbs.</strong> It is a portal for non-national data made available through a number of outside institutions (e.g. Census, BLS, FBI and others).</p><p> Quick link: <a href="http://www.huduser.org/portal/datasets/socds.html" target="_blank">http://www.huduser.org/portal/datasets/socds.html</a></p><p>See all <a href="/data-resources/">data and resources</a> »</p>