Mastering Excel: Named Ranges, OFFSET and Dynamic Charts

What are they? how can they help in Excel?Then we cover the OFFSET function. This lesson focuses on many seemingly unrelated topics but then combines them in unexpected ways to create something greater than the sum of its parts. There are no macros, no programming involved; just a creative way of combining existing Excel functionality.

This lesson covers a lot of ground. It is truly one of the most powerful, flexible and mostly unused functions in Excel. It is unused because it does take some getting used to, OFFSET doesn't 'work' like the rest of the Excel functions. Follow the link in the book and you can receive the workbook to work on alongside the text.

The sample workbook has a bonus extra dynamic chart not covered in the book. Excel users will be able to click the worksheet and see the charts animate in real time. However, we will go through this function in detail here. Then we move on to using Forms in Excel. First we review the concept of Named Ranges. Forms give users familiar ways to interact with an Excel worksheet, much like a form on the web.

And finally, offset, we combine Named Ranges, Forms and Charts to create animated charts. To help everyone understand and follow along with the exercises, I have made available a sample workbook.

Mastering Excel: Interactive Charts

Users can change column charts to bar charts, pie charts to 3D pie charts, etc. For new students, I don't leave you high and dry after you buy my lesson. You will learn to build one dashboard that can morph into an entirely different dashboard. As with all my lessons, there are follow along workbooks that you can use to work through the exercises at your own pace.

This lesson continues to build upon the "Mastering Excel: User Forms" lesson. The user forms lesson covered the various form objects and how to use them. You will learn how to set up charts so users can interact with them via option buttons, spin buttons, scroll bars, etc. The last exercise shows you how to build a dashboard where users will be able to change the entire dashboard as it suits them.

This lesson in the Mastering Excel series covers charts, specifically interactive charts. The focus in this lesson is on combining user forms and charts. You can always contact me with any questions you have.

Mastering Excel Formulas IF

The new section explains the new formulas but you can follow along without the 2016 version. You do not need Excel 2016 for this lesson. This lesson, like all other lessons in the series, comes with a follow along workbook where you can practice what you read. Updated versionthis lesson has been updated to cover two new Excel 2016 formulas: SWITCH and IFS.

Become an excel guru by understanding and then dominating the IF formula. This formula intimidates novice and intermediate Excel users but with a step by step approach and hands on examples, you can easily master this monster. Additionally, the lesson then goes on to cover two other complementary formulas: AND, OR that can be used with IF to handle complex logic.

This lesson is written in a step by step, easy to understand manner.

Mastering Excel Formula Tips and Tricks

Think through how to do a calculation once, make sure it is correct, then copy it to other cells and use it as many times as you need to. In this lesson, how to use them, you will learn some of the most popular and few not so popular formulas, you will review their syntax, and you will also learn some other non-formula tricks that will make your life much easier.

Let the computer do the hard work!a few of the topics covered in this lesson are: absolute and relative cell references, search, offset, concatenate, custom number formats, count, edit directly in cell, month, counta, if, watch window, proper, averageif, countif, indirect, precision as displayed, small, trim, substitute, today, vlookup, cell, max, year, mode, iferror, sumif, roundas with all the mastering excel lessons, MEDIAN, Evaluate Formula, NETWORKDAYS, AVERAGEIFS, SUTBTOTAL, Formula Auditing, DAY, AVERAGE, MIN, Text to Columns, Protecting and Hiding FormulasFormulas: LEFT, RIGHT, Function Wizard, DATEDIF, Function Screentips, Summing Across Worksheets, Scroll Lock/F2 Trick, NOW, SUMIFS, there is a follow along workbook available for you to work through as you read the text.

Excel is all about formulas.

Mastering Excel Formulas VLOOKUP

The accompanying workbook has additional worksheets for learning this function. Update: i have added a new section that shows you how to perform a lookup on several columns and return the corresponding text value. Use vlookup to build sales reports, commission reports, financial models, fantasy football models, recipe database, you can use it for just about anything.

There is a section on using the INDEX function that can also be used to lookup values. How does it work? why should you use it? When should you use it? It is all revealed inside!Become an Excel guru one easy step at a time. In this lesson, you will learn how to use the VLOOKUP formula. Explained in plain English, you will learn how to use this powerful formula.

