For business users of Microsoft Excel Free guides and templates
Home >  Training > 

New Training

How to Achieve Excel Productivity 

Here's how to skyrocket your Excel output, slash your Excel errors, and continually improve your Excel reports and analyses. 


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

If you want to become MUCH more productive with Excel, that is, if you want to...

  • skyrocket your Excel output,
  • slash your Excel errors, and,
  • continually improve your Excel reports and analyses...

...then this is the most important message you’ll ever read.

But first, a warning…

Before we go further, let me make a few things clear:

First, this is not an “instant-expert” training program that promises you vast Excel knowledge while you never actually do anything with Excel. When you take this training, plan to invest some time with it.

Second, although you can do virtually everything I teach in Excel 2010 and 2013, I wrote this training using Office 365’s version of Excel 2016. So if you’re still using a version of Excel prior to 2010, please upgrade Office before you take this training.

Third, when you start the training, you’ll need at least lower-intermediate Excel skills. But the less you know about Excel, the more time you’ll need to invest, and the more questions you'll probably need to ask.

With that understood, let's see...

How to Be Much More Productive in Excel

The Excel-Friendly Database StrategyThe Excel-Friendly Database Strategy, shown here, marks your pathway to productivity in Excel

To move from low Excel productivity to high Excel productivity, you...

...Stow your data in an Excel-Friendly Database (EFD), which gives your worksheet formulas the ability to return data from it.

...Flow your data to Excel templates, using formulas. This step offers two benefits. First, using templates reduces Excel errors.

Second, using templates allows you to create new reports and analyses with your work nearly half done before you even get started.

...Show your data in reports and analyses using formulas linked to your EFD.

Excel reports and analyses that truly are productive, contain no data. Instead, they contain formulas linked to your Excel-friendly data, along with some text and settings. This lets your reports update automatically when new data is added to your EFD.

...Grow your reports and analyses by continually improving them.

Continual improvement finally is possible with Excel because the Excel-Friendly Database Strategy gives you a repeatable process...an actual system that can be improved.

Productivity training teaches you how to implement and continually improve the EFD strategy.

Here’s What You’re Getting…

Your Productivity training comes in three parts:

1.  Your Productivity lessons,

2.  Your Excel Productivity Mastermind membership,

3.  The Productivity workbooks,

And some other goodies.

My goal is to make you one of the most-productive Excel users in the world, and I’ll give you the details in a minute. But this goal is about far more than just your personal productivity. It’s about what your improved productivity can bring to your employer, your career, and your personal life.

Your Employer

If you’re like most Excel users in business, you’re a Subject Matter Expert (SME). Because of your professional training and your time on the job, you know a lot about your industry and your company. You know where to find useful data; you know what your data actually means; and you probably have ideas about ways to extract even more meaning from your data.

Think about it...

You and the other Excel users in your company are far better-qualified than anyone else to discover new problems and opportunities in your data, and to give your managers the business intelligence they need. And you could do that if you quickly could do the boring and repetitive Excel work—the Excel scutwork—that you can’t escape, and then invest your time in the fun and important Excel work that your company really needs for you to do.

Your company’s managers need your time, your knowledge, and your curiosity...and that’s what your improved productivity will allow you to give them.

Your Career

Suppose you were twice as productive with Excel as you are now. That would give you the time to talk with your managers...to better understand their information needs, brainstorm solutions, offer them options, improve your reports, and give them the insight they need to improve company performance.

The knowledge you would gain from working more closely with your managers—and the record of accomplishment you would create—certainly should help your career.

And also, in the article, "Research: The More Essential Your Job Is to Your Company, the Happier You’ll Be," the Harvard Business Review suggests another reason to improve your Excel productivity. The closer you can become to being a "lynchpin" in your company—that is, crucial to your company's mission—the happier you'll tend to be in your job.

Your Personal Life

I occasionally think back to the days when I used spreadsheets as an employee, and as an onsite consultant. In my memory, I see lonely office buildings at night, completely dark, except for the area around my desk and the safety lights in the stairwells.

