Mark Gillan
Mark Gillan online - College Lecturer, Author, Digital Artist, Photographer
  • Mark Gillan
  • Books
  • Key Skills
  • Professional
  • Employment
  • Qualifications
  • Contact
  • Photographer
  • Links
  • Advanced Excel

Advanced Excel - sample training course


Welcome and Introduction

Mark Gillan welcomes you to the Advanced Excel course and provides an introduction to the use of this page - please watch video
and so it begins ... enjoy!

Importing Data

Picture
Video and other links
Video authored by Mark Gillan demonstrating and explaining the importing of data into Excel
Video authored by Mark Gillan demonstrating and explaining the use of queries and Microsoft Query to import data
Demo files
stores.txt
File Size: 2 kb
File Type: txt
Download File

customers.docx
File Size: 12 kb
File Type: docx
Download File

syndicate.accdb
File Size: 589 kb
File Type: accdb
Download File

syndicate_withquery.accdb
File Size: 536 kb
File Type: accdb
Download File

Exercise
importing_exercise.pdf
File Size: 382 kb
File Type: pdf
Download File

Exercise files
accountsdept.txt
File Size: 0 kb
File Type: txt
Download File

asiaonly.xlsx
File Size: 9 kb
File Type: xlsx
Download File

employees_table.docx
File Size: 12 kb
File Type: docx
Download File

global_join.accdb
File Size: 520 kb
File Type: accdb
Download File

world.accdb
File Size: 487 kb
File Type: accdb
Download File


Data Validation

Picture
Video and other links
Video tutorial and explanation for Data Validation authored by Mark Gillan
Demo files
overage.xlsx
File Size: 11 kb
File Type: xlsx
Download File

Exercise
exercise_datavalidation.pdf
File Size: 374 kb
File Type: pdf
Download File

Exercise file
deliveries.xlsx
File Size: 9 kb
File Type: xlsx
Download File


Consolidation

Picture
Video and other links
Video by Mark Gillan showing completion of Sales Consolidation Exercise
Demo files
consolidation_choco_1.xlsx
File Size: 10 kb
File Type: xlsx
Download File

consolidation_choco_complete_1.xlsx
File Size: 10 kb
File Type: xlsx
Download File

Exercise and exercise files
sales_consolidation_exercise.xlsx
File Size: 14 kb
File Type: xlsx
Download File

consolidation_1.xlsx
File Size: 11 kb
File Type: xlsx
Download File

consolidation_1_complete.xlsx
File Size: 13 kb
File Type: xlsx
Download File

consolidation_choco_all_exercise.xlsx
File Size: 14 kb
File Type: xlsx
Download File

Exercise completion - answers
sales_consolidation_exercise_complete.xlsx
File Size: 21 kb
File Type: xlsx
Download File

consolidation_choco_allyears_complete.xlsx
File Size: 15 kb
File Type: xlsx
Download File


Pivot Tables and Pivot Charts

Tutorial notes
pivottables.pdf
File Size: 528 kb
File Type: pdf
Download File

Picture
Video and other links
  • Using Pivot Tables
  • Pivot Tables to Pivot Charts
  • Custom grouping in Pivot Tables​
Practice files - try using a pivot table and edit for whatever analysis you would like to perform
bigdata.xlsx
File Size: 33 kb
File Type: xlsx
Download File

Exercise
pivottableexercise.pdf
File Size: 326 kb
File Type: pdf
Download File

Exercise files
pivotcars_1.xls
File Size: 19 kb
File Type: xls
Download File

teamsales_1.xlsx
File Size: 12 kb
File Type: xlsx
Download File


Auditing, trace precedents and dependents

Tutorial notes and explanation
auditingtools_notes.pdf
File Size: 899 kb
File Type: pdf
Download File

Demo files
auditing_bdm_1.xls
File Size: 30 kb
File Type: xls
Download File

Exercise
exercise_auditing.pdf
File Size: 370 kb
File Type: pdf
Download File

Exercise files
auditing_purchase.xlsx
File Size: 44 kb
File Type: xlsx
Download File

auditing_studentevidence.docx
File Size: 44 kb
File Type: docx
Download File


Macros

Tutorial notes and explanation
macros_explained_mg.pdf
File Size: 131 kb
File Type: pdf
Download File

Picture
Video and other links
Authored by Mark Gillan, video demonstrates use of macros, editing a macro, running a macro and other information
Demo files
inventory_demo.xlsx
File Size: 28 kb
File Type: xlsx
Download File

Exercise
macro_exercise.pdf
File Size: 203 kb
File Type: pdf
Download File

Exercise files
marketingbudgetplan_a_.xlsx
File Size: 20 kb
File Type: xlsx
Download File

marketingbudgetplan_a_withmacro.xlsm
File Size: 27 kb
File Type: xlsm
Download File

marketingbudgetplan_b_withmacro.xlsm
File Size: 26 kb
File Type: xlsm
Download File

marketingbudgetplan_b_withmacroran.xlsm
File Size: 27 kb
File Type: xlsm
Download File


Protection and collaboration

Tutorial notes and explanation
protection_notes.pdf
File Size: 1012 kb
File Type: pdf
Download File

Picture
Video and other links
Video explaining sharing process and tracking changes to Excel Workbooks - note: SkyDrive is now OneDrive
Demo files
protection_demo.xlsx
File Size: 23 kb
File Type: xlsx
Download File

Exercise
protection_exercise.pdf
File Size: 188 kb
File Type: pdf
Download File

Exercise files
loancalculator.xlsx
File Size: 12 kb
File Type: xlsx
Download File


Picture

Reading List and resources


Microsoft Excel book
​Excel Exercises book by Mark Gillan via Amazon ISBN: 978-0955777073
Also available elsewhere, including Excel Exercises book by Mark Gillan via Waterstones

Picture

Other books by Mark Gillan


Microsoft Word book
Microsoft Word Exercises book by Mark Gillan via Amazon ISBN: 978-0955777028
Also available elsewhere, including Microsoft Word book by Mark Gillan via Waterstones

Web Design book
Qualified Teacher presents Web Design book by Mark Gillan via Amazon ISBN: 978-0955777059
Also available through other book retailers, including Qualified Teacher presents Web Design by Mark Gillan

Key Skills

Photography
Digital Art
Author

Mark Gillan

About Me
Professional
Links

Contact

Contact Mark Gillan
Mark Gillan Home Page

© COPYRIGHT MARK GILLAN 2023
ALL RIGHTS RESERVED