One “Excel-adjacent” tool that can be particularly intimidating for prospective learners is Python. Shouldn’t this be the last tool on the learning list, many Excel users think, given that it’s not even a Microsoft product and it literally requires learning a new language to use?

While Python may not be the best choice for every Excel user, it’s worth serious consideration for those looking to build complex automations, version-controlled projects and other advanced development products. Here’s the role of Python in modern analytics and modern Excel.

A growing stack requires glue

When I started as an analyst, my choices started and ended with Excel. All data, reporting, dashboards, everything was under that one green roof.

Fast forward a few years and there’s Power BI, Office Scripts, Jupyter Notebooks and more. This broadening of the analyst’s tech stack is right in line with wider trends in tech: a move away from one single, monolithic application to a loosely connected suite of specialized products.

To make this architecture work, a “conductor” or “glue” is needed. Whether it’s reading a dataset in from one source and visualizing it another, or deploying a machine learning model from the cloud to an end-user’s dashboard, Python is a great choice for this glue. It’s one of the rare languages that’s used for simple amateur-written scripts to enterprise-level applications alike, and can work smoothly with a variety of operating systems and other programming languages.

Microsoft has acknowledged and celebrated Python’s role as a “glue” language, and it’s already available to use for a variety of purposes in Azure, Power BI, SQL Server and more. And while Python isn’t yet officially supported for Excel, it still has advantages. Plus, think of all the other places you’ll be able to use Python… and who else is using it!

Network effects mean faster development time

“Everybody’s doing it” isn’t usually a good reason to engage in something… but, in the case of programming languages, it may be a good enough reason.

Have you ever heard of network effects? It’s the idea that the value of something grows as more people use it. Programming languages have network effects — the more programmers there are, the more code is shared, the more code there is to use and then build on… it’s a virtuous cycle.

Because Python is such a neutral “glue” language, it’s been adopted in professions as wide ranging as database management, web development, data analytics and more. This means it’s a good chance that no matter where your Excel project takes you, or what tools you need, someone you collaborate with also “speaks” Python.

For example, maybe you develop an inventory tracker or other application using Excel. The program gets too unwieldy for a workbook, or it becomes so popular that your organization wants it to become a standalone web program. The turnaround time for this project will be much faster if the existing code is already in Python.

I find it unfortunate that data analysts and other tech professionals often work with a completely different set of tools — it can make collaboration slow and tedious. By adopting a shared language, the network effects expand and development time drops.

Bring modern development to Excel

“Software developer” has become a coveted job title over recent years, almost as sexy as “data scientist.” Sadly, the methods and best practices developed by this profession have not caught on with BI or VBA developers. At an organization level, doesn’t it seem like trouble to have two tech professionals both called “developers” going about their work in totally different methodologies?

Python offers the modern Excel developer to implement these best practices. These include the following:

Unit testing

Most programming languages offer automated unit tests as a way to confirm that code is performing as expected. Unfortunately, base Excel and VBA do not. There are a few workaround tools here, but Python is a solid candidate for unit testing with the network effects to boot.

Automated unit testing makes applications more reliable and less likely to break — particularly useful for Excel workbooks that users with a variety of technical backgrounds might pick up.

Version control

Another development tool de jure that Excel lacks is a version control system. This tracks changes in a repository, allowing users to, among other things, see who contributed what, when and revert back to older versions.

If you’ve ever pulled your hair out over determining the difference between budget-model-final.xlsx and budget-model-FINAL-final.xlsx, then you understand why version control might be helpful.

Excel does provide some basic version control-esque features like the ability to view version history in OneDrive and the Spreadsheet Compare add-in, but lacks many of the features that could be found by moving all code production to Python.

Package development and distribution

The previous two features, while useful for a serious developer, may not be all that interesting for a typical Excel user. Perhaps you are looking for a more immediate reason to learn Python as part of your day-to-day analysis tasks… is it still worth learning?

And to that, all I have to say is one word: packages.

Don’t get me wrong, I love building things on the computer. But if someone’s already built something that does exactly what I want, I’m not reinventing the wheel. Python’s features for building and sharing packages, particularly through the Python Package Index, open a whole world of tools in a way that would be very difficult to replicate through Excel add-ins or VBA modules.

Not only that, but the pip manager and other tools provides methods for installing and managing these packages.

Whether you’re looking to collect data from the Census API, describe an image, or just have a function to generate descriptive statistics, access to Python packages is well worth the price of learning Python code. Some of these packages are even designed to work well with Excel.

Python and the future of Excel

In an AI-driven world, it can seem like learning to code is exactly the wrong choice to stay relevant. Ironically, however, as more data gets generated in more formats (including some AI-powered), the need to code may be more relevant than ever.

As an integral part of the modern analytics stack, the possibilities to integrate Python with Excel are only growing as Excel itself takes on a variety of AI-driven features.

