Administrative Systems

Excel Useful Functions

TO OPEN A DOWNLOAD INTO EXCEL: Once you have entered Parameters, selected Download & hit SUBMIT -> Save the file to your computer (NOTE: if you are asked to open the file directly - do not - close the dialog box then move to step 3). Open Excel. Use the FILE then OPEN option to locate the file you saved and open the file into excel. A text Import Wizard box will appear.

  • At Step1 - make sure DELIMITED is selected, press NEXT.
  • At Step2 - make sure TAB is selected. Select COMMA as well, press NEXT.
  • At Step3, press FINISH.
TO SORT DATA:
  • Highlight the entire worksheet by clicking in the square between the Column A and Row 1.
  • Click DATA then SORT.
  • A Sort box will appear - you will be able to sort by up to 3 columns.


    • EXAMPLE: Sort your worksheet by FUND, then by ORGN, then by ACCOUNT.
TO FILTER DATA:
  • Highlight the entire worksheet by clicking in the square between the column A and Row 1.
  • Click DATA then FILTER then point to AUTO FILTER - Arrows will appear in the column heading of each row.
  • To filter a column, click the arrow - point to CUSTOM.
  • A Custom AutoFilter box will open - this will give you several options for how you want to filter the information


    • EXAMPLE: I am going to filter the DESCRIPTION column of the Financial Transactions download. At the Custom AutoFilter box - I choose that the Description contains the word OFFICEMAX - once I click OK - I will get a list of all transactions for Office Max. NOTE: The Filter function can be very useful to search for specific transactions. In this example I am searching for transactions for a particular vendor. You can also search for transactions of a specific amount, specific document number, specific transaction date, etc…
TO SUBTOTAL DATA:
  • Highlight the entire worksheet by clicking in the square between the column A and Row 1.
  • Click DATA then SUBTOTAL.
  • A Subtotal box will appear - select the column you would like to subtotal and the function you would like to use.


    • EXAMPLE: I am going to subtotal the Financial Transactions download by Account.
    At each change in ACCOUNT, use function SUM, add subtotal to TRAN AMOUNT.
    This will give me the total transaction amount for each Account.