Learn Excel Blog

Full Slate of Excel Bloggers at PASS Business Analytics Conference

               

There is an amazing line-up of speakers at this year’s PASS Business Analytics Conference. You might read blogs by some or all of these speakers: Bob Phillips, Ken Puls, Jordan Goldmeier, Matt Allington, Rob Collie, Chandoo, Avi Singh, Rick Grantham, Oz du Soleil, Chris Webb, Jen Stirrup, Jen Underwood, Kasper de Jonge, Igor Peev. I am really excited to get to meet spreadsheet pioneer Dan Fylstra. Dan was president of Visicorp, the company with the first killer app: Visicalc. You will also get to hear from the true Mister Excel – Microsoft’s lead project manager for Excel: David Gainer.

Join Bill Jelen and other business and data analytics professionals at the PASS Business Analytics Conference held in Santa Clara, April 20-22, 2015. Learn how to leverage data to drive decisions, uncover insights and increase productivity to remain competitive in today’s marketplace.

Session Highlights

Set yourself apart with world-class analytics training on Data Visualization, Predictive Analytics, R, Big Data, Excel, Power BI, and more. Select from 60+ sessions including:

 

  • MrExcel’s Most Productive Analysis Techinques
  • Advanced Modelling and Calculation Using the Power BI Designer
  • Beyond What-If: How to Build Analytic and Decision Models in Spreadsheets
  • Data Visualization, Storytelling and the Brain
  • An Overview of Predictive Analytics for Practitioners
  • Unlocking Big Data: The Power of Cognitive Computing

Folks from Microsoft like Marco Russo and Marc Reguera, Finance Director, will also be leading sessions and you’ll have the opportunity to meet them.

Why Attend?

  • Take your skills to the next level. Learn the tools and technologies required by today’s business and data analytics pros.
  • Learn from the best. Get first-hand info from top-rated analytics speakers from leading companies.
  • Stay ahead of the curve. Make smart data-driven decisions by understanding new trends and directions.
  • Build connections. Immerse yourself in a dynamic community who shares the same business challenges.
  • Be inspired! From keynotes, panel discussions, and sessions to mealtime conversations, the PASS BA Conference is all about meeting people, sharing issues, and gaining new ideas and that will make you a better analytics professional.

Discount Code

Register today using discount code BASPBIL for $150 savings.

Help fund and be a part of my 40th book: MrExcel XL

               
Do you remember:
  • Led Zeppelin II
  • Chicago III
  • Rocky IV
  • Star Wars V
I am about to write my 40th book.
  • MrExcel XL
(yep, “40” in Roman is “XL”)
MrExcelXLIGG2
In a crazy bid to build buzz, I am pre-selling the book and everyone who buys gets their name in the book. Funds are used to pay for upgrading the book to color and hiring illustrators. Plus… the investors will get to vote on which Excel tips are the 40 greatest of all time…this alone will be generating bar fights across the land.

Free Fresh Excel Content For Your Blog or Website

               

There is a great discussion at the MrExcel forum about using Excel forums to increase your knowledge of Excel. Board member TinaP summarized it this way: “Books got me started with Excel knowledge, but the forums educated me.”.

Most people who use Excel 40 hours a week don’t realize which powerful tools they do not know. Nothing makes you aware of how much you don’t know about Excel than reading a few forum threads every day. Reading someone else’s question and then the collaboration of the community in solving the problem will enlighten you about many different ways to solve the problem.

When I check Google Analytics for the forum, I find that too many people come in to the site for the first time, read one thread, and then bounce out. This could mean they got the answer to their particular problem and left happy. But it does capture them to stop back every day and read a few more threads to learn what they don’t know.

One feature that I added to the MrExcel home page is an RSS box in the right column that shows a different five hot topics from the MrExcel board.

hottopicssample

This content is refreshed every day. I choose topics that include a mix of VBA and non-VBA questions.

Anyone who is looking for fresh content for their blog or website is welcome to re-use the RSS feed or the script that displays the RSS feed in a 160-pixel wide box.

The RSS feed is http://feeds.feedburner.com/MrexcelExcelForumTop10Posts

To embed that feed in a box on your blog or website, use this script:

<script src="http://output77.rssinclude.com/output?type=js&id=823310&hash=64428596f8e326ae631fbf9fe201e474" type="text/javascript">

Why Haven’t You Adopted Power Pivot Yet?

               

It is late 2014, almost 4 years after PowerPivot reached general availability. This product is a game-changer. It enables people to do dramatically more powerful analyses than regular pivot tables. Yet, when I walk into a room full of 100 people who opted to come to a Power Excel with MrExcel seminar, there are only 2 or 3 hands that go up when I ask who has tried Power Pivot. These are people who use Excel 40 hours a week (and those are the weeks they are on vacation…), and only 2 or 3 percent of them have tried Power Pivot, Power View, Power Query, Power Map.