It is more flexible than VLOOKUP. Very cools stuff! .

Mastering Excel: Pivot Tables

Who’s the highest earner? Which product sold the most in the last seven days. The lessons are geared towards the average Excel user that just wants to improve their skill set without having to suffer through hours of technical, confusing instruction. Included in the book is a link to download a free workbook so you can follow along with the exercises at your own pace.

Pivot tables are THE indispensable skill you need to know to master Excel. You have 100, product, 000 rows of data by invoice, salesperson and you are getting bombarded with questions. After this lesson, you’ll be able to answer those questions in seconds. This lesson is laser focused on this incredibly power tool.

I walk you through all the steps to create a pivot table, how to create calculations in Pivot Tables, how to work with pivot charts, using Excel tables effectively with Pivot Tables, and much more. As always, i explain all the steps in plain English. They are the workhorses of Excel.

Excel Importing & Exporting Text Data: Quickly Turn Raw Data Into Excel Tables Data Analysis With Excel

This book shows how to import and export different types of data from Excel. This book walks through how to get each type of data into Excel, how to get that data out of Excel in a way usable to other programs, and this book includes some tips if things are not working correctly. The focus is on working with common data types that you are most likely to see, raw ASCII tables, PDFs, including CSV format, or even images.

.

Become a VLOOKUP KnowItAll: Mastering the Key Microsoft Excel Function for Data Analysis

There are books and blogs that cover general information on this topic, but Become a VLOOKUP KnowItAll contains detailed explanations on why and how the nuts and bolts of Microsoft Excel VLOOKUP works. In the business world, Excel Worksheets are a standard document used for reporting and analysis. There are screenshots and a free downloadable companion file with examples along the way.

In this book you will:learn the purpose of each vlookup argument - lookup value, column index, table array, and rangeExplore multiple creative ways to pull information into these argumentsUnderstand error messages, troubleshoot them, and learn how to prevent themExperiment with advanced techniques to pull data together using VLOOKUPAnswer review questions at the end of each chapter Be a professional spreadsheet guru.

Crack the code and get to the next level in Excel Become a Microsoft VLOOKUP Function Spreadsheet virtuoso!If you want to master Excel then begin with formulas. Understand the details of the file and learn how to avoid errors. Seriously. Don’t waste your money on VLOOKUP books for that. That said, the intended audience is for anyone looking for detailed explanations on what this function can do.

The point is, you can accomplish some amazing feats in Microsoft Excel with VLOOKUP! When you harness the power of VLOOKUP, you will find that manipulating data will become significantly easier to do. Explore the depths of the VLOOKUP function to see what goes into each part.

Excel Conditional Formatting: Tips You Can Use Immediately To Make Your Data Stand Out Data Analysis With Excel Book 3

You can use it to spot trends in data, make outliers visible, or make intuitive tables. Conditional formatting allows you do to all the formats that you would want to do if you could manually go through and examine all your data, but that you typically don’t have time for. This book walks you through how to set up different types of conditional formatting in Excel, including applying it to a full table of numbers, or just targeting specific cells.

Conditional formatting is very useful for many different functions.

IF Statements Book 4 - Mastering Excel Macros

It gives your program the ability to 'think' and perform actions based on one or several conditions. The if statement is the basic unit of logic in Excel programming. In this lesson we cover how to write IF statements in VBA. Along with that topic, we review how to use the OFFSET function programmatically and the ActiveCell.

As with all the other lessons, there is a sample workbook you can get from my website to follow along with the exercises.

Mastering Excel: Data Tables

Data tables are a way to analyze different scenarios in Excel. However, you can change many variables simultaneously and see the effect instantly. As always, when you buy my lessons, I am here to answer any question you may have. There is a link in the ebook where you can download follow-along workbooks so you can read and practice to learn the tasks.

My lessons are written with regular users in mind. This lesson covers data tables. Data tables let you answer questions like: 'what's the effect of increasing price by $1 increments' and 'how can I create a gird that shows the interaction between a price changes and raw goods changes?'I'll cover all that and a bit more in a step-by-step fashion with tons of images.

No technical jargon.