Previously data related to companies which were paid by this fictional small business (payees) was copied from Financial_Report and pasted into the Payees worksheet. Many of the payees appear more than once. This can be remedied by removing duplicate data. But first, take some time to format the worksheet. The first change will be to widen the columns.
- Widen the columns so that all of the text is visible.
While the colors and formatting of the table appear the same, when the data was copied and pasted, the Excel table features were not copied with it. The column headings no longer take the place of the lettered column headers. And while the Total Row still shows the count, this is now simply a number, not based on a formula or function.
a. To see that the Total Row no longer uses formulas or functions,
Click in cell B214
The formula bar shows simply, "211," not a formula or function that calculates the count.
The goal of this worksheet is to see each payee, more specifically each person or company considered an expense, and the corresponding category. Many of the payees appear in this worksheet multiple times. The Remove Duplicates feature can be used to narrow the data to show only one occurrence of each payee.
- To move to the Data tab, on the Ribbon,
Click the Data tab
- To open the Remove Duplicates dialog box, in the Data Tools group,
NOTE: The Remove Duplicates dialog box can look very different depending on your operating system.
Office for Windows: The Remove Duplicates dialog box opens:
Office for Mac: The Remove Duplicates dialog box opens:
It is important to note that your data has headers. This informs Excel that the first row of data should not be included when searching for and removing duplicates.
- Confirm that the "My data has headers" checkbox is checked.
Remember, you are trying to create a list that shows each payee a single time. Therefore, you want to remove the duplicate entries for payees, but not for category.
NOTE: This step is different for Office for Mac than Office for Windows.
- To uncheck the Category field and search for duplicate data only in the Payee field,
Click the Category checkbox, Click
- To uncheck the Category field (Column B) and search for duplicate data in the Payee field (Column A),
Click the Column B checkbox, Click
A dialog box appears. Informing you that there were 124 duplicate values found and removed; 89 unique values remain.
- Close the dialog box.
Depending on where your cursor was when the duplicates were removed, you may be looking at blank cells.
- Scroll up to see the data, if necessary.
The results aren't quite what was expected. When looking at the worksheet, there are several occurrences of "Invoice #------." Because the number of each invoice is different, these entries were not treated as duplicates. Another problem is that the goal of this particular exercise is to create of list of people or companies paid by this fictional small business. These invoices are listed as income, not expenses. A filter can be used to hide the invoices and income.
The filters that were next to the column headings in the Excel table disappeared when the data was copied to a new worksheet. However, they can easily be returned.
- To select a cell that contains data,
Click any cell that contains data
- To turn on the filters, in the Sort & Filter group,
The drop-down filter options appear next to the column headings.
- To see the Category filters, in the Category heading,
- Office for PC: To remove the Income category,
Click the Income checkbox, Click
NOTE: In Office for Mac, a dialog box opens.
- Office for Mac: To remove the Income catgory, in the dialog box,
Click the Income checkbox
The list is much smaller. But there are still some problems with this data. Because the Total Row is no longer connected to the data, it still says "211" when it is obvious there are not 211 entries. Also, the Payee listed as "Carryover 2017 Balance" doesn't make any sense in this context. Both of these rows can be removed.
- To see the Payee filters, in the Payee heading,
- Office for Windows: To remove the 2017 Carryover Balance and (Blanks),
Click the 2017 Carryover Balance checkbox, Click the (Blanks) checkbox, Click
- Office for Mac: To remove the 2017 Carryover Balance and (Blanks),
Click the 2017 Carryover Balance checkbox, Click the (Blanks) checkbox
- Office for Mac: Close the dialog box.
- Save the workbook.
Our data is now a complete list of companies paid by this fictional small business.