[Free Ebook]Excel Training Manual for Financial Professionals by Anton Du Plessis - KING OF EXCEL

# [Free Ebook]Excel Training Manual for Financial Professionals by Anton Du Plessis

Author:Anton Du Plessis [Du Plessis, Anton]
Language: eng
Format: azw3
Publisher: EVBA.INFO
Published: 2019-01-15T05:00:00+00:00
Exercise: PivotTables
Open file: PivotTables.xlsx
Steps
Select any cell within the data on the Sales worksheet Click on [Insert][Tables][PivotTable]
Click OK on the Create PivotTable dialogue box Select the applicable fields as follows:
Your results should be similar to:
Select the Value Field Settings… of the Amount Excl VAT field
Change the name to Sales and the number format to Accounting with no symbol Center align the Sales heading
Sort the Sales data from highest to lowest values
Apply any PivotTable style to your PivotTable
Clickon[PivotTable Tools Options][Calculations][Fields, Items & Sets][Calculated Field] Name: Cost Price
Formula: ='Amount Excl VAT'/125*100 (Remember to double click on Amount Excl VAT to use it, don't retype it)
Format the heading the same as for Sales (Field name Cost Price, remember to add a space as this name already exist)
Clickon[PivotTable Tools Options][Fields, Items & Sets][Calculated Field] Name: Gross Profit
Formula: ='Amount Excl VAT' -'Cost Price' (Remember to double click on Amount Excl VAT and Cost Price to use it, don't retype it)
Format the heading the same as for Sales
Double click an amount in the Head Office line, you should get a new worksheet with only the Head Office data
#evba #etipfree #eama #kingexcel