Back then, spreadsheet tools and techniques weren’t available to bring the high level of productivity we can achieve today. That’s why I spent so much time at work. And that’s why I didn’t get married until I was 36.

Your personal life is important. And your greater Excel productivity will give you more time to enjoy it.

1. Your Productivity Lessons

You’ll receive Excel productivity lessons every month. Here's just a small part of what you'll learn about in your subscription:

  • Excel Tables
  • Power Query
  • VBA
  • Three types of lookup methods
  • Pivot Tables
  • Analytical techniques
  • Excel analytical dashboards
  • Date & Text Functions
  • Conditional formatting
  • Sparklines and Data Bars
  • Beginning through advanced charting
  • Visualization techniques
  • Using formulas in Tables
  • Pivot Charts and dashboards
  • SUMIFS, SUMPRODUCT, & Array formulas
  • Interactive reporting and analysis
  • Static and dynamic range names
  • Consolidating budgets and forecasts in Excel
  • Connecting Excel to Web-sourced data
  • Linked Pictures (formerly: the Camera Tool)

And they'll all be built on a productive foundation! That is, when you use these tools and techniques, you'll still be able to update your reports and analyses in seconds; you'll still be able to slash your errors; and you'll still be able to continually improve your Excel work.

This training is solutions-based. That is, you'll learn what you need to learn...so you can do what you need to do in Excel. By taking this approach...

  • You won't get overwhelmed by trying to learn everything about these features.
  • You'll remember what you learn, because you'll be using it on the job.
  • You'll be immediately productive, and then become more productive over time.

Here’s what your first four lessons will cover...

Lesson 1: Productivity Foundation

Productive Excel reportThis lesson—which is more than twice the length of a normal lesson—introduces the full productivity strategy, from start to finish. It provides the foundation for all that follows.

We begin with a CSV file. And then you’ll learn how to create the simple interactive report shown here.

You can update this report merely by using  updated CSVs and by changing the report date from March to April to May, and so on. That is, it adjusts to your data automatically, with one click. And with another click, you can update its fiscal year. You also can switch from region to region.

In addition to being an interactive report, you could print it, or save it as a PDF file…without showing the black-and-gray frame that surrounds the report.

The lesson also shows you how to slash your errors...in three ways.

Automatic alerting system in an Excel reportFirst, it shows you how to set up the automatic error-alerting system, which uses text and conditional formatting, shown here, to alert you to errors. This system is effective only when you use an EFD.

Second, by virtually automating your reports (without using macros) you protect your reports from new errors that we all make occasionally when we manually update existing reports.

And third, the lesson describes how to use templates and formula-based automation to ratchet down the errors in all your reports.

By the end of Lesson 1, you’ll have the complete foundation ready for the rest of your Productivity training.

Lesson 2: Dashboard Productivity

mini-dash-reportLesson 2 builds on the foundation you learn in Lesson 1. In this lesson, you’ll create this interactive Excel dashboard report of analytical data.

The key to Excel dashboard reporting is to get your plumbing set up correctly, plumbing that uses formulas to flow your data from your EFD to the charts and small tables in your dashboards.

You’ll learn not merely how to create iterative charts of your source data, you’ll also learn how to chart any transformations of the data that you can perform in Excel.

The charts in this lesson are simple, but business-quality. Future lessons will teach you how to create world-class charts in Excel…charts that look extremely professional and that your managers can read quickly and understand easily.

Lesson 3: Formula Productivity

Productivity training will turn you into an expert in productive Excel reporting and analysis. It does that by concentrating on productive methods while ignoring areas of Excel that typically aren’t directly relevant to improving your productivity.

For example, Lesson 3 teaches you the dozen or so worksheet formulas that you’ll use frequently for your reporting and analysis…while ignoring more than 400 other Excel functions.

The key formula in Lesson 3 will be the SUMIFS function, which generally is considered to be an upper-intermediate or an advanced worksheet function...but which is easy to learn.

However, you'll need more calculation methods than merely using the "S" functions. That's why this lesson also includes array formulas and their related functions.

