Since I pretty much live in Excel, I get excited by new features that I find useful, and disheartened by oversimplification (read, stupidification) of tools and analysis; it's a very personal thing for me because I work so closely with it. Computers are very smart, and in a lot of ways smarter than humans, but there are some things that humans can see and do that computers are just not developed enough yet to get; it is for that reason that analysts like myself are empowered by Excel's tools, while people less skilled at Excel and data analysis cannot replicate our work by simply trying to make Excel "go". I will really only be able to evaluate Office 365 after it is forced upon me at work and have been working in it for a while, and really, a better evaluation could result from getting used to Office 365 and then having to go back to Office 2007. I recently made a similar move when I changed companies; I've gotten so good and comfortable in Excel 2007 and my new company currently has Excel 2003, so I'm having to re-learn how to do things in that old format (the menus like File, Edit, Data, and Insert were replaced with ribbons in 2007). Not only am I having to find where the functions are again, I have a feeling of being handicapped because I try to use the features of 2007 and then realize I can't (filter by color, filter by multiple values with checkboxes), so then I have to find workarounds.
The good news is that I just discovered my company would be moving forward with Office 365 in the new year, and that means, at a minimum, I should (I hope) regain the functions I have become used to in Excel 2007. But upon hearing the news, I decided to take a peek into what we can expect in the coming year that goes beyond what I'm familiar with in Excel 2007. So, without further ado, based on what I've seen and read about Excel 2013, here's my analysis.
The Good
The most immediately useful and intriguing function I noticed is called Flash Fill. The way Microsoft explains it, it is "like a data assistant that finishes your work for you." They use vague, colorful terminology to describe it, but what I see it as is like an automatic VLOOKUP, with a twist that the lookup table is inherent in the data and does not exist elsewhere. That is impressive and exciting for a few reasons: (1) people who suck at or forget how to use VLOOKUP will get immediate benefit, (2) people who are good at VLOOKUP will save time, (3) files can be smaller and less complex by not requiring lookup tables to exist in the files, and (4) where files would have referenced tables on a specific user's computer, those files are less likely to get corrupted or have non-evaluating formulas because the lookup tables are not needed. It is a bit scary that, from a programmer's perspective, we're no longer in control of or prescribing exactly what the values should be, but exciting if it actually works intelligently. This does not mean that VLOOKUP is dead, it will definitely have a very important role, but perhaps needed a bit less with the advent of Flash Fill. Only time will tell how helpful this feature really is.
Animation in charts is probably more of a gimmicky thing than anything else, but I still like it a little. It's very Apple-kind-of-sexy-feeling, and may help real analytics in a small subset of scenarios of which it is used.
The idea of using multiple tables and relationships may be a good one, but my guess is that most users won't get that deep. This sounds a little like they're directly attacking Access and integrating its database operations into Excel. This is going to make Excel more of a monstrosity than it already is, and perhaps push Access towards obsolescence. Personally, I like the separation of the tools. Excel is very good at x, y and z, and Access is good when you need more than that. By blurring the lines, we are looking at Excel becoming much more complex and difficult to follow, bigger less manageable files, and much more technical skills required to be an Excel guru. And I could just see scenarios all over the place of where you suddenly find that you do actually need Access, and it will probably require a whole restart on relationships and logic and will cause people to miss deadlines. Still, I'll reserve my judgment and suffice it to say that this could be a nice feature in a pinch, and used on a small scale, will be a helpful time-saver in lieu of a quick VLOOKUP here or there.
Thankfully, Microsoft heard our cry on number format. In Excel 2007, the ribbon contained a quick way to format numbers as Accounting, but to get Currency you had to go the long way. This annoyed the frick out of me, and I'm glad to see they have wisened up.
Another good addition is better Conditional Formatting and "Sparklines". In Excel 2007, Conditional Formatting was cumbersome at best, and wrong at worst. Assuming this enhanced Conditional Formatting works, it will be a strong analytic tool as well as a nice visualization. 
The Bad
A new file format? Again? I haven't encountered anyone saying that they wish their Excel files would save in an ISO8601 date format, yet Microsoft has introduced a new file format to do just that. If I had been advising Microsoft, I would have told them to steer clear of new file formats, and instead offer, as they rightfully did, a function to convert the dates.
Microsoft is using their updated save feature to push their cloud storage solutions. I'm sure they are obligated by marketing to do so, but it's in poor taste in my opinion. They are being a little Apple-licious and closed-minded. Most companies already have shared drives or SharePoints and will default to continuing to use those. It'll be one of those features that is always in the way and in your face but highly ignored.
I have a few nested formulas that I use on a regular basis, and it would be great if Microsoft created single functions that did what my nested functions do. So I had to take a peek at what new functions were coming out, and I was wholeheartedly disappointed. Lots more niche math, trig, statistics and engineering functions have been added, which I feel are just going to convolute the function world. We need more business stuff, and I'm not talking financial, I'm talking useful. Nobody in business cares about the hyperbolic cosecant of a complex number. I get spell check errors just writing the description down! There are a few nuggets of goodness (with caveats) that I will credit them with:
- IFNA function - this does replace my nested formula IF(ISNA(... and takes it to a new level of efficiency and simplification. I initially thought this would be a direct replacement of what I was doing, but upon further examination, it is much better. See, the downfall of my IF(ISNA(... nested formula was that it invariably contained a complex VLOOKUP as the argument for the ISNA
function, and then I had to not only specify what to use if the VLOOKUP evaluated to #N/A, but then I had to repeat (usually by copying and pasting) the VLOOKUP for the "else" part of the IF function. IFNA does away with that duplication. Genius! Just enter the argument to evaluate in as the value, and it will return the value of that argument unless it evaluates to #N/A, in which case it returns your value_if_na. - FLOOR.MATH function - this rounds a number down to the nearest number of significance, which is interesting. CEILING.MATH is the version that rounds up, and is also new to Excel.
- DAYS function - I thought this already existed, but upon further inspection, it was NETWORKDAYS that I was thinking of. DAYS gives you the number of days between two dates, while NETWORKDAYS gives you the number of work days between two dates. Regardless, the ability to calculate the number of days between two dates is already available; just subtract one from the other. Microsoft's website did not define whether the DAYS function would treat the dates as integers or have a decimal element when working with dates that have time elements. To make matters worse, NETWORKDAYS uses the syntax (start_date, end_date) while DAYS uses (end_date, start_date). This is a jarring oversight. The only redeeming feature of the DAYS function is that it converts date-like strings automatically, whereas without the function you'd have to use DATEVALUE to do so.
- ARABIC function - I just had to highlight this one because it makes me laugh that its new and that its opposite, ROMAN, has existed and been flying solo at least since 2003. It makes me think of the Super Bowl, because that's the only time I ever see Roman numbers. So for those of you who need to figure out what Super Bowl number we're on, you can now get it from Excel 2013. In other words, =ARABIC(XLVIII) evaluates to 48, but only in the new version of Excel.
- BASE function - The best way I can explain this is with an example: how do you convert a number to binary? Binary has a base of 2, so with this new function, you can type in =BASE(7,2) to convert 7 to binary, with the result of 111. Base 16 would be hexadecimal, but only programmers to my knowledge use that. That's probably true of binary, too, but at least more people know and understand it.
- SHEET and SHEETS functions - this is very telling that programming-minded people are demanding more of Excel. These functions have existed for years in the less-traveled recesses of Excel's Visual Basic macro programming, but are finally being given the light of day in Excel 2013. An obvious and well-founded move on Microsoft's part.
As far as charting, there is one dire chart that still does not and cannot exist in Excel, even with workarounds, and that is one with side-by-side bars with internal stacked groupings. That is, if I want a chart of all my inventory by category, and see what type of inventory it is (i.e. slow-moving, obsolete, high-runner), I need two or more separate charts, one for the total and one for each category or just type of inventory. What I want is one chart with vertical bars side-by-side representing the primary grouping, each with the ability to show sub-groupings as stacked bars. The stacked bars are like making a pie chart out of each major bar. This type of chart would instantly be a big hit, if they only thought to create it. Why, Microsoft, can you create intelligent things like Flash Fill but not give us a simple enhancement such as this?
 |
| My rendering of what a bar chart with stacked subgroupings might look like. In this example, within the "Vegetable" category there might be "Spinach" in dark green and "Carrots" in orange, and within the "Fruit" category there is "Apples" in red and "Bananas" in yellow. You get both the total from each category as well as the breakdown within each category. |
Histograms have been available for several iterations of Excel, and I presume will continue to exist, but hidden in the shadows of the Statistical Analysis feature. I wish Microsoft would put Histograms as a predominant chart type, because they are so telling and powerful in business. Until such time, the Histogram will continue to be my secret weapon.
The Kickass
We have a new term called Slicers. Not all users will understand this immediately, but I'm excited about it. It is a way to "slice" the data, or filter it, probably in chunks rather than individual, painstaking filters. It sounds like there is some setup to it, which is good news, because this is not something I would want the computers of today to try to guess at for me. I suspect this will be one of my favorite new features. Future enhancements will undoubtedly create automatic Slicers, just like we are now seeing automated guesswork done for charts and basic summary data, and I think it's wise to let the users create them first and then build the automation logic based on that.
What Microsoft's page calls "Richer data labels" I call beautiful. Having had to go back to Excel 2003 (temporarily, thankfully), I have been constantly reminded of how far we've come with our chart graphics, and this is absolutely the next step. The look and feel of it is just so right. It claims that they will stay in place when you switch to different types of charts, which is a relief if you've ever perfected a chart only to have to change one thing and have all your beautiful formatting get reset. I am stoked to see more beautiful data labels. I'm going to return to Flash Fill for a moment, because there is a unique application that goes beyond VLOOKUP-type functionality, and this is perhaps the more ingenious aspect. It can be used to "Split" a column of data. Imagine you get an Excel sheet with names in one column, but they are first and last name all in one cell, and you need the first names in one column and the last names in another column. What I've always done thus far is use some combination of string functions like =LEFT(cell_with_name,FIND(" ",cell_with_name)-1) for the first name and =MID(cell_with_name,FIND(" ",cell_with_name),40) for the last
name (which assumed no middle names and thus was incorrect when more than two names were present). I always thought they would create a formula which would find FIRSTNAME and LASTNAME, but instead, they made it even smarter. Flash Fill will guess, based on the context of what you start typing in the column one over from the combined names, and if you accept its assistance, does the heavy lifting for you. Now that is cool.While Microsoft didn't readily spell it out for me, I'm seeing something called PowerView which is mega enhanced charting. Finally a better pie chart (and not just the dumb old pie chart in 3D)! However, this seems to be a premium feature, and does not come with standard Office 365.
Quick Analysis is another feature I will definitely be playing with. I don't know exactly all the ins and outs yet, but look for this to help summarize tables of data quickly and easily.
All in all, I'm excited about what Office 365 brings to my Excel experience. There is nothing devastatingly different like the transition from menus in Excel 2003 to ribbons in Excel 2007, but certainly a lot of goodness and some annoying things that we'll just have to look beyond.
No comments:
Post a Comment