The post What is the role of Python in modern Excel? first appeared on Stringfest Analytics.

I have been watching “Universal Principles of Design” on Lynda.com, an absolutely fascinating series on design with rather un-intuitive — and powerful — lessons for us spreadsheeters.

I have frequently taught that your most important job as an analyst is to make your boss’s life easier — namely, by providing, timely, accurate, insightful data preparation and analysis. 

If you can’t do the messy work with data, why have you around?

But maybe a limited element of DIY is useful. 

Enter the IKEA effect. From Wikipedia:

The IKEA effect is a cognitive bias in which consumers place a disproportionately high value on products they partially created.[1] The name derives from the Swedish manufacturer and furniture retailer IKEA, which sells many furniture products that require assembly.

This makes sense. I believe that humans are fundamentally creative and crave interactivity. Deep down, everyone loves a good old arts & crafts session. Knowingly or not, IKEA tapped into this need. This seems to contradict our demand for convenience and service. But IKEA balances convenience and interaction just right with nearly foolproof directions. 

So what does this mean for your next spreadsheet? 

Add an IKEA effect. 

Understood, many managers are terrified of using or distributing interactive spreadsheets, for fear of “breaking something.” So make it simple. Balance convenience and interaction, just like IKEA.

Consider using form controls, for example. Jordan Goldmeier covers those beautifully in Excel.TV’s Dashboard Pro course. 

Not as involved as VBA, simpler. Who doesn’t love clicking a button?

Interactivity gets people personally invested in your work. Cells become Lego blocks.

Further Reading:

NPR, “Why You Love That Ikea Table, Even If It’s Crooked”

The post Could Spreadsheet Developers Use the IKEA Effect? first appeared on Stringfest Analytics.

One of my courses this year is a seminar on information systems. Before each class, we each write a “conversation starter” incorporating our thoughts on the papers.

A topic that has greatly interested me lately is waterfall vs. agile development. “Waterfall” projects hinge on all-or-nothing strategy: let’s spend weeks and months developing something, then release in one fell swoop (like a waterfall building at the top and then crashing down.).

In contrast, agile looks at how to release smaller bits of product to be tested by the audience and incorporated back in an iterative process.

I argue that academic publishing is still built largely on the waterfall approach (let’s spend all our time on one big paper submission and hope it gets in) rather than lean (let’s use digital media to develop a minimally viable research proposal.).

I develop this (and other questions of IS research) below:

——-

Keen in “MIS Research: Reference Disciplines and a Cumulative Tradition” offers a definition of the field from MIT’s Center for Information Systems Research: “a study of the effective design, delivery, and usage of information systems in organizations.” Given that widespread computing in organizations is new, we should not be too surprised that MIS is still finding its bearings as a research discipline. But is this novelty stifling the development of a unique discipline with its own frameworks and agendas?

Yes and no. The adoption of information systems has made good organizations better and bad organizations worse. Information systems can deliver timely, actionable data or keep bright people mired in the weeds of information. So these problems of information and organization are not new – but digitization of information has exacerbated the effects.

IS as a discipline can answer how organizations can use information as an asset – but how do we do this? This requires that IS frames itself both inside and outside academia.

My first job after graduation was in demand planning at a national specialty retailer. I was ready to be the best forecaster in the company’s history. Exponential smoothing, EOQ – you name the theory, I would be using it.
It turns out that very little of my job was built on theories of finance and economics but instead on principles of information. What data do we need? How are we going to get it? Much of my time was spent on fruitless reporting, unheeded requirements analyses, and so forth. I ended up leaving the demand planning job to go into operations finance at a public hospital, where the information problem was so much worse! It was this frustration of how companies use information that in part led me to the program.

A theme of the readings is how to place IS within the “practitioner-scholar” continuum. Can research be rigorous and not esoteric? How can IS establish itself as a discipline with a well-defined research agenda while still benefitting non-academics? One model I see here is economics. As the field has become more rigorous, it has been less understood by practitioners. I have noticed that many of the pioneers of MIS have a background in economics. Maybe they were frustrated with the way things were going in that profession? I was – when looking at graduate programs, so little of the research I saw appeared as useful social science.

So maybe we need to model IS on economics with caution. But the comparison with IS to law and medicine in Davenport and Markus can only go so far. The boundary between professional and user is not as distinct in IS. Most people with direct contact with the legal or medical professionals are themselves lawyers or doctors.

Not so information – everyone is in contact with management information systems. And most of them do not want to be a data architect. They want to get back to their jobs of being an accountant, a non-profit coordinator, a teacher, etc. Those people may also have valuable insights on information and organization, while perhaps not being so knowledgeable about the “IS artifact.” So it is more difficult to see IS as a profession than law or medicine.

