excel pro brochure.pdf
TRANSCRIPT
-
8/14/2019 Excel Pro brochure.pdf
1/14
1
The
Excel Pro
Brochure
The
Excel Pro
Brochure
-
8/14/2019 Excel Pro brochure.pdf
2/14
2
INTRODUCTION
Excel
Enthusiasts (www.ashishmathur.com) is a website dedicated to Ashishs work in
Microsoft (MS) Excel. Commissioned in July 2006, this website is dedicated toanswering
questions of users who mail their Excel queries to Ashish Mathur.
Ashish has written articles for Microsoft Office Online and the PC Quest magazine.
Additionally, Ashish has also conducted Excel webcasts.
The hallmark of a good professional/would be professional is possession of an optimal
blend of technical and soft skills. Good software skills can, to a very great extent, assist
in speedier execution of projects. Additionally, individuals with a good blend of these
skills tend to be more confident in their work.
With this basic tenet, Excel Enthusiasts would like to introduce itsSelfdesigned and
conceptualised session (Excel Pro) on MS Excel which is geared towardsprofessionals who are regular users of this application
http://www.ashishmathur.com/http://www.ashishmathur.com/knowledge-base/http://www.ashishmathur.com/knowledge-base/http://www.ashishmathur.com/knowledge-base/http://www.ashishmathur.com/ -
8/14/2019 Excel Pro brochure.pdf
3/14
3
Ashish Mathur, Founder Excel
Enthusiasts has been a Finance
professional who has worked in the
Business Advisory Group of PulsarKnowledge Centre (PKC) (a Consultancy
firm based in Gurgaon, Haryana, India)
from March 2004 to August 2008. In
addition to the other M&A and advisory
assignments which Ashish has worked on,
one of the prominent assignments in
which he was involved was the sale of
Wataniya Telecom (a Mobile Telecom
operator with operations in six countries
across the MENA region) to Qtel in March2007 for US$3.7 billion. In April 2007,
Ashish was awarded the KIPCO Group
Excellence award. He was the only one
of the 12,000 employees across the KIPCO
Group of Companies to receive this award.
Prior to working in PKC, Ashish worked
with KPMG India (in the Audit and
Assurance practise) for three years. In
2001, Ashish was felicitated, by KPMG,with the title ofExceptional Performance
for the year.
Ashish is an avid spreadsheet user
To share his knowledge with other Excel
users, Ashish manages his own website
He also written articles for magazines (PC
Quest) andMicrosoft Office Online
Microsoft has been conferring the yearly
Most Valuable Professional (MVP) title to
Ashish since March 2003. In India,
Ashish is the first MS Excel MVP
Ashishs profile can also be viewed on the
Microsoft website and on LinkedIn
Globally, there are less than 200 MS ExcelMVPs
He interacts with the Excel development
team at Microsoft and provides
feedback/suggestions to them on current
and future versions of Excel
Since April 2003, Ashish has been invited
by Microsoft to Redmond, Washington,
(U.S.A.) (Microsofts headquarters) to
attend a four day Global MVP Summit. In
April 2007 and March 2008 he, along with
other MVPs, spent two days with the
Microsoft Excel development team
discussing product development plans of
Excel
He was also abeta testerof Office 2007
and other MS products/services
In 2004, Ashish Post Graduated with an
MBA Degree from Management
Development Institute (MDI), Gurgaon.
Ashish completed his Graduation andSchooling from Shri Ram College of
Commerce, New Delhi and Welham
BoysSchool, Dehradun respectively.
Ashish Mathur
Founder - Excel Enthusiasts
www.ashishmathur.com
(91) 98110 41988
LinkedIn profile
Follow me on Twitter
Blog
PROFILE
http://www.pulsarkc.com/http://www.pulsarkc.com/http://www.pulsarkc.com/http://www.pulsarkc.com/http://video.msn.com/video.aspx?vid=99b41573-2bb0-4074-8bc0-da1d4aa52448http://www.ashishmathur.com/https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Excel&page=3http://www.linkedin.com/profile?viewProfile=&key=1869201mailto:[email protected]://www.ashishmathur.com/http://www.linkedin.com/in/excelenthusiastshttp://twitter.com/http://www.ashishmathur.com/knowledge-base/http://www.ashishmathur.com/knowledge-base/http://twitter.com/http://www.linkedin.com/in/excelenthusiastshttp://www.ashishmathur.com/mailto:[email protected]://www.linkedin.com/profile?viewProfile=&key=1869201https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Excel&page=3http://www.ashishmathur.com/http://video.msn.com/video.aspx?vid=99b41573-2bb0-4074-8bc0-da1d4aa52448http://www.pulsarkc.com/ -
8/14/2019 Excel Pro brochure.pdf
4/14
4
OBJECTIVE OF THESESSION
Discuss solutions to MS Excel problems which end users face in their day to day
working;
The emphasis of the session would be on discussing the most commonly used
functions/formulas in a fair bit of detail such that seasoned as well as
unseasoned Excel users can derive maximum value;
The focus would be on the application rather than theoretical use of functions
and formulas.
Since the objective of this session is to solve practical problems, I prefer not to
categorise this as a Basic/Intermediate/Advanced level session. As mentioned
later on, this session is suited for all regular MS Excel users.
We will discuss solutions beyond Excels Help menu.
-
8/14/2019 Excel Pro brochure.pdf
5/14
5
Unanswered
questions can be
routed to other MS
Excel Consultants
The
Excel Enthusiasts
Edge
Discuss functions
and formulas
which are well
researched
Discuss
unknown tips
and tricks
Adopt a problem solving, participative
style approach
Discuss solutions to real life MS Excel
problems
Focus on the application of formulas and
functions
THE EXCELENTHUSIASTS EDGE
Simple to
understand,
layman solutions
approach
Suitable for
professionals
working across
industries/
functional
specialisations
-
8/14/2019 Excel Pro brochure.pdf
6/14
6
VALUE ADDITION
- Better return on investment Learn to use MS Excel better and thereby get
maximum return on your software investment
- Effective time management Learn efficient ways of solving Excel related problems
and thereby reduce time spent on project/task execution
- Gather the confidence to deal with a cross section of Excel related problems
- Improve the existing Microsoft Excel skill set
The agenda as outlined in the next two slides has been decided/is regularly updated
with reference to:
- Questions which Ashish has received (through his website) from end users;
- Questions raised during Excel sessions conducted so far
- Questions discussed in various online Excel forums
-
8/14/2019 Excel Pro brochure.pdf
7/147
Basics of referencing (absolute, relative and mixed), IF()/nested IF(), AND(), OR() functions Lookup techniques and nuances such as:
Making the column index in a VLOOKUP() function dynamic; Auto expanding the table array in a VLOOKUP() formula when new rows get added; Looking up data within upper and lower limits;
Performing a look up operation where the look value is not the first column of the table array; Looking up instances where lookup values appear in multiple columns; Lookup last value (by position) in a horizontal/vertical range
Looking up data in large datasets while satisfying one/multiple conditions with and nuances such as: Overcoming Excels (2003 and prior versions) limitations of 7 nested IFs; Filtering databases on multiple AND/OR conditions in/across worksheets/workbooks;
Extracting unique values from a range
Extracting repeated values based on conditions Nuances of auto filtering such as: Filtering data without manually selecting the data point in the auto filter drop down; Running more than one auto filter on one worksheet;
Dealing with date related problems such as: Converting non dates to dates;
Displaying non dates as dates; Retaining date/number formatting when concatenating with data in cells
Working with data validation and nuances such as:
Creating dependent validation lists Validating across worksheets
Basics of graphing and nuances such as Dealing with dates in the x-axis; Auto aligning data labels in a pie chart where data labels are lengthy; Customising data labels on charts; Presenting data in Bar of Pie and Doughnut charts; Getting total labels in stacked column charts/hiding zero data labels in stacked column charts Showing breaks in line charts for missing data points
Colouring columns/bars based on their values
AGENDA(DAY1)
-
8/14/2019 Excel Pro brochure.pdf
8/148
Basics of SUMIF() and COUNTIF() functions and nuances such as: Dealing with one and multiple conditions Summing values in a range which has error values (without sorting/filtering/deleting);
Summing values (based on one/multiple conditions) from another workbook even whetherthe other workbook is closed
Count unique values Performing YTD/MTD calculations with year/month as user input
Performing a scenario analysis (or What-if analysis/Sensitivity analysis) on key performanceindicators by changing: Once variable; Two variables; Multiple variables; and
Impact of a change in key assumptions under various scenarios across multiple years Impact of y-o-y change in key assumptions under various scenarios across multiple years
Basics of pivot tables and nuances such as: Performing calculations and addressing issues when writing formulas in pivot tables
Creating a pivot table from multiple worksheets in same workbook (without copying andpasting) where rows are well in excess of 300,000;
Creating a pivot table from multiple worksheets of multiple workbooks (without copying andpasting);
Resolving problems when grouping numeric data/dates in a pivot; Retain pivot table formatting after pasting a pivot tables as Paste Special > Values; and
Specifying different grouping criteria for different Pivot Tables created on the same database
Database approach to solving MS Excel problems (where using native MS Excel functions wouldcause the workbook to become very slow) Counting unique values where number of records are very large with and without conditions; Summing based on conditions across workbooks where the number of records are very large.
Consolidating date from multiple worksheets/workbooks that do not have the same structure Discussion on various Excel short cuts and tips & tricks;
AGENDA(DAY2)
-
8/14/2019 Excel Pro brochure.pdf
9/149
We will take a cursory glance (with some example) over VBA* but will not discuss it
in depth. Rationale for the same is:
Before learning programming in Excel i.e. writing macros and coding in VBA, it is
imperative to understand the capabilities of the inbuilt functions and formulas of
Excel. This session is designed to give you a fair level of expertise in solving
problems using Excels inbuilt functions and formulas
VBA is not something which participants (especially non developers) can
understand/facilitators can cover in 2 days. To understand and gain significant
working knowledge in writing macros and coding in VBA, a fair amount of time
has to be invested by participants.
There are ample online resources available which share free VBA code for
solving common problems in Excel. For business/end users, it is more important
to know where these sources are than learn programming
Mathematical, Statistical, Financial functions
ASPECTSNOTCOVERED IN THESESSION
VBA is the programming language used to customise/automate Excel
-
8/14/2019 Excel Pro brochure.pdf
10/1410
After the session, i encourage all participants to contact me for seeking assistance
on solving their practical MS Excel problems via the MS Excel forums ofwww.merawindows.com.
POSTSESSIONINTERACTION
-
8/14/2019 Excel Pro brochure.pdf
11/1411
WHOSHOULD ATTEND/TEAMSIZE
The target audience of this session are regular Excel users users who are aware of
basic features and functionalities such as
Linking cells in and across worksheets;
Writing a basic IF() function;
Writing basic AND()/OR() functions;
Preparing a simple pivot table;
Creating a simple bar, line, column and pie chart
The maximum number of participants should be 20.
-
8/14/2019 Excel Pro brochure.pdf
12/1412
OTHER ASPECTS
Venue and Duration
The entire session (as outlined underAgenda) would be covered in two days
The session could be conducted at any venue which suits your organisation
Infrastructure Requirements
One projector and white screen display
A flip chart/white board and marker
One laptop/desktop per participant
MS Excel 2007/2010/2013
Adequate power points to operate desktops/charge laptops
Each laptop/desktop should have the DVD/CD drive enabled
One writing pad and pen/pencil per participant
-
8/14/2019 Excel Pro brochure.pdf
13/1413
REQUEST FORPROPOSAL
Ashish Mathur
http://www.ashishmathur.com
(91) 98110 41988
If you are interested in organising the Excel Pro session in your organisation,
please contact Ashish Mathur
If you choose to write an e-mail, then please do so from your official e-mail address
clearly mentioning your official address and contact details (mobile, landline and
extension number)
LinkedIn profile
Microsoft profile
Follow me on TwitterBlog
mailto:[email protected]://ashishmathur.com/contactus.aspxhttps://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Excel&page=3http://twitter.com/http://www.ashishmathur.com/knowledge-base/http://www.ashishmathur.com/knowledge-base/http://twitter.com/https://mvp.support.microsoft.com/communities/mvp.aspx?product=1&competency=Excel&page=3http://www.linkedin.com/profile?viewProfile=&key=1869201&locale=en_US&trk=tab_prohttp://ashishmathur.com/contactus.aspxmailto:[email protected] -
8/14/2019 Excel Pro brochure.pdf
14/1414
Thank YouPlease check http://www.ashishmathur.com/corporate-interventions/
for further updates to this brochure and other products/services
Version: February 12, 2013
http://www.ashishmathur.com/corporate-interventions/http://www.ashishmathur.com/corporate-interventions/