Let’s travel back in time to 1989. I was using Lotus 1-2-3 and was really adept at data analysis in spreadsheets. Pivot tables did not exist yet. Instead, you would do an Advanced Filter, another Advanced Filter followed by transpose, write an @DSUM function in the corner, then /Data Table 2. It took fifteen minutes to create what pivot tables could do in six clicks. At that point, Lotus 1-2-3 had 90%+ market share. There were other spreadsheets: Quattro, Excel, As Easy As, but they were also-rans. Lotus was the king.

Over at Lotus, Pito Salas and his team had invented a new way of thinking about spreadsheets. They created a way to create pivot table reports. But Lotus opted not to put this product in the next release of Lotus 1-2-3. It was too powerful. They could not just give this away. It was sold in a separate product called Lotus Improv. You had to go spend an extra $100 to get the pivot table functionality. My manager bought one for himself… it gave him super powers. But he did not have the budget to buy a copy for me. I went out at lunch and plunked down my own $100 so I could do pivot tables in six clicks instead of 15 minutes.

Across the country, in Redmond, the Excel team were trying to gain market share against Lotus and getting nowhere fast. Excel was so much slower than Lotus. It took two more releases of Excel, but eventually, Microsoft included the now-rebranded pivot tables, for free, in Excel 93. This was the beginning of the end for Lotus. Find any old accountant. Ask if he used to use Lotus. He will nod. Ask if he can remember when he switched. The odds are that he switched between 1994 and 1997.

Am I saying pivot tables killed Lotus 1-2-3? Not completely. Excel had VBA. Subtotals. AutoFilter. And computers finally caught up to have enough processing power to enable the GUI interface of Excel not feel sluggish compared to Lotus 1-2-3. But pivot tables were a factor. It is not that Lotus did not have pivot tables. They practically invented pivot tables (or were the first to borrow the concept from Javelin), but they decided not to put them in the box with Lotus 1-2-3.

If the move from @DSUM to Pivot Tables were like moving from a horse to a car, the move from Pivot Tables to Power Pivot + Power Query + Power Map + Power View is like moving from a car to a jet.

Next week, when I walk into my Power Excel seminar in Miami, I will again ask how many people have used Power Pivot, and it will be the same 2-3%. We are about to see the fourth generation of Power Pivot come out next year and we are still only at 2-3% adoption. Why aren’t 30% of the data analysts out there using Power Pivot yet?

Here are my theories:

  1. It is nearly impossible to buy. You can not walk into a Microsoft Store and buy Power Pivot. It is not in any box. Well… it is in the stand-alone boxed version of Excel 2013 sold at Amazon, but that box is not sold at the Microsoft Store. And, that box excludes other goodies like editing for Office on the iPad.
  2. Office 365 plans are too confusing. There are flavors of Office 365 Home. There are flavors of Office 365 Business. There is Office 365 University. None of the Home editions include Power Pivot. It is a travesty that our future analysts are being encouraged to buy the dumbed-down Office 365 University. Of the Business Plans, the $12 a month Pro Plus plan includes it, but the $12.50 a month Business Premium plan does not include it.
  3. Power Map is free for all with any Excel 2013. But Power Query and Power View require you to have one of the hard-to-buy versions of Excel 2013.
  4. The DAX formula language is hard to master. You really need to study Rob Collie’s DAX Formulas book to unleash some of the potential of Power Pivot. You can see huge analysis improvements without DAX, but with DAX you can create new statistics that are simply impossible in regular pivot tables.

If I were in charge of merchandising the new flagship Microsoft Store in Manhattan, I would simplify this down to two boxes:

  • The Real Office 365: Include everything in the box. Excel. Power Pivot. Power Query. Power View. Power Map. Power Q&A. Power BI sites. Heck, you could even throw in freebies like Word, Power Point and One Note.
  • Not Really Office: (also known as Office For Your Grandmother). This box could be an amalgam of every hampered SKU they are selling at the Microsoft Store now.

As Microsoft marketing starts planning for Windows 10 and Office vNext to come out in 2015, they need to simplify the product offerings. Every copy of Office should have the ability to use Power Pivot, Power Map, Power Query, Power View. These are the next-generation tools that will make people never think of Google Docs or Quip or other spreadsheet upstarts.

Make Font Red If It Meets a Condition – Without Conditional Formatting

               

Before there was conditional formatting, there was custom number formatting. You can still add a condition to your custom number formats.

QuickTipCondFormat

  1. Select the range of cells. Press Ctrl+1 to open the Format Cells dialog.
  2. Select the Number tab. Choose Custom from the bottom of the list.
  3. In the Type box, enter a format such as [Red][>=90];[Blue][>=60]0;0

You can only specify two conditions, so a total of three colors, counting the final ;0 which will show the assigned font color for the cell.

You can only use certain color names… think back to the 3-bit color days: Blue, Black, Yellow, Teal, Red, White. But the little-known secret is you can use any of Excel 2003’s colors with [Color1] through [Color56]. For those of you who never memorized them, here they are:

ColorIndex

 

Learn Excel from MrExcel