This tension of how to fit IS inside management research becomes even more difficult with Big Data. There is an implicit conclusion here that “big = more.” But Ackoff had a great retort to this, even sixty years ago. Do managers really need all the information they think they do? Many managers, under the false pretense of doing “big data,” have simply added more variables to their models. And the more complex these calculations become, the more effort it takes to maintain it. Again, we see people without the necessary skill (or passion) doing “shadow IS” work under a misunderstanding of Big Data.

So, how do these tensions resolve? One meeting point between academia and trade could be publishing. While peer-reviewed journals absolutely have their place, maybe we need to look at media to help the profession outside academia. I see traditional publishing as a “waterfall” form of development where all the work is grouped into one result, which will either be acceptance or rejection. Can we adopt more “agile” based research development via digital media, consulting, practitioner-focused conferences, etc.? Maybe the breakthroughs won’t come from a blog post, but they could be a place for non-academics to collaborate. This allows practitioners to learn from and collaborate with academic research, while setting the boundaries and guidelines for IS academics.

The post Go Researching Waterfalls first appeared on Stringfest Analytics.

If you’re a student, you may be more familiar with them than you think.

In our example, we want to find our final term grade based on the weighted average of four scores. Because some are assigned greater weight than others, we do not want to do a straight average.

Rather, we take a weighted average using SUMPRODUCT. 

This Excel function cross-multiplies all cells in your selected arrays and adds the result.

So in our example, SUMPRODUC is adding 25% * 92, 25% * 85, and so forth.

The post Don’t Weight to use SUMPRODUCT first appeared on Stringfest Analytics.

Chandoo of chandoo.org often emphasizes the importance of playfulness and interaction when building solutions in Excel.

A good analyst knows how to combine disparate functions and features in Excel to engineer new solutions. This takes a creative mindset — don’t think that spreadsheets are for the uninspired! 

The post Excel: Lego for Grown-ups first appeared on Stringfest Analytics.

Chandoo runs a great Excel site at chandoo.org. I really like his site because he frames Excel problems from the viewpoint of a real life data analyst. He’s been one and he knows what makes analysts great.

Here’s a great quote on what to emphasize in your spreadsheet modelling — usefulness to clients, not obscure, needless formulas.

This is not to say it’s unimportant to have a deep Excel toolbox….this can greatly improve efficiently and depth of analysis. However, it’s just a tool, not the goal in itself.

Quote from “The VLOOKUP Book – Definitive guide to Microsoft Excel lookup formulas” by Purnachandra Rao Duggirala –

“I see many analysts wasting hours of time trying to fine tune their formulas. The reality is your bosses or clients never ask for fastest spreadsheets. They ask for most usable, accurate and simple ones. So instead of focusing your energies to reduce the length of your formulas or trying to use complex ones, you should spend time impressing your bosses & clients.”

Start reading this book for free: http://amzn.to/1XEvY1j

The post Happy boss > Fancy formula first appeared on Stringfest Analytics.

Image from Gartner study

If you’re worried that your Excel skills may soon become obsolete, take comfort. A recent Gartner study positions Microsoft as an industry leader in BI and Analytics.

I’m very impressed with Microsoft’s products and services lately. They have become a more collaborative, multi-platform BI provider. Their customer service is killer. I frequently tweet with the Excel team and was even given a year of free Office 365 for my online enthusiasm for Excel.

A lot of people think Excel is going to be eclipsed by more robust data solutions like Tableau. Microsoft has dug in to keep Excel the king of BI, loading it with cutting-edge features that very few users are yet aware of.

The only problem I see is getting consumers to see Excel as more than a spreadsheet. People will not see the benefit of “just another plug-in” like PowerPivot. Moreover IT may not want to bother adding more features to peoples’ machines. Other than that, way to go Excel

The post Never Fear, Excel is Here (to stay!) first appeared on Stringfest Analytics.

This is part of the “Hired with Excel” blog. Read the last post here.

I have drilled home the point that you cannot rely on your employer to train you. I want to elaborate on this point a little more to explain why learning these skills on your own is essential. After training, you can rest by playing games such as slot online. One of my worst assumptions in college was that I didn’t have to learn Excel because I would find a good employer who would train me. This was the most debilitating attitude I had in my “hired with Excel” process.

The first thing to remember is that old adage: “It’s a business, not a charity.” Training employees is expensive. Consider: Candidate A never learned Excel, and Candidate B is ready to go. Whom do you think will get hired?

Laying aside the costs associated with training, there is an even more blunt reason why self-training is needed. Even if companies wanted to spend the money training you, they probably couldn’t. This leads some companies to rely on staffing agencies such as interim romania franta for skilled talent acquisition.

Technology advances rapidly, and many of the Excel tools discussed in this book were not even available until Excel 2007. By that time, many hiring managers had already done their time in the cubicle. They no longer spend their days looking at spreadsheets. So their knowledge of Excel is not up to date.

As an analyst, you will be asked to do things with Excel that even your manager might not know how to do.

The post Hired with Excel: Why You Must Train Yourself first appeared on Stringfest Analytics.