This book is for anyone who wants to be taken on a journey to learn Excel; from basic through to complex Excel functions. It doesn’t cover all functions within Excel, but gives you what I consider to be the most practical functions and how to use them. This will give you a great set of tools which you will be able to apply in many different situations, limited only by your imagination!
The book is split into 4 sections:
Covering SUM, COUNT/COUNTA, IF, AND/OR and AVG/MAX/MIN.
The essentials to get you started with basic data analysis. We learn about using functions in your Excel formulae, a useful Excel feature which is designed to make your life easier. It will give you the starting blocks that you need in order to present and make sense of a spreadsheet in a quick, easy and confidence-boosting way.
Covering VLOOKUP/HLOOKUP, LEN, LEFT/RIGHT/MID, SEARCH/FIND, First Name and Last Name formulae, CONCATENATE, LOWER/PROPER/UPPER, TRIM, CLEAN/SUBSTITUTE/CHAR and IFERROR.
Now we start digging a bit deeper. Beginning with some criteria-based analysis and how to pull out data. The second half of this section looks at removing elements which may impede analysis or affect the quality of any data analysis results. We need to ensure the most effective prep to allow the most reliable and meaningful analysis. For example; have you ever created a formula that you know was correct but it didn’t work properly for some reason, this has happened to me so many times. Often it’s because the original data was inconsistent. I’ve got some handy functions for you to help with getting rid of unwanted characters, spaces and inconsistencies – as I always like to say ‘don’t get errors, get answers!’
Which talks about absolute references and covers COUNTIF/SUMIF, INDEX/MATCH, COUNTIFS/SUMIFS and how to do a two-way lookup.
Data analysis is the process of developing answers from data and it’s in this section where we can really manipulate the data in order to start understanding the messages contained within. This exploration might require additional data cleaning or requests for data, but that's ok; the process of collecting, cleansing, analysing, interpreting and presenting data is often an iterative process. To be successful in carrying out this process it does not necessarily mean providing the right answer first time, but being able to understand and give insight as to why that answer is what it is. A fluid process which allows you to revisit the data again and again is the key.
So how do we do this? Well Excel will facilitate this analysis, but it won't do it for you. You need to decide what you want to get from the data and I'll give you some tools in this book to query data within Excel and summarise it into top level information in order to present it like a pro and give your career a boost.
Which really starts to show you the power of Excel. We cover OFFSET, OFFSET validation, INDIRECT, ROW, COLUMN, ADDRESS, INDIRECT/ADDRESS, SUM/INDIRECT/ROW.
A word of warning with this section, it will open up a world of possibilities and really expand how you think about Excel. However please remember, simple is usually better – so if you can do something in a more straight-forward way, and that might mean restructuring how your spreadsheet works or using other Excel functionality, then I would do that. Failing that option – you can probably use a bespoke formula and I will start to show you how!
Is this ebook right for you? Will it give you the Excel help you need? Take a glimpse inside the first few pages - if you like what you see, then go ahead and buy!
The book is split into 4 sections:
1 - Basic Excel Formulae
Covering SUM, COUNT/COUNTA, IF, AND/OR and AVG/MAX/MIN.
The essentials to get you started with basic data analysis. We learn about using functions in your Excel formulae, a useful Excel feature which is designed to make your life easier. It will give you the starting blocks that you need in order to present and make sense of a spreadsheet in a quick, easy and confidence-boosting way.
2 - Intermediate Excel Formulae
Covering VLOOKUP/HLOOKUP, LEN, LEFT/RIGHT/MID, SEARCH/FIND, First Name and Last Name formulae, CONCATENATE, LOWER/PROPER/UPPER, TRIM, CLEAN/SUBSTITUTE/CHAR and IFERROR.
Now we start digging a bit deeper. Beginning with some criteria-based analysis and how to pull out data. The second half of this section looks at removing elements which may impede analysis or affect the quality of any data analysis results. We need to ensure the most effective prep to allow the most reliable and meaningful analysis. For example; have you ever created a formula that you know was correct but it didn’t work properly for some reason, this has happened to me so many times. Often it’s because the original data was inconsistent. I’ve got some handy functions for you to help with getting rid of unwanted characters, spaces and inconsistencies – as I always like to say ‘don’t get errors, get answers!’
3 - Advanced Excel Formulae
Which talks about absolute references and covers COUNTIF/SUMIF, INDEX/MATCH, COUNTIFS/SUMIFS and how to do a two-way lookup.
Data analysis is the process of developing answers from data and it’s in this section where we can really manipulate the data in order to start understanding the messages contained within. This exploration might require additional data cleaning or requests for data, but that's ok; the process of collecting, cleansing, analysing, interpreting and presenting data is often an iterative process. To be successful in carrying out this process it does not necessarily mean providing the right answer first time, but being able to understand and give insight as to why that answer is what it is. A fluid process which allows you to revisit the data again and again is the key.
So how do we do this? Well Excel will facilitate this analysis, but it won't do it for you. You need to decide what you want to get from the data and I'll give you some tools in this book to query data within Excel and summarise it into top level information in order to present it like a pro and give your career a boost.
4 - Dynamic Excel Formulae
Which really starts to show you the power of Excel. We cover OFFSET, OFFSET validation, INDIRECT, ROW, COLUMN, ADDRESS, INDIRECT/ADDRESS, SUM/INDIRECT/ROW.
A word of warning with this section, it will open up a world of possibilities and really expand how you think about Excel. However please remember, simple is usually better – so if you can do something in a more straight-forward way, and that might mean restructuring how your spreadsheet works or using other Excel functionality, then I would do that. Failing that option – you can probably use a bespoke formula and I will start to show you how!
Is this ebook right for you? Will it give you the Excel help you need? Take a glimpse inside the first few pages - if you like what you see, then go ahead and buy!
