User: I have a large Excel spreadsheet with hundreds of rows of data and need to analyze and summarize the data. I know nothing about Access and need something quick.
ITS helper: Microsoft Access is great if you know it, but it requires quite an investment in time to learn Access. Before exporting into Access, try creating pivot tables in Excel. If your data is set up with the top row as column headings rows with your data, it should be ideal for creating a variety of summarized reports. Just follow these easy steps to create a quick pivot table:
- Place your cursor within the data.
- Click Insert > Pivot Table. Excel will usually determine the range based on empty rows and columns. If the range is not correct, you can drag to select your range.
- Select New Worksheet. It is suggested that you rename your new worksheet “Pivot Table” and your original data to something like “Data” so that you can easily identify the raw data vs. the pivot table data. (Right click on the worksheet tab and click Rename). As you add additional pivot tables, make the name more descriptive.
- In the right side you will see the Pivot Table panel. Place checks to select the field (column headings) to show in your pivot table. By default, text fields appear in row labels and numeric data appears in the Values section. You can drag and drop to “pivot” your fields to different sections for a completely different look. While you are working in the pivot table, there are a number of Pivot Table tools available in Options and Design tabs.
Learn more about Excel Pivot Tables in ITS’ Autumn Tricks and Treat Session on Thursday from 9 to 10:30 a.m. Register online.