Excel Tip: Pivot Table Tutorial: Data Extractionby
This is one in a series of Excel Pivot Table Tutorials developed by AccountingWEB software consultant David Carter. The tutorials were originally developed for a UK audience, but the techniques work equally well for US needs.
Be sure to check out Carter's other pivot table tutorials, including:
- Budgeting with the Excel Pivot Tables
Analyzing Sales Data Using Excel Pivot TablesData Extraction Using Excel Pivot TablesAudit & Financial Reporting Using Excel Pivot TablesBudgeting With Excel Pivot Tables - Part IIModelling and What-If? Analysis Using Excel Pivot Tables
DATA EXTRACTION USING EXCEL PIVOT TABLES
David Carter's latest tutorial is quite possibly his most exciting. By harnessing the power of pivot tables with the ubiquitous software Sage, he estimates that the whole process of accounts production could be turned on its head - accounts could take up to 25% less time, there would be better management reports, and costly visits to the client could be eradicated.
"I believe this will revolutionise accounts production. The accountant can ask his client to forward the data over the internet, produce the accounts in Excel, then e-mail them back to the client again," he said. "A prediction: within three years, 30% of accountants will be producing management accounts from Sage entirely via Excel pivot tables."
A bold prophecy, but past users will know of the immense potential of the pivot table. If you haven't seen these remarkable Excel tools in action, visit David's past two tutorials - the first on budgeting, the second on analysing sales data with a text file.
Users of Sage Line 50 and Instant Accounting will benefit most from the following tutorial, the first in a two-part series. Nominal activity reports - the standard file of these programs - can be saved in a CSV format, and are ready to be transformed into the quick and simple lines of the pivot table.
However, if you are in a country such as the USA which does not support the UK's DD/MM/YY date format, you should download the Nomactiv.txt file instead and use Excel's import wizard to change to your own date formats (instructions given below). Otherwise Excel's "Group" feature won't be able to group the dates into months.
Please read the instructions below, but if you have any trouble downloading either file from Accountingweb, send an email to Robert Benson and we will email you a copy by return. Please send any comments you have on the tutorial to the same address, putting 'pivot table comment' in the subject line.
STARTUP INSTRUCTIONS â PLEASE READ THESE CAREFULLY
STARTUP INSTRUCTIONS â PLEASE READ THESE CAREFULLY.
To run this tutorial you will need to have Microsoft Word and Excel on your own PC.
The tutorial consists of two files, a Word file and Sage data file named NOMACTIV. NOMACTIV contains the raw data you are going to analyse via pivot tables. The Word file contains the instructions on how to create those pivot tables in Excel. Your next steps will be to:
1) Download NOMACTIV and the Word file from Accountingweb on to your own PC
2) Disconnect from the Internet.
3) Print out the Word file containing your instructions. (17 pages)
4) Start up Excel and, with the instructions before you, work your way through the tutorial.
DOWNLOAD PROCEDURE â WORD FILE
You have two files to download from Accountingweb. Take the Word file first, as follows:
Double click on the underlined Word file. The egg-timer appears. The file is 120 kb in size and will take about 20 seconds to download. When it appears on the screen, select: File - Save As from your menu. Wait a few seconds. The Save As box appears. First, select a suitable folder to save the file in. Then, save as: File name: Sageone Files of Type: Word Document
After it has been saved, click on Back in your menu. You are returned to Accountingweb.
DOWNLOAD PROCEDURE â âNOMACTIVâ FILE
(UK users) Click on Nomactiv.csv. One of two things will happen.
Either: the File Download box appears. Accept the default âSave the file to diskâ. The Save As box offers a file name of : NOMACTIV.CSV and files of type: Excel Comma Separated Values. Accept these defaults and save the file to disk.
Or: the File Download box doesn't appear and transactions begin to appear on your screen, with âDemonstration Exhibitions Ltdâ in the left column. The scroll bar on the right moves up to the top of the screen. The file will have been downloaded when you can pull the RH scroll bar down to the bottom of the screen and you can see the last account â 9999, Mispostings account.
When the file as been downloaded, select: File: Save As. Wait a few seconds. The Save As box appears, offering File name = NOMACTIV_CSV and Files of Type = Web page, complete
Change Web page, complete to Text file. Also change filename from NOMACTIV_CSV to NOMACTIV.
Now open the file in Excel and use the Text Import Wizard, (see instructions just below).
(Non-UK users with non DD/MM/YY date formats)
Click on Nomactiv.txt. Save the file as a text file.
Start up Excel. Import the NOMACTIV.TXT file into Excel as follows:
File - Open. Open the folder that contains NOMACTIV.TXT You can't see NOMACTIV.TXT.
Files of Type: (at the bottom) change from Microsoft Excel Workbook to Text Files
NOMACTIV now appears. Open it.
The Text Import Wizard, Step 1 of 3 appears.
Make sure that the Data Type is Delimited, not Fixed Width. Click: Next.
The Text Import Wizard - Step 2 of 3 appears. Click on: Comma
Vertical lines appear between the columns Click Next. Step 3 of 3 appears.
The first column is highlighted. At the head of the column is the format which Excel assumes it will be in when it is imported â the âGeneralâ format. Excel assumes GENERAL for all fields.
In the top right of the screen are the possible Column data formats - General Text Date & Skip (i.e. do not import).
General format is OK for all your fields except any date fields
Scroll to the column that contains dates. The first one is 31/12/1996.
You will need to change the date format above this column from General to DMY.
In the Column Data Format box the âDateâ option should have the default value DMY against it. This means that Excel is expecting that any Date fields it finds in the text file will be in Day/Month/Year format, which is the UK standard.
If DMY is not the default offered, select DMY from the list so that it is.
Click on the âGeneralâ heading of the third column, DATE. The column is highlighted
At the top right click on: Date. The heading of the DATE column should now read DMY.
Click on Finish. Wait a moment. The file is imported into Excel, with correctly formatted dates.
If you have any trouble downloading either file from Accountingweb, send us an email on [email protected] and we will email you a copy by return.
TO PRINT A SECTION OF THIS TEXT
First make sure that your printer is switched on. Then highlight the section of text you want to print. (to highlight, hold down the left mouse key and drag downwards).
Now right click the mouse on the highlighting. A small menu appears, with âPrintâ at the bottom.
Select: Print. Wait a few seconds. The âPrintâ box of your printer appears.
In the âPrint Rangeâ section, change from All to Selection. Then click OK to print.
INTRODUCTION AND BACKGROUND TO THE SAGE LINE 50 TUTORIAL
This tutorial (Sageone.doc) will show you how to take raw data from a Sage accounting system, load it into Excel, modify the data for use in a pivot table, then use a pivot table to generate a Trial Balance.
Part Two of the tutorial (Sagetwo.doc) shows you how to enter adjustments, extract a Profit and Loss report, and produce departmental profitability reports. This will be available in mid-February.
Part one (this tutorial) should take you about 45 minutes to work through. If possible, it is a good idea for two people to do it together, one reading the instructions, the other operating the keyboard.
You will make extensive use of both left and right mouse buttons. When I say âright clickâ I mean click the right mouse button. When I say âclickâ on its own, I mean âclick the left mouse buttonâ.
You can run this tutorial on any version of Excel from Excel Five (released in 1994) onwards, that is, on Excel 5, on Excel 7 (for Windows 95), on Excel 97 or on Excel 2000. The tutorial was developed and tested on Excel 97. Notes in square brackets  indicate where other versions of Excel differ. If you are not sure which version you have, find out now by starting up Excel. At the right of the main menu click on Help, then About Microsoft Excel. The version is at the top of the screen.
Finally, before starting you need to be sure that the date formats on your PC are correctly set for your part of the world. Otherwise the pivot table may not be able to group dates correctly. Click on: Start - Settings - Control Panel - Regional Settings. If you are based in the United Kingdom they should read English (United Kingdom) or English (British).
Before starting on this tutorial you should have already gone through the previously published tutorial, BUDGDEM (âBudgeting with Excel pivot tablesâ), which is designed to familiarise you with the basic workings of pivot tables. This Sage tutorial is more advanced in that it is considerably longer, and much of it is concerned with preparing the data for analysis.
BACKGROUND TO THE TUTORIAL
In BUDGDEMO and SALANAL the data was pre-processed for you and you were able to start analysing it straight away. Unfortunately, however, in the real world it's not like this. Before you can even start analysing, there may be a lot of work to be done in getting the raw data out of your accounts package in the right format. (If you have tried using pivot tables on your own company's data, you may already have found this out.)
In this tutorial you will import some raw data from a third-party accounts package and see what's involved in setting it up to be analysed. The package will be Sage Line 50 (previously âSterlingâ).
Different accounts packages differ in the amount of additional work they require of you. Sage is one of the best. Both Line 50 and Instant Accounting come with a predefined âNominal Activityâ report which will output directly into Excel. After that there are only a couple of small modifications to make and you are ready to go.
This tutorial is based upon some sample data supplied by Sage with their Instant Accounting 98 package. It relates to a company called âDemonstration Exhibitions Ltdâ. The export formats of Instant Accounting and Line 50 are virtually identical, and this tutorial is appropriate for both.
The final pages of this document are a Trial Balance from Sage. Please locate them now.
The printed instructions have a twofold purpose: on the one hand to help you with the tutorial, on the other to help you to download and analyse data from any Line 50 or Instant package. Therefore please use them to try to download and analyse your own Sage data and see how you get on.
On the next page are instructions on how to download data from your own copy of Sage or Instant.
If you are doing the tutorial, the data is already downloaded, so ignore Section A and go straight to section B.1 on page 4 - âIMPORT THE NOMINAL ACTIVITY CSV FILE INTO EXCELâ.
HOW TO EXPORT YOUR OWN DATA FROM SAGE INTO EXCEL
(if you are working through the tutorial, you already have the exported data. Go to Section B)
A. 1. EXPORT THE NOMINAL ACTIVITY REPORT AS A âCSVâ FILE
Start up Sage. You need to output the contents of Sage's nominal transaction file.
From the menu with icons, select: Nominal. Then select: Reports. A list of reports appears.
The standard nominal transaction report in Sage is âNominal Activityâ (NOMACT_SRT). The tutorial is built around this report. However, âNominal Activity (with A/C ref)â (NLACTIV_SRT) is available on some Sage versions. If you have this report, use it, since it is âpivot-table readyâ and after keying in the column headings you can go to section B.8 and start reproducing the Trial Balance straight away. Similarly, if you analyse by department in Sage, âNominal Departmental Analysis (Detailed)â is also pivot-table ready.
From the list of reports, highlight Nominal Activity. Then: Run
The Criteria box appears. The Sage demonstration company has no Brought Forward balances (see bottom LH corner). Therefore click OK to accept the defaults. Wait.
After a few moments the Nominal Activity report appears on screen.
From the menu at the bottom of the screen select: Save as. The File Save As box appears.
Folder c:excelsage (or whichever folder where you want to save this data)
Save File as type: Comma Separated Files (*.csv)
OK The report is saved to disk
A. 2. PRINT FIRST PAGE OF THE NOMINAL ACTIVITY REPORT
The exported file will come over as raw transactions only, that is, without any column headings. You may find it helpful to print out the first page of the printed report to compare with the exported file. To do this, from the menu at the bottom of the screen, select: Print
In the âPrint Rangeâ box to the right, change âAllâ to âRangeâ. From 1 to 1 - OK
The first page of the report is printed, with column headings.
A. 3. PRINT OFF A TRIAL BALANCE REPORT
Your next task is to print out a Trial Balance report from Sage. You will use this report as a control. When the pivot table in Excel matches the Sage Trial Balance you can be sure that the data has been brought over correctly. Only then should you move on to the next stage.
From the lower menu with icons, select: Financials. Then: Trial
The âCriteriaâ box should offer the year-end as default. OK to accept the default.
Three âOutputâ options are available: Printer, Preview or File. Select Printer - OK.
The Trial Balance is printed. When it is finished, click OK at bottom right.
This completes your work in Sage. Close it down, then load up Excel.
B. 1. IMPORT THE âNOMINAL ACTIVITYâ CSV FILE INTO EXCEL
Start up Excel. Import the NOMACTIV nominal transactions file as follows:
File - Open Go to the folder that contains NOMACTIV.CSV. You can't see the file
Change Files of Type: to All files NOMACTIV appears in the list. Open it.
A lot of data appears. There are no column headings. In column A is âDemonstrationâ, in column B nominal codes, in column C descriptions, etc.
Highlight the whole sheet by clicking onto the top LH brick above 1 and left of A. Then:
Double click between column headings A and B to adjust all columns to best fit.
Now click on the â1â brick to the left of Row 1. The whole row is highlighted
Select: Insert - Rows to make room for some column headings. Then:
Type the following headings (in capitals) along Row 1: COMPANY, NLAC, NLNAME, BALANCE, NO, TYP, DATE, REFNO, DETAILS, DEP, TC, VALUE, DEBIT, CREDIT
Remove all columns to the right of CREDIT [i.e. column O onward - highlight column, then: Edit â Delete]
Also remove column A, Demonstration Exhibitions [highlight column A, then: Edit â Delete]
Make the column headings in Row 1 bold [Select Row 1 - click B on the menu bar].
Now click on the â2â brick to the left of Row 2. The whole row is highlighted
Freeze the column headings by selecting from the main menu: Window - Freeze Panes
Now highlight the BALANCE column by clicking the âCâ column header. Then, from the menu:
Format â Cells â Number -Number category - Dec Places 2 - 1000 separator? YES - â1234.10 in red at the bottom
[Excel 5 users â from the list select: #,##0.00;[Red]-#,#00.00]
Apply the same formatting to column K, VALUE [tip: highlight the column, then press F4 key].
Also apply the formatting to the DEBIT (L) and CREDIT (M) columns.
From columns A to M your column headings should read: NLAC, NLNAME, BALANCE, NO, TYP, DATE, REFNO, DETAILS, DEP, TC, VALUE, DEBIT, CREDIT
Widen column I, so that the âDEPâ heading is visible.
Select: File - Save as: NOMACTIV - Save as Type: change to Excel Workbook
B. 2. CONTENTS OF THE DATABASE
Excel can read CSV files directly, so the NOMACTIV file opened straight away. (If you remember, in the Sales Analysis tutorial you had to use the Text Import wizard to load the TXT file).
Let's take a look at the data that's come over. These are the principal fields recorded on the Nominal Transaction file in Sage. They are:
NLAC = the Nominal Account Code
NLNAME = the Nominal Account Description
BALANCE = the current Balance on the nominal account
NO = the transaction reference number. This is a unique sequential number automatically allocated to the transaction by Sage.
TYP = the Type of transaction. SI = Sales Invoice, JD = Journal Debit etc DATE = the Date of the transaction.
REFNO = the Reference Number of the transaction, i.e. Invoice no, cheque no etc
DETAILS = the narrative typed in by the bookkeeper
DEP = the Department code (if any) typed in by the bookkeeper
TC = the Tax code, i.e. 1 = 17.5% VAT, 0 = zero rate VAT etc
VALUE = the Amount of the transaction
DEBIT = the Amount expressed as a Debit
CREDIT = the Amount expressed as a Credit
The data has come over from Sage sorted first by NLAC nominal account code, then by DATE.
B. 3. MOVING AROUND THE FILE
Scroll down through the first 100 or so records to see the sort of data the file contains. Then:
Go to the bottom of the file: (tip: keyboard shortcut = hold down Ctrl key, then press Down arrow)
There are 518 rows (i.e. 518 nominal transaction records)
Return to the top of the file, cell A2. (keyboard shortcut = hold down Ctrl key, then press Up Arrow. Let go your Ctrl key. Down arrow once)
B. 4. TIDY UP THE DATA (1) - CREATE A SECOND âVALUEâ COLUMN
Sage has done a good job in bringing over most of the data you need. However, there is a bit of tidying up to do.
First, there is a problem with the VALUE (K) column. The amounts are all positives, whereas we need them to be positive only for debits, and negative for credits. We have to create another column where the values are expressed this way. We'll do this in column N. Therefore:
First highlight column N by clicking the N at the top, then select: Format â Column â Width â 11
In cell N1 type the column heading: VALUE2.
In cell N4, type the formula =L4-M4 and press Enter. #VALUE! appears in N4
This formula would express debits as positives, credits as negatives. However, Excel apparently interprets the blank in cell M4 as non-numeric and reports a #VALUE! error.
Into cell M4 type: 0 (zero). Enter. #VALUE! in cell N4 changes to 50,000.00
This has done the trick. To get the formula working throughout the worksheet we will have to replace any blanks in columns L and M with zeroes. To do this:
Click on cell L10. Then click on the AZ icon. The DEBIT column is sorted into ascending order.
Go to the bottom of the DEBIT amounts in Row 182.
Fill cells L183 to L518 with 0's, using your own method or the tip below.
[tip: Type: 0 into L183. Enter. Click back onto L183. Hold down your Shift key and it keep it held down. Now tap your Page Down key until you get past the last row of the worksheet. Tap Up Arrow until you get to row 518. Take your finger off the Shift key â the highlighting remains.
From the menu, Edit â Fill â Down. The cells are filled with 0's ]
Now go to the top of the worksheet. [tip: while holding down the Ctrl key, press the Home key]
Click on cell M10. Click the AZ icon. The CREDIT column is sorted.
Go to the bottom of the CREDIT amounts in Row 192. Then fill cells M193 to M518 with 0's.
Go to the top of the worksheet. [Ctrl+Up Arrow, then Down Arrow once]
From the main menu, select: Data - Sort - Sort by NLAC - Then by - DATE OK
The worksheet is sorted back into its original order.
Copy the formula in N4 down to N518, using your own method or the tip below.
[tip: click onto cell N4. Place the mouse pointer on the RH bottom corner of cell N4 until it becomes a black cross. Double click the mouse. The formula is copied down to the bottom of the worksheet]
The VALUE2 column now displays positive and negative balances
Type 0 into cells N2 and N3. If there are any hashes (#####) in the new column, widen it.
B. 5 âPASTE VALUESâ IN THE VALUE2 COLUMN
The VALUE2 column may contain a series of formulae. We need to convert them to numbers.
Highlight the VALUE2 column by clicking on the N at the top.
From the main menu, select: Edit â Copy Moving lines appear around the column
From the menu: Edit â Paste Special â Values - OK
Press the Escape key at top left to remove the moving lines
Click on N10 in the VALUE2 column to remove the highlighting.
Note that cell N10 now contains â93.00 not a formula, and so does the rest of column N.
Finally, we'll move VALUE2 to the left of the VALUE column, so that it is easy to see on the screen.
Click on K10, then: Insert â Columns. Highlight the VALUE2 column, then: Edit - Cut
Click on the top of the blank K column. Then: Edit â Paste. VALUE2 is copied into column K.
B. 6 TIDY UP DATA (2) - FILL IN EMPTY DATE FIELDS
There is a second problem with the data as exported by Sage. This is that where there are nominal accounts with no transactions (e.g. Freehold Property, Leasehold Property, at the top of the screen) Sage has brought over a zero account balance but no date.
One of the most useful features of pivot tables is the âGroupâ command. This groups date fields by day, week, month etc. and you will be using it shortly. However, it is a bit temperamental, and if just one record out of thousands in your database has an incorrectly formatted date or no date at all, it will refuse to work and simply say âCannot Group That Selectionâ. Therefore, whenever you import a database, always make a point of checking that the date columns are filled with valid dates before you start running any pivot tables.
Click on cell F10 in the DATE column. Click the AZ icon. The database is sorted into DATE order.
Go to Row 436. In F436 enter the date: 28/02/1997 Now copy 28/02/1997 down to row 518.
[tip: type 28/02/1997 into F437 as well. Highlight both cells. Black cross on RH bottom of F437, then double click. If you highlight one cell, Excel will increment the dates to 01/03/97, 02/03/97 etc]
Similarly, go to cell I436 in the âDEPâ column. In I436 type: 0 (zero)
Fill I436 down to I518 with zeroes.
Go to the top of the spreadsheet. [Ctrl+Home]
Now to sort the database back into NLAC/DATE order. From the menu select:
Data - Sort â Sort by NLAC - Then by: DATE. OK
The database reappears in the original order. Freehold Property and Leasehold Property in rows 2 and 3 now have dates.
The data is now ready to be analysed in an Excel pivot table.
B. 7 CHECKING THE DATA VIA SORT AND AUTOFILTER
After bringing over data, you may wish to audit and reconcile it before producing the final accounts. Apart from pivot tables themselves, Excel's SORT and AUTOFILTER commands offer phenomenal facilities for analysing thousands of transactions very quickly. To give a couple of examples:
At present the database as exported from Sage is in nominal account code (NLAC) order. Suppose you wanted to look at the original source transactions. To this, simply:
Click on cell D10, in the âNOâ column. Then click the AZ icon.
The data is now sorted in âNOâ, i.e. Transaction Number, order.
And suppose you wanted to look particularly at purchase invoices. Select: Data - Filter - Autofilter.
Black down arrows appear against each column heading.
Click on the black down arrow in E1, the âTYPâ column. A list of transaction types appears.
From the list, select: PI (purchase invoices). Only the PI type transactions are now shown.
The earliest items are opening balances and not very interesting. Go to the bottom of the list.
The last 2 purchase invoices were reference numbers 674 and 435. For each one, three lines of double entry are shown, the debits offsetting the credits.
Reconciling the Bank account often takes up a large amount of time. Suppose that you have a cheque on your bank statement for Â£2,138 and are not sure whether or not it was ever entered on Sage. AUTOFILTER enables you instantly to trace a single transaction within many thousands of records:
At the top of column E, the down arrow is blue. Click on it and select (All).
The filter is removed and all the data reappears.
(You may need to use Ctrl+Home to get to the top of the worksheet).
Now click on the down arrow in cell A1, NLAC. A list of nominal codes appears.
Scroll down to account 1200 (this is the account for Bank). Highlight it and click.
Autofilter filters out everything except Bank transactions.
Now click on the down arrow in cell K1, the VALUE2 heading.
A list appears of all the values Excel has found. From the list select: â2138.00
Excel has found the entry. It was transaction no. 189 in Sage, with no cheque number.
Close down Autofilter via: Data - Filter - Autofilter. The transactions reappear.
B. 8 REPRODUCE THE TRIAL BALANCE
Our initial task is to reproduce the Sage Trial Balance report (the last two pages of this document). If we can do this, it will prove that all the transactions have come over correctly from Sage into Excel. We'll do this via a pivot table.
When creating a pivot table, always first check that all Date fields are OK. The Group command cannot group transactions by month if there is even one faulty value in a Date column. Therefore:
Click on F10 in the DATE column. Click the AZ icon. The database is sorted into DATE order.
Confirm that the dates in Rows 2 and 3 look OK. (They do.)
Now go the end of the file [Ctrl+Down Arrow]. Confirm that the dates in the bottom rows are OK.
(They aren't. The last few transactions are dated August. But for this exercise, accept them.)
Also, note down the number of the last row. It is 518. Now go back to Row 1 [Ctrl+Home]
We'll build up the pivot table step by step. From the main menu at the top of the screen, select :
Data - Pivot Table Report [Excel 2000 = Pivot Table and Chart Report, Excel 5 = Pivot Table]
The Pivot Table Wizard, Step 1 of 4 screen appears. Click on: Next [Excel 2000 â Step 1 of 3]
Step 2 of 4 The Range box should say: $A$1:$N$518.
[Check the range carefully. The first half should always be $A$1. The second half should show the last row you noted down â 518. If the number is higher, say 519, it means the pivot table includes a blank record and the Group command will fail. Also, earlier versions of Excel don't always pick up the range automatically. If the Range box is blank or says âDatabaseâ, then: make the box blank; click the pointer inside it; click onto cell A1; type a colon; hold down Ctrl and press the âEndâ key]
Click on Next.
Step 3 of 4: The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet [note: if you are using Excel 2000, you must first click on Layout]
[if instead you get the message âA pivot table field name is not validâ¦â¦.â, then either one of the column headings is missing, or your Range includes a blank column at the far right (which has no heading, obviously). Cancel, then check Row 1 and the far right column of the Range.
Drag and drop these headings as follows:
NLAC into the ROW area.
VALUE2 into the DATA area.
[When dropped into the DATA area, VALUE2 should now say âSum of VALUE2â. If it says âCount of VALUE2â, double click on Count of VALUE2. A box appears. Change âCountâ to âSumâ]
Now click on Finish. [Excel 2000, OK - Finish]
In column A is a list of Nominal Codes. In column B is a list of âTotalsâ.
Excel has taken the 518 transaction records in the NOMACTIV worksheet and generated a pivot table that summarises them by nominal code
Look at the bottom left of your screen. To create the pivot table, Excel has generated a new worksheet to the left of NOMACTIV. Probably it is named Sheet1.
Click back onto the NOMACTIV worksheet to confirm that your transactions are still there.
Now right click back to the new sheet containing the pivot table. Rename this worksheet PIVOT.
B. 9 FORMAT THE NUMBERS
The numbers in the âTotalâ column B need to be tidied up.
First, right click on any number within the âTotalâ column.
A menu appears. It has âFormat Cellsâ either at the top or in the middle.
From the menu select: Field. [Excel 2000 = Field Settings, Excel 5 or 7 = Pivot Table Field]
The âPivot Table Fieldâ dialogue box appears. From the list of options on the right, select: Number
The âFormat Cellsâ box appears. From the âCategoryâ list, select: Number Then:
Decimal Places = 2 - Use 1000 separator? YES - click on the -1234.10 in red at the bottom of the list - OK - OK.
The pivot table now shows all figures to 2 decimal places and credits are in red with minuses.
[Excel 5 â from the list select: #,##0.00;[Red]-#,#00.00]
B. 10 ADD THE NOMINAL ACCOUNT NAME
In addition to the nominal account code, we need the account name as well:
Right click on the pivot table. The menu appears. Select: Wizard. [Excel 5 or 7 = Pivot Table]
The COLUMN-ROW-DATA box re-appears. [Excel 2000, select Layout first]
Drop NLNAME below NLAC in the ROW area. Then Finish.
The nominal description is displayed on the pivot table, but the screen is not very clear as the NLAC Nominal Code is totalling. These totals need to be removed:
Double right click on the grey NLAC field button in A2. The menu appears
[Note to Excel 2000 users. Cell references within pivot tables are usually two rows lower in Excel 2000 compared with other Excel versions. Cell A2 therefore usually signifies cell A4, and so on].
Select: Field. [or Field Settings, or Pivot Table Field]
In the Subtotals area on the left, change from Automatic to None. OK
That looks better. Now left click on NLAC in cell A2. The column is highlighted.
[Note 1. Excel 2000, move pointer above NLAC until you get a black down arrow, then left click]
[Note 2. If you can't get the highlighting or the black down arrow to work, you may have the Selection feature switched off. To switch it on: Right click for menu - Select - Enable Selection]
Centre the nominal account codes by clicking the Centre icon.
Click on cell A4, two lines below the grey NLAC field button. [Excel 2000, click on A6]
Select: Window - Freeze Panes
Now go down to the bottom row (Row 148). The Grand Total is zero, as it should be in a Trial Balance.
Check the account balances in the pivot table against your printed Trial Balance.
They match. The data has been imported correctly and you can move to the next stage.
B. 11. ADD âBALANCEâ IN THE PIVOT TABLE
In fact, strictly speaking you didn't need a copy of the Trial Balance report, since Sage obligingly have brought over the account balance in the BALANCE field.
Right click on the pivot to bring up the menu. Select: Wizard
Drop BALANCE into the ROW field under NLNAME. Then:
In the ROW area, double click on NLNAME. Change Subtotals from Automatic to None. - OK.
Finish. The pivot table is recalculated and a âBalanceâ column is added.
The balance is positive, whether debit or credit. Check it against the Total column as follows:
Click on cell E3. From menu, select: Insert - Function.
In the box there are two lists. From the right hand list, double-click on: IF
Type the following: Logical test : d3 Value if true: c3+d3
Value if false: c3-d3 OK The value 0 appears in E3.
Click onto E3. Place the mouse pointer on the RH bottom corner of E3 until it becomes a black cross. Double click the mouse. The formula is copied down to the bottom of the worksheet.
If you have any problems, hold the mouse button down and drag the cross to the bottom of the column.
All the values in column E are zero, proving that the calculated Total equals BALANCE.
Now delete column E.
Also remove BALANCE. Right click for menu - Wizard - drop BALANCE off the table.
B. 12. GROUP THE PIVOT TABLE MONTH BY MONTH
We'll break down the account balances by month, using the Group command.
Go back to the top of the pivot table. Right click within the pivot table to bring up the menu.
Select: Wizard [or Pivot Table]. The COLUMN-ROW-DATA box re-appears.
Drag and drop DATE into the COLUMN area. Finish.
The pivot table is recalculated. Across the top there is now a large number of dates.
Now double right click on the grey DATE field button in cell C1. The menu appears.
Select: Group & Outline - Group. The âGroupingâ box appears.
[if instead you get a âCannot Group That Selectionâ message, see paragraph below]
Select both Months and Years (at the bottom of the list under Quarters). OK.
The pivot table summarises the balances by month.
[Error message - âCannot Group That Selectionâ You should not get this error message during the tutorial. However, if you ever do, the following notes may be helpfulâ¦â¦â¦The Group command is invaluable, but a bit temperamental. I get more trouble trying to group date fields than all other problems put together. And if it does fail, the only feedback you get is âCannot Group This Selectionâ. There are two likely sources of error. First, your default date format in Windows is set to US month/day/year format, whereas the date fields in your imported data were expressed in UK day/month/year format. To check this, take a closer look at the way your dates are currently formatted in Excel. If they are US, with the month first, you need to reset your Regional Settings in Control Panel, as described in section 1 above, then restart the tutorial over again.
The second possibility is that one or more date fields within your data are either blank or not formatted correctly as dates. First thing to do is to go into the NOMACTIV worksheet, click anywhere on the DATE column BUT NOT THE COLUMN HEADING ITSELF, then click the AZ icon. This sorts the database into date order. Look at the first and the last dates in the worksheet as this is where faulty dates will appear Correct any errors, then Refresh Data. Second, check that the Range is not looking at any blank rows, which will contain blank date fields. Do this via Wizard â Back, which should show you the Range $A$1:$N$518. If it says $A$1:$N$519, then you need to change it to $N$518.
Even when you've corrected all the errors, when you Refresh Data you may find you still get the âCannot Group This Selectionâ message. In this case delete the entire pivot table and build it again from scratch: this time it should work. Finally, sometimes when a Date field is put into a Row area, it will not Group, but if put it in the Column area, it will. Try grouping it first in Column, then go into the wizard and move Years/Date into Row. You will find that the Grouping stays OK.]
B. 13. SHADE COLUMN HEADINGS (Excel 97 & Excel 2000 only)
In Excel 5 and Excel 7 any formatting applied to a pivot table was lost as soon as you recalculated the pivot table or refreshed the data. It was only with the introduction of the âSelectâ option in Excel 97 that formatting would âstickâ when the pivot table was recalculated.
Excel 5 and Excel 7 users should therefore skip to the next section.
Left click on the grey DATE field button in cell D1. All the months are highlighted
Move the mouse pointer above the grey DATE field button in D3. Move it gradually downwards until it turns into a thick vertical black arrow. Then left click the mouse. All the months are highlighted.
[And then for both]
Click on the yellow âFill Colorâ icon in the bottom row of icons, second from right (the one that looks as if it's pouring a can of paint).
Click on the Centre icon. Then, click on the B icon to make them bold.
Click anywhere to remove the highlighting. The months are centred, bold and shaded yellow.
B. 14. DRILLING DOWN TO SEE TRANSACTIONS
The pivot table is divided over 1996 and 1997. We'll look at 1996 first.
Double click on cell F6 - 50,000 for Plant and Machinery.
A new worksheet is created. Cell H2 shows that this is an opening balance entry.
Click back onto the PIVOT worksheet.
Now find 11,665.00 in E17, Debtors Control Account, for November. Double click on that.
Another worksheet is created. Again it's a list of opening balances. The system was started up on 1st January 1997 and these were the outstanding sales invoices at that time.
Click back onto the PIVOT worksheet.
B. 15 CORRECTING FAULTY DATES
Now let's look at 1997. An obvious question arises. Why, after January and February, is there a column for August?
You will find in practice that, whenever you bring over data from an accounts package, there will always be one or two transactions with wrong dates â year 1909, year 2009 etc. Probably these August transactions have been entered with the wrong date.
The quickest way to look at the August transactions is to double click on the August total. Therefore:
Go to cell I149, the Grand total for August. It is 0.00. Double click on the August total.
A new worksheet is created listing the August entries, transaction numbers 161, 162 and 180. 161 and 162 are the two sides of a cash transfer journal. 180 is a deleted Salaries payment.
Go into the NOMACTIV worksheet.
It should be sorted in transaction number (âNOâ) order. (If it isn't, click on D10, then AZ icon.)
Go to Row 359 where you will find transaction no. 161.
The transaction immediately before 161, no. 160, was dated 30th January. The transaction immediately after it, no.162, was dated 2nd February. There's clearly an error here. Suppose that, upon checking the original source document, you found that the correct date was 1st February.
Change the dates in F359 and F360 to: 01/02/1997.
Now go to look at transaction 180 in Row 404. This is a Deleted transaction.
This is puzzling. Sage is supposed to exclude Deleted transactions from the Nominal ledger, but these have been included. I confess I do not understand this.
Anyway, for the purpose of this exercise, change the three dates in F404-6 to: 01/02/1997
In real life you would need to make these changes in Instant Accounting as well. If you didn't, they would still be there the next time you downloaded from Sage. Always aim to correct errors at source.
Now, go back into PIVOT. Go to the top of the Trial Balance.
Right click on the pivot table to bring up the menu. Select: Refresh Data.
The pivot table is recalculated. A message appears: âPivot Table Was Changed During Refresh Data Operationâ. The August 1997 column has disappeared.
B. 16 DELETE EXCESS WORKSHEETS
Notice that new sheets were created when you drilled down on the opening balances and the August total (Sheet2, Sheet3 etc). New worksheets tend to proliferate when you are drilling down on a pivot table. Each one takes up memory. Therefore aim to delete them quickly:
At bottom left, right click onto one of these worksheets, eg Sheet3. The menu appears.
Select: Delete A message appears: The selected sheet(s) will be permanently deleted. OK
Delete all the new worksheets and just leave NOMACTIV and PIVOT.
B. 17 USING THE HIDE COMMAND
In a moment, you are going to take a break. You have brought the data over from Sage, got it to Trial balance stage, and confirmed its accuracy. The first stage of your task is complete.
However, if you later use these instructions to download your own Sage, there are a couple of other things you need to know. The first is the âHideâ command. The Sage sample data we are using just covers a few months. However, if you bring over data from a live Sage system it may contain several years' worth of data. You will need to use the Hide command to suppress data from prior years.
We'll illustrate how to hide months and years. Go into the PIVOT worksheet.
Go to the Debtors Control account (1100) in row 17. The September value is 2,610.00 and the grand total in column I is 45,376.38. You would like to exclude the September figure. To do this:
Double right click on the DATE field button in D1. The menu appears. [Excel 2000 â see below]
Select: Field. In the âHide Itemsâ area at bottom left, highlight Sep and click OK.
[Excel 2000 â click on down arrow in the grey DATE field in D3. Remove the tick from Sep. OK]
September is excluded and the grand total is recalculated as 42,766.38.
Now we'll exclude the entire year of 1996. Double right click on the grey âYearsâ field button in C1.
From the menu select: Field In the Hide items area, highlight 1996. OK.
[Excel 2000 â click on down arrow in the Years cell in C3. Remove the tick from 1996. OK]
1996 disappears entirely and the Debtors Control balance is recalculated to 1,655.38
(Note that, to get P & L balances you will need to hide all months and years prior to the current year. Balance Sheet balances by contrast are the cumulative total of all transactions since the system began).
Now restore Sep and 1996. Right click on DATE â Field - click on Sep to remove highlighting. OK . Do the same with 1996. The months should now read Sep-Oct-Nov-Dec-Jan-Feb
B. 18 ANALYSING DATA BY TRANSACTION TYPE
The other important feature to note is that, by using TYP as the Page field you can get Excel to instantly calculate the monthly nominal totals for a group of transactions such as purchase invoices or petty cash payments. This is very useful for checking that transactions have been correctly analysed.
You should be in the PIVOT worksheet, at the top of the pivot table. Right click for menu.
Select: Wizard Drop TYP into the PAGE area. Finish.
The pivot table stays unchanged, but TYP has appeared in the Page field, cell A1.
Click onto the down arrow in cell B1. A list of transaction types appears.
Select PI (Purchase Invoices)
The pivot table now shows the nominal analysis totals for purchase invoices only. E.g., in January there were purchase invoices to the value of Â£7,191.48, which were analysed to Â£1,071.08 VAT, Â£2,821.00 Materials Purchased, Â£560 Materials Imported etc etc.
Similarly, let's take a look at Petty Cash. In B1, select CP . The Petty Cash totals for January and February are displayed. (These should match the monthly analysis totals in the Petty Cash book).
This analysis by transaction type is very useful at month-end when you want to detect any transactions that have been analysed to the wrong nominal account. For example, if a summary of purchase invoices showed an amount analysed to the Sales nominal account, there's probably something wrong. Drill down on the amount analysed to Sales and Excel will show you the underlying transaction, which you can then correct in Sage via File â Maintenance - Corrections. Transaction types in Sage are:
BP = Bank Payments BR = Bank Receipts
CP = Cash (i.e Petty Cash) Payments) CR = (Petty) Cash Receipts
JC = Journal Debit JD = Journal Debit
PI = Purchase Invoice PP = Purchase Ledger Payment
SI = Sales Invoice SC = Sales Credit
SD = Sales Discount SR = Sales Ledger Receipt
VP = Visa (i.e Credit card) Payment VR = Visa Receipt
At this point, take a break. Save your work:
File â Save As. File of Type: Change to Microsoft Excel workbook. Then relax.
B. 17 WHERE DO I GO FROM HERE?
If you want to follow up on this tutorial, there are three options you can take.
1. TRY THIS OUT ON YOUR OWN VERSION OF SAGE
If you use Sage in your own company, try downloading the data from your own system. Use the âProcedure for Exporting data from Sage to Excelâ in page 3 of these instructions. Find out if your version of Sage has the âNominal Activity (with A/C Ref)â report. If it does, you're in luck because all the data is ready to analyse and, after you've typed in the column headings, you can load the data into a pivot table straight away.
If you are an accountant in practice, do you have any clients who use Sage and are on the Internet? Over the phone, get them to download their data as described on page 3. Then get them to email it to you. Download from your PC into Excel, and bring it to Trial Balance following the procedures laid down in this tutorial.
2. WORK THROUGH PART TWO OF THE SAGE TUTORIAL
Part 2 of the Sage tutorial shows you how to take the raw data from Trial Balance to final reports. It shows you how to enter adjustments, extract a profit and loss account, and produce departmental profitability reports. It will also show you the all-important vlookup command, which enables you to attach new fields to the data for additional analysis. Part 2 should be available on Accountingweb by mid-February.
3. GO ON A TRAINING COURSE
We have been thinking about setting up some training courses for accountants on using pivot tables to analyse Sage. The course would last a day at a local hotel or computer training centre. It would be specific to Sage, working on PC's with sample Sage data much of the time, and reinforce what you learn in tutorials 1 and 2, but also deal with some of the finer points like auditing and error detection. In addition, perhaps users could bring their own export files of Sage along and we could have an afternoon session where people analysed their own company data.
Would you be interested? If you would, or have any suggestions about such a course, please email us at Accountingweb or David Carter at his email address below.
END OF TUTORIAL
DAVID CARTER, Jan 26th 2000.
Based in Hemel Hempstead, Herts, David Carter is an independent consultant who installs accounting and order processing systems into small and medium size businesses. He also writes reviews of small and mid-range accounts packages for the computer press. Since 1996 he has been using Excel pivot tables in areas such as financial and management accounting, budgetting, job costing, sales analysis, order book analysis, and production control. He would be pleased to have any feedback from you on this tutorial and may be contacted at: [email protected].
Date: 25/01/2000 Page: 1
Demonstration Exhibitions Ltd
Period Trial Balance
To Period: Month 12, December 1997
N/C Name Debit Credit
0020 Plant and Machinery 50,000.00
0021 Plant/Machinery Depreciation 4,583.33
0031 Office Equipment Depreciation 355.33
0040 Furniture and Fixtures 16,900.00
0041 Furniture/Fixture Depreciation 117.30
0050 Motor Vehicles 28,360.00
0051 Motor Vehicles Depreciation 1,882.83
1001 Stock 111,491.25
1100 Debtors Control Account 45,376.38
1103 Prepayments 4,625.00
1200 Bank Current Account 9,440.63
1210 Bank Deposit Account 34.37
1230 Petty Cash 16.31
1240 Company Credit Card 1,959.50
1250 Credit Card Receipts 1,230.00
2100 Creditors Control Account 48,988.01
2109 Accruals 1,271.43
2200 Sales Tax Control Account 3,217.84
2201 Purchase Tax Control Account 2,609.18
2300 Loans 84,010.00
2310 Hire Purchase 7,600.00
3000 Ordinary Shares 96,332.00
3201 Drawings 900.00
4000 Sales Type A 22,539.79
4001 Sales Type B 100.00
4009 Discounts Allowed 100.00
4101 Sales Type E 100.00
4903 Insurance Claims 2,500.00
5000 Materials Purchased 8,864.00
5001 Materials Imported 1,560.00
5002 Miscellaneous Purchases 1,939.40
6200 Sales Promotions 1,200.00
6201 Advertising 1,000.00
6202 Gifts and Samples 10.00
7000 Gross Wages 1,000.00
7001 Directors Salaries 2,300.00
7003 Staff Salaries 1,501.26
7103 General Rates 320.00
7200 Electricity 1,071.43
7300 Fuel and Oil 8.51
7304 Miscellaneous Motor Expenses 22.99
7400 Travelling 6.00
7403 U.K. Entertainment 7.50
7406 Subsistence 3.20
7500 Printing 1.28
7501 Postage and Carriage 1.00
7504 Office Stationery 103.25
Date: 25/01/2000 Page: 2
Demonstration Exhibitions Ltd
Period Trial Balance
To Period: Month 12, December 1997
N/C Name Debit Credit
7505 Books etc. 0.64
7600 Legal Fees 200.00
7800 Repairs and Renewals 2.13
7801 Cleaning 3.19
7802 Laundry 4.26
7901 Bank Charges 30.00
8001 Plant/Machinery Depreciation 583.33
8002 Furniture/Fitting Depreciation 24.30
8003 Vehicle Depreciation 840.83
8004 Office Equipment Depreciation 355.33
8204 Insurance 375.00
8205 Refreshments 16.67
Totals: 284,997.99 284,997.99