Even if you’re an advanced Excel user, I’m sure you’ll learn something new in this lesson. For example, you’ll learn how to use the SUMIFS function to return not just numbers, but text from Excel Tables.

Lesson 4: Power Query and FRED

Power Query first was introduced as an add-in for Excel 2010 and 2013. In Excel 2016, it’s in your Ribbon’s Data, Get & Transform Data group...but most people still call it Power Query.

Power Query (PQ) is a very powerful tool for, well, getting and transforming data…which it loads into a Table or a Data Model in Excel. But it’s also a challenging program to learn. One reason it’s challenging is that Excel has more PQ functions than worksheet functions. Another is that it uses a programming language, M, which is nothing like VBA.

Several Excel MVPs teach outstanding PQ courses. To do so, they immerse you in the topic. One of them does so for three long days.

But I take a different approach in Productivity. Rather than submerging you in PQ, I'll cut it up into bite-sized chunks, each of which is designed to solve a problem.

In Lesson 4, I’ll introduce you to two topics.

First, I’ll introduce you to the Federal Reserve Economic Database (FRED). This source of free economic data has more than 500,000 data series for the US and many other countries. For your managers to understand their revenues, costs, markets, industry performance, and so on, they often should do so in the context of actual data about the world in which your company operates. And FRED can provide a huge amount of that data…easily.

Second, I’ll introduce you to Power Query. Specifically, I’ll teach you how to use PQ to import text files from the FRED database into an Excel Table. To do so, I’ll reveal a secret URL where you can find a text file for each of FRED’s 500,000 series.

By learning Power Query with FRED data, you'll learn powerful PQ techniques using real data, not artificial data. And because FRED data looks much like your own data, you'll be able to use similar methods to import much of your own internal data. That's why I'll also teach you how to use PQ to import a text file that's on your own computer.

The Training Process

All Productivity lessons will be delivered in PDF files, not videos. I know, everyone creates video training these days, but I’m not doing that. Here’s why…

  • The Productivity Foundation, Lesson 1You can’t browse video. This training is designed for lower-intermediate users and above. So if you’re an advanced Excel user, you quickly can browse PDF content that you already understand, and slow down when you find content that’s new to you.

  • You can’t study video. What if you run into content that you don’t understand? With a PDF, you can re-read the few paragraphs that preceded it. You can look at nearby displays. And you quickly can review similar ideas from other pages.

  • You can’t add notes to video. Adobe Acrobat Reader, which is free, allows you to add your own notes and highlights to PDF pages. You also can print your PDF and mark up your paper. 

  • You can’t search video. With Windows File Explorer, you can search your Productivity workbooks and PDF files for the words and phrases you need, and then quickly see a list of the files that contain those search terms. That is, you can use your Productivity files as reference material.

In short, I want you to actually learn this stuff, and then use my training files for reference in the future. And that’s why I don’t use video.

And to get answers to your questions, your subscription includes...

2. The Excel Productivity Mastermind Membership

I’ve worked for years to develop the contents of this training. Even so, the Mastermind could turn out to be even more valuable than my training.

Over the long run, your new ability to continually improve your Excel work is the most-important feature of my productivity method. And I’ve designed the Mastermind to put you on the right track for continual improvement…from the moment you start your training.

As a Productivity member, you’ll also be a member of the Excel Productivity Mastermind. This private Facebook group is the only international community of business professionals set up specifically to improve the productivity of Excel users in business.

Its mission is to accelerate the growth of its members’ companies and careers, while leaving time for a personal life. Its members will do so by continually improving both their Excel productivity and the Excel work they produce.

This Mastermind is where members unite online to ask questions, share knowledge, brainstorm ideas, suggest solutions, and develop a worldwide network of productive peers in business.

The group will start a revolution in Excel productivity. And that’s what you’ll be joining.

3. The Productivity Workbooks

You’ll have access to workbooks from three sources.

First, each lesson explains the strategy and tactics for building workbooks that will make your Excel work more productive. Therefore, the Productivity training will include all the workbooks used in each lesson. Of course, these workbooks include the workbook examples I’ve shown you in the images above.

Some of the workbooks will be Excel templates that you can save and use to create your own reports. Others will be examples used in the training.

Still others will be bonus workbooks that I develop over time and include for download from your members’ area.

Second, as a Mastermind member, you’ll be able to upload your own (nonproprietary) workbooks for other members to view, and download the workbooks they share. This allows members to contribute knowledge, ideas, problems, and discoveries to your Mastermind community.

But it gets better, because you’re also getting…

Bonus 1: My Five-Minute Dashboard

To understand this unique document, imagine this scenario…

Go from this... empty-worksheet

...to this...  quick dashboard

...in about 5 minutes, with this: The 5- Minute Dashboard

You’re visiting a potential employer, and near the end of a successful interview, a manager asks about your Excel skills.

You could tell her about your Excel training, experience, and certifications—just like every other job candidate does—or you could take a different approach…

“Rather than telling you about my Excel skills,” you could say, “why don’t I show you…using real data?

“I can create a quick-and-dirty analytical dashboard in a few minutes. All I need is a web browser and a recent version of Excel.

“Working alone, I can do it in about five minutes. But because, I’ll explain what I’m doing, it’ll take five or ten minutes longer.”

The 5-Minute Dashboard takes about 8 pages to explain the following steps…

1. For your data, download the monthly Consumer Price Index from the Federal Reserve Economic Database (FRED)…public data that looks much like your own CSV files.

2. Set up the data as an EFD in an Excel Table.

3. Flow the data to a staging Table that the charts will reference.

4. Enhance the original data by adding a Rate of Change (ROC) calculation to the Table.

5. Use the INDEX, MATCH, and TEXT functions to flow data from the EFD to the staging Table.

6.  Set up the report-date cell for the current report.

7.  Format the report sheet so it looks like a dashboard report.

8.  Create the two charts linked to the data and controlled by the report date.

9.  And then, add the charts to your quick-and-dirty dashboard report, shown above.

And finally, this unique document suggests ways you could improve your dashboard report, if you had more time than only five minutes to spend on it.

To illustrate, you could…

  • Add a validation list to the date cell, so that you could choose a report date using a dropdown list box. (This is covered in Lesson 1.)

  • Add an error-finding worksheet which would drive error-alerts, which control conditional formatting, which turns your report red when there’s an error. (Lesson 1.)

  • Add more controls to the dashboard so you could choose among several measures, or several different analytical ways to look at your data. (Lesson 1.)

  • Use Power Query so you could update your data each period by pressing Ctrl + Alt + F5. (Lesson 4)

  • Clean up the back end of your dashboard report so that everything is nicely organized, and so that your dashboard is easier to understand, debug, and expand in the future. (Lesson 2.)

  • Add more figures to your Dashboard, which is easy once the back end is organized. (Lesson 2.)

  • Set up range names, which adds power, helps to document your formulas, and makes formula-entry quicker and easier. In part, this is because range names allow you to use IntelliSense and the Paste Names dialog. (Lesson 1.)

  • Set up dynamic range names, so you could use interactive controls to change the number of periods to display. (Lesson 7.)

  • Put performance into context by adding shaded areas to mark recessions or slowdowns in your charts. (Lesson 5.)

  • Save an improved version of the dashboard workbook as a template so that you could create an analytical dashboard with different data in about 30 seconds. (Lesson 1.)

  • Create your dashboard using Pivot Tables and Slicers. (Lesson 6.)

  • And a lot more.

And you're also getting...

Bonus 2: All My Other New Products…Free

In coming months, I’ll create new products, Productivity versions of past products, and additional examples that use the same methods that you’ll be learning. And as a Productivity member, you’ll get them all for free...in the lessons in which they best fit.

To illustrate a longer-term product, this is the alpha version of a dashboard I’ve been working on…one that I’ll send you somewhere around Lesson 10.

When I’m done with this interactive dashboard, it will allow you to compare the financial performance of one of thousands of public companies with that of your own company’s financial performance or with that of another public company.

analytical dashboardThe controls at the top will give you the power to display any two companies, measures, transformations, and time periods that any chart figure displays.

Said another way, those same ten list boxes would allow you to control any number of chart figures you might add to this dashboard.

This example compares the actual performance of Microsoft (the blue lines) with Apple (the gray lines.)

To illustrate, Figure 1 shows actual revenues over ten years. Here, you can see that Apple’s revenues jumped significantly and became very seasonal soon after June, 2007, which is when Apple introduced the iPhone.  

And Figure 5 shows the ratio of Microsoft’s total spending for R&D to that of Apple. Ten years ago, as you can see, Microsoft spent ten times more than Apple in R&D. But today, the two companies spend about the same.

One of the changes I need to make to this display is to add recession indicators to each chart. That information can help to explain the huge swings you see in Apple’s rates of change in several measures, as shown in Figures 2 and 4.

Also, this chart doesn't allow you to display your own company's performance yet.

If you’re a lower-intermediate Excel user, don’t let this dashboard intimidate you. When I finally send you this dashboard, you’ll know how all the elements work, where the data comes from, and how to update the Excel-Friendly Database automatically when you open the workbook.

And because so many different techniques will be used in this dashboard, I might even dedicate a lesson to it.

That’s a lot of Excel strategies, training, resources, and support…all designed to make you significantly more productive in Excel than you are today.

Here’s What to Do Next

Subscriptions to Productivity training, mastermind, workbooks, and free versions of all my new products is $279 per month. That works out to be the cost of about 20 minutes per working day for an average Excel user.

As soon as you place your order, you’ll get an automated receipt, with a link that generates a second email. That email will have your link to the ExcelUser Portal, along with your user name and password. In the portal, you’ll be able to download your first lesson and any of the free downloads I’ve added to the Portal. Then, monthly, each new lesson will be added to your portal.

And within two days, you’ll receive another email that welcomes you to the Excel Productivity Mastermind. (There's a delay because Facebook doesn’t offer a way to add new members to the Mastermind automatically, so my assistant must do that manually.)Subscribe now

To get started click on this button, and get productive NOW.

Natural Questions

I can almost hear the perfectly natural questions that might be occurring to you at this point. Let’s see if I can answer some of them…

How Can I Get My Company to Pay for Excel Productivity?

By subscribing to Productivity—and then learning what the lessons teach—you'll become much more valuable to your company. To illustrate this value, I've set up a zip file that contains a one-page PDF, Evaluate the ROI of Excel Productivity. You can download it at the link.

In the PDF, I write that, "On average, Excel Productivity will cost your company the equivalent of about 20 minutes per working day for your Excel users who subscribe to the training."

Therefore, the zip file also includes a workbook with a small sheet that shows my sources of data and the calculations I used to arrive at the 20-minute number. (It's actually 18.6 minutes, but I rounded it up to 20.)

(Look at the SUMPRODUCT formula in the worksheet's calculation of Weighted-Average Salary. Do you know how it works? Do you know why I used an undocumented syntax for the function? You will after you read Lesson 3.)

This Is a Lot to Learn. Can You Actually Do This?

Years ago, when I taught a room full of accountants about Excel dashboards, a young accountant came up to me during a break and said, “I’ll never learn it all.”

“No one ever learns it all,” I said. “Instead we become experts in the area of Excel that’s most important to our career and that we’re the most interested in."

With that question in mind, I’ve worked to bring elegant simplicity to your training. But if you do get into trouble, that’s one of the reasons the Mastermind exists. Just ask the questions you need to ask.

Lesson 1 will take more time than normal, because it’s more than twice the content of a normal lesson. It also has the Five-Minute Dashboard, of course.

But after Lesson 1, I’ll limit each lesson to 10 to 15 pages, and each lesson will build on the previous lessons.

So yes, you definitely can learn how to be productive in Excel! I've got your back.

Will This Work for You?

Several times a week, Excel users send me questions about Excel problems that this training would NOT solve. These users tend to fall into two categories.

First, some users are lost in massive, complex models, and they’re asking for help that no one can provide. (The Productivity strategy can provide current data for your models, but after that, you’re on your own!)

Second, most of the others are using Excel as a special system of some kind—an accounting system, a payroll system, a system to track the performance of sports leagues…and so on. If the Productivity strategy can help with these systems, I’m not sure how.

Instead, I designed the Productivity strategy to solve the three kinds of problems I’ve seen in most companies over several decades:

1. Excel reporting and analysis of business data takes WAY too much time and effort to generate.

2. Excel work contains WAY too many errors.

3. No matter how hard you work, or how many advanced-training courses you take, there seems to be no practical way to reduce these problems…other than to replace Excel.

If these three problems seem familiar, then this training will work for you.

Is This Better Than Some Other Option?

The answer depends on where you are and where you want to go.

If you’re not satisfied with your current level of Excel productivity, there’s no other training program like this. Our Productivity training gives you the only strategy to achieve general Excel productivity in a business setting.

However, if you have a solid productivity framework in place, you often can add additional training to enhance some aspect of your productive Excel system. For example, if you need higher levels of expertise to create complex queries to bring certain data into your Excel Tables, then I can recommend other companies that can offer such training.

Or, if you want to scale your Excel-Friendly Database beyond Excel Tables, I can recommend Excel-Friendly Databases that can contain petabytes of data...one billion times more data than a Table can contain!

Also, as a Productivity member, you'll find that the Mastermind will be a great source for recommendations like this.

Time Is of the Essence

Here’s why…

The price of Excel Productivity will go up significantly for new subscribers...for two reasons.

First, your price is roughly equivalent to the full cost of 20 minutes per work day for the average Excel user. And it will bring significantly more savings than that.

Second, the cost of Masterminds alone tends to range from $4,000 to $100,000 per year, therefore, I plan to raise prices above today’s price of $279.

And also, if you subscribe now, I’ve decided to add one more bonus...

Bonus 3: All of Charley's Swipe Files

Swipe File 3 Swipe file 2
Swipe File 1 Swipe File 4
You’ll also be getting my entire collection of the 76 Charley’s Swipe Files, a $1,097.20 value.

Each workbook contains a professionally designed chart like the ones shown here. These workbooks will show you how to do things with Excel charts that you probably didn't know is possible.

(You'll see that many of them use meaningless text that's known as "greeking"—from "it's Greek to me." It probably should have been called "latining," however, because greeking actually uses meaningless Latin.)

When I created these charts, I usually copied their designs from respected business magazines and newspapers like The Economist, The Wall Street Journal, Forbes, Business Week, and The Harvard Business Review.

Subscribe nowThis is a very limited offer. So register now before the clock below hits zero, the offer expires, and the price doubles.

Thanks for taking the time to read this letter. I look forward to hearing from you soon.

Charley

P.S. In case you’re one of those people, like me, who just skips to the end of the message, here’s the deal…

I’m offering you my Excel Productivity subscription for $279 per month. The subscription includes monthly PDF and workbook files that teach you the only strategy known that can skyrocket your Excel output and slash your errors…with continual improvement of your Excel reporting and analytical system.

You also get a membership in the exclusive Excel Productivity Mastermind, where members unite online to ask questions, share their knowledge, brainstorm solutions, suggest ideas, and develop a worldwide network of productive peers in business.

You'll also get tons of workbook examples from the training and from additional templates and examples I add in the future.

You also get The Five-Minute Dashboard, which shows you how to start with an empty workbook and create an interactive dashboard report of real data in about five minutes.

You'll also get all my future Productivity products.

You also get my complete collection of Charley’s Swipe Files, a $1,097.20 value. These 76 workbooks contain cutting-edge Excel chart designs adapted from the pages of many business magazines and newspapers, including The Economist and The Wall Street Journal.

Subscribe nowThat's a huge bargain for only $279 per month...the averge daily cost of an Excel user working for a company.

Register now to start your subscription. You won’t regret it.