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