Pivot table with Payroll data – This includes instructions to create a pivot table which is not necessary each
month, see page 20 to just replace the next month’s data, update the data source and refresh
Select all the data that was exported
Piv1
Pivot Table Instructions – By Cindy Johnson
Click on the insert tab, then click on Pivot
Table
You see that shows the data range that was
selected and now just click on OK
Piv2
Pivot Table Instructions – By Cindy Johnson
This side is where the
data will appear
This side is where you
select fields to drag
down to the areas
below
Piv3
Pivot Table Instructions – By Cindy Johnson
If this shows up as
Count‐ just click on it
and edit value field
Pull down fields (any settings, also change
ones you want) this is number format to
easy to customize (this currency if you want
is a drag and drop
feature)
Piv4
Pivot Table Instructions – By Cindy Johnson
Change Report Layout
to Show in Tabular
Change Design features – Form, also click on
turn off Subtotals – turn Repeat All Item Labels
off Grand Totals for rows
and Columns
Piv5
Pivot Table Instructions – By Cindy Johnson
Your pivot table will
now look like this, you
can rearrange as need
for various things. Now
we need to exclude
Health Insurance from
the Benefit Expense
Piv6
Pivot Table Instructions – By Cindy Johnson
Add ACCT_CODE to the
Report Filter
PAY_SEQ is helpful to
get rid of the pesky cost
Also, do this with transfers from your
PAY_ID, all the payroll payroll or include them
Then, click on the drop data is in here SP, BW, or exclude as needed
down arrow, check MO….. I select MO for
select multiple items, this purpose
then uncheck 61403
Piv7
Pivot Table Instructions – By Cindy Johnson
Now we are only
showing the actual
payroll run, and Health
insurance is not
included in the fringe
(Benefit Expense)
Piv8
Pivot Table Instructions – By Cindy Johnson
Copy and paste this on another sheet
Divide the Benefit expense by
the earnings (column
D/column E)
Then you can scroll through
and look for our people‐ this
brings in other people
because of the Leave Pool
If you want to whittle it
down, there is a way to do
that with info griz‐ which is
shown on the next few pages
Remember you don’t need to
recreate this pivot table each
time,
SEE PAGE 20 for instructions
on how to just paste your
data from each month into
the pivot table to change and
refresh the data source
Piv9
Pivot Table Instructions – By Cindy Johnson
InfoGriz Report‐ using V‐Lookup to narrow down the data
Piv10
Pivot Table Instructions – By Cindy Johnson
Piv11
Pivot Table Instructions – By Cindy Johnson
View Report, Export to Excel
Piv12
Pivot Table Instructions – By Cindy Johnson
Copy and paste into pivot table on a separate sheet in your Pivot Table spreadsheet.
Now do a VLookup to look at only our people – remember when you have this in from the previous month you
don’t need to do much but copy and paste – once in a while you will want to recreate so I am showing it.
Piv13
Pivot Table Instructions – By Cindy Johnson
Highlight column A on
InfoGriz sheet and
convert to number
Piv14
Pivot Table Instructions – By Cindy Johnson
Go back to your sheet
that has the fringe
calculations and move
the 790 number column
to column a and
highlight the column
and convert to number
Piv15
Pivot Table Instructions – By Cindy Johnson
Go to the sheet with
the InfoGriz Data –
highlight it all, click on
the formulas tab
Piv16
Pivot Table Instructions – By Cindy Johnson
Click on Define name
and name the selection
– this is also a semi‐
permanent way to
prepare easy v‐lookups
via names for the range
where to look
Piv17
Pivot Table Instructions – By Cindy Johnson
Create a vLookup to
eliminate non‐Forestry
employees, copy and
paste down to the
bottom. Piv18
Pivot Table Instructions – By Cindy Johnson
Do some sorting and
now you are only
looking at our people,
use conditional
formatting to highlight
the fringe rate over
25% and then take a
look at each one to
make sure it is the way
it is supposed to be.
When there is leave,
you need to manually
add for verification
Piv19
Pivot Table Instructions – By Cindy Johnson
REPLACE DATA WITH NEW DATA IN PIVOT TABLE
Click on the page with
all of the data and
select all, click delete
Then go back to the
beginning and export
the date for the new
month
Piv20
Pivot Table Instructions – By Cindy Johnson
Paste the new data
into the sheet where
you deleted the data
Piv21
Pivot Table Instructions – By Cindy Johnson
Go to the Pivot Table, click on
Change Data Source and re‐
select all the data that you just
pasted, then click on refresh
Piv22
Pivot Table Instructions – By Cindy Johnson
Pivot Table –SLICERS
Insert Slicers by clicking on
Pivot Table and Analyze tab,
then insert Slicer, check the
boxes of what you want, and
click ok
Piv23
Pivot Table Instructions – By Cindy Johnson
Now you have all of the people
for MFRRO5 that are on 61123
Piv24
Pivot Table Instructions – By Cindy Johnson
