How to Filter and Sort Reports in Excel and Google Sheets
After exporting your reports from Civic Review, you can use powerful filtering and sorting tools in Excel and Google Sheets to analyze your data. This guide will walk you through a few common ways to organize your information.
Sorting Alphabetically (A-Z)
To quickly organize your data alphabetically, you can sort any column from A to Z. This is useful for organizing your list of businesses, applicants, or any other text-based column.
In Excel:
Select the column you want to sort.
Go to the "Data" tab in the ribbon.
Click the "Sort A to Z" icon.
In Google Sheets:
Select the column you want to sort.
Go to "Data" in the menu bar.
Click on "Sort range" and choose "Sort by column A (A-Z)."
Sorting by Dates
Sorting by date allows you to view your records chronologically, which is helpful for tracking deadlines, application submissions, or approvals.
In Excel:
Select the column containing the dates.
Go to the "Data" tab.
Click the "Sort Oldest to Newest" or "Sort Newest to Oldest" icon.
In Google Sheets:
Select the column with the dates.
Go to "Data" > "Sort range."
Choose the date column and select "Sort Z to A" for newest first or "Sort A to Z" for oldest first.
Calculating a Total for Fees
You can easily sum a column of fees to find the total revenue collected for a specific report.
In Excel:
Click on an empty cell where you want the total to appear.
Type the formula
=SUM(and then select the range of cells in the fees column you wish to add.Close the parenthesis
)and press Enter. For example,=SUM(B2:B50).
In Google Sheets:
Select an empty cell.
Type
=SUM(and then click and drag to select the column of fees.Press Enter to get the total.
Finding the Total Number of Licenses or Projects
To get a quick count of how many items are in your report, you can use the COUNT function.
In Excel:
Click on an empty cell.
Type
=COUNT(and then select a column that contains data for every row, such as the permit number column.Close the parenthesis and press Enter. This will give you the total count.
In Google Sheets:
Select an empty cell.
Type
=COUNT(and select the column you want to count.Press Enter.
Filtering by Project or License Type
Filtering allows you to hide all data except for a specific type, which is useful for focusing on a single category of licenses or projects.
In Excel:
Click on any cell in your dataset.
Go to the "Data" tab and click the "Filter" button (it looks like a funnel).
Click the dropdown arrow that appears in the header of the "Project Type" column.
Uncheck "Select All" and then check only the specific project type you want to see.
In Google Sheets:
Select your data range.
Go to "Data" and click "Create a filter."
Click the filter icon that appears in the header of the "Project Type" column.
In the filter menu, click "Clear" and then select the specific project type you want to display.