Excel In A Nutshell

I love Excel. The look of it. The feel of it. The functionality. The data transformation features. And I love all the tricks we have up our sleeves to create something powerful and useful! Additionally, Excel is like a beginner’s playground into the world of data types, data structures, data analysis, and basic programming language/theory. It’s a safe, fun, and easy platform for all users to build transferable technical skillsNote: In this article, I’ve bolded key terminology to emphasize the correct conceptual language of the data and software environment we live in.

ExcelNutshell

Excel: The Functionality

Terminology (It’s Important to Know The Official Language!)

Data organization. Data formatting. Data transformation. Data calculations. Logic functions and procedures. Mathematical operations. Statistical applications. Industry-specific formulas (accounting, finance, engineering). Data lookups and information indexing. Data referencing and tagging. Data filtering. Pivot table and query integration. Data summarization. Data analysis tools. Data presentation and visualization. Graphing. Dashboards. Charts. Process automation via programming with Excel VBA. And…wow, what a functional list! How many of these have we mastered?

Limits and Purpose of Excel

With all these tools at our disposal and with a little work and knowledge, Excel can do so much of what we need! Other times a spreadsheet is simply incapable of crossing the boundary from a celled-grid to specialized program or system. One key boundary that Excel struggles with is the relational database boundary of merging data sets and automating queries among different related tables. Another boundary that severely limits Excel compared to newer technologies is its lack of automated, cloud-based, live-platform, and mobile sharing capabilities. If our needs are beyond these boundaries, Excel becomes a limited place to store data, perform manual adhoc analysis, and create pseudo-automated interfaces. This is Excel both at its best and worst. It all comes down to how we use it.

There are now many Excel add-ins that help Excel bridge these gaps, whether by Hyperion/Oracle query products, PowerPivot and PowerBI add-ins, or even using SQL query data links to servers or Microsoft Access tables. An advanced Excel user can also learn to create relational databases manually in Excel, though the end result can be cumbersome to maintain. That being said, knowing how to access, manipulate, and report data is the core skillset that any analyst must have – within Excel or otherwise. Excel can help teach it.

Excel is best used for three purposes:
1) Inputting information, organizing and transforming raw data sets manually, and performing basic data analysis
2) Creating customizable and robust data models
3) Designing customized reporting, user forms, and user interfaces to communicate text and numeric data

Beyond these scopes, Excel can require advanced macro automation or unsustainably-complex manual processes to create and maintain designs and processes. It can feel like trying to build a real house with legos. At this point, more specialized software may be necessary to fully implement the end concept, although specialized software can be cost-prohibitive or it can limit the accessibility and transferrability to other users. From the reverse perspective, getting those specialized programs to bend to our every will, format, and detail makes Excel seem like the personalized do-it-yourself customizable process and software that it can be. Striking a balance of adequate complexity is key when using Excel for advanced purposes.

Still with me? Then let’s accept Excel for what it is. Let’s not compare. Let us jump into the world of Excel and enjoy what it does have to offer.

Using Excel: The Look and Feel

Before we get into the more technical side of Excel, lets start with something a bit more subjective: enjoying the creative process.

The Feeling of Creating

There is something so satisfying about seeing a blank sheet of paper and knowing/wondering what we are going to make of it. It’s that start of the creative process that encourages us to make something, shape it, make it better, and then finally rest in the completed work. Whether it be the first word, shape, color, or raw data, an artist is an artist as he/she begins to create anew.

Even Excel’s blank sheet gains substance as soon as we input data. That’s when the fun starts. Each cell has the capability of serving a purpose and functionality. It’s raw potential for usefulness is incredible, but that’s simply the power of creating in itself.

Refined Look

Excel has drastically improved its graphical features. It now displays subtler colors, smoother edges on shapes and text, and overall has been moving toward a more visual interface. Don’t underestimate the power of a beautiful front end to a spreadsheet. Even pre-calculating taxes can be a wonderful experience; spreadsheets don’t have to look like calculator chicken-scratch.

Calculate our taxes with a few quick inputs!

Calculate our taxes with a few quick inputs!

Using Excel: Tricks We Have Up Our Sleeve

Keyboard Shortcuts

Hardcore purists say all they need is an Excel menu and a keyboard and they can build any spreadsheet from scratch. How? Keyboard shortcuts! A lot of them! It’s liberating and exciting to have literally everything under our fingertips. Keyboard shortcuts allow us to simply THINK and with a few muscle-memory keystrokes it happens. Oh, the time savings and feeling of mastery over our software.

HelloWorldShortcuts

I think I might like to Alt+H+O+W+2 (Column width 2), Alt+W+VG (Turn off gridlines), Down, Right, Ctrl+B, Alt+H+FC (Color select), Alt+H+FC+16 (Font size), and say “Hello, World!”

Why should we care about keyboard shortcuts? Because keyboard shortcuts give us users the power over the program, instead of the program having power over us. To have the fullness of Excel in hand at each moment based on our every whim allows us to create without impediment. We don’t have to access menus and try and find out what the program can do. We can just do. Our train of thought remains stable and on our final product. We don’t get distracted by needing to find or implement something. We just do. This is master-level technical ability.

Programming Mentality

A program should be a function of its user, made for the user to make something of the program. Anytime a program attempts to reduce the user to simply a part of the program or process, then the program becomes the facilitator of services in a consumption-based relationship that requires user understanding instead of user creativity. Apart from automation processes that can be developed in Excel for such end users, Excel is first and foremost designed to be a work-horse for the user’s every whim, not an impossibly-complex tool with limited accessibility.

Most users of Excel simply ask, “Can I do this?” Advanced users ask a different question, “How should I do that?” They live above Excel and exist at the process (ideological) level. Excel is simply the medium and using its tools we can build the process required. There are multiple ways to solve a problem too, so let’s not try and see if Excel can make it work, but rather how we can make Excel work for us in the most effective way possible.

What Kind of Programs/Spreadsheets Are Fun To Create?

Create utility programs or smart pre-programmed calculators for life-scenarios, model your favorite games or issues life, or for developing a visible system to incentivize, track, score, or monitor your daily life according to principles. Data simulation programs like monte carlo analysis can generate distributions of data to see various outcomes; this is especially powerful in the world of stock investing and retirement portfolios. Build models with variable inputs to perform scenario analysis and understand the optimal mix of inputs to generate maximized or preferred output levels. Or, simply try and model real world processes in Excel to learn how to break down the subjective processes of real life into a logical and technical way.

As You Create In Excel: Data Types

Strings/text. Numbers. Dates. Boolean. Cells. Arrays. Addresses. Defined names. Errors. Each of these is a unique type of data that Excel handles differently! Knowing the distinctions of each unlocks the ability to use all of Excel’s functionality and helps troubleshoot most non-syntax-related problems. A key is understanding how Excel processes data in its formulas.

Data Process Within A Formula

An Excel formula follows this 1-2-3 step process: 1. Data In  2. Data Read, Data Compare, Data Pass  3. Data Output. We must first understand what kind of data we are putting into our formula. Certain data types cannot be compared correctly to one another within the formula operations. Watch each step of the formula execution using the “Evaluate Formula” window in the Formula ribbon. After all the formula comparisons, evaluations, and indexing, and passing data from function to function, the formula outputs its result in a data type particular to the formula.

  • Text/string/character formulas output a text
  • Numerical formulas output a number. Know numbers can be stored as text values
  • Date formulas output a unique number (the # of days from the start of year 1900)
  • Boolean and logic formulas output a boolean 0 or 1 represented as FALSE or TRUE
  • Reference formulas output either a cell result or a cell reference
  • Error formulas handle errors from syntax, lookup, evaluation, or reference issues

Properly managing the data type inputs and data type outputs of formulas allows for efficient formula nesting and saves on conversion and error checking. Data types are the context that makes communication possible within a program!

As You Create In Excel: Data Structure

A good data structure is a particular way of organizing data so that it can be used efficiently. Sometimes it’s a science; sometimes an art; sometimes it’s just common sense. In the end, the goal is to manage our data and data structure to smartly create relational data tables within Excel. Because Excel isn’t a relational database software, it can’t do many of the more advanced join and query operations that enable an end user to generate a new table or data subset that combines information from a set of related tables.

Such join and query feats in Excel would need to be managed manually be extracting the data subset into a sheet, looking up data from another relational table, performing boolean analysis to understand which data meets the criteria, and then repeating the process for each bit of required information from other tables.. It’s occasionally feasible! But only once. The information can’t be refreshed unless it’s manually recompiled. If requiring a different variable or subset, you’ll get to run the process again! This is simply unsustainable and is the reason why Excel is not a long-term solution for most companies. Creating macros to do this relational data synthesization is one work around. Using new Excel Add-Ins that enable relational and query functionality is another option. Using other programs may be the best option. However, that is no substitute for being able to structure and manage  information well. If you are able to do it manually, just think of the raw potential with automated systems! We often can learn so much more by understanding the component transformation processes of more advanced software. Excel requires it!

As You Create In Excel: Data Transformation and Manipulation

One of the most powerful things about Excel is its ability to take nearly any kind of data and transform it into a more useful format. Organize or rearrange data sets with cut/copy/paste, sorting and filtering, and converting into an Excel table. Modify or standardize data formats or data syntaxes using formulas or built-in convert-to options. Parse or concatenate data into relevant pieces using Excel text formulas. Categorize, relabel, or reidentify information into proper belonging via mapping tables and data lookup functions. Summarize or highlight details to tell the story of the data using pivot tables and powerful array formulas. Excel can do it all right in front of our eyes as we dictate every detail. We can learn right along with our data!

Mastering Excel: Data Analysis

So we’ve used Excel’s functionality to read, transform, label, manage, and filter our data well. Excellent! Now it’s time for data analysis. Use pivot tables to quickly summarize information. Build additional logic and criteria columns into the data set to stratify, categorize, and identify relevant subsets. Use qualifying SUMIFS, COUNTIFS, unique SUMPRODUCTs, or transformational {ARRAY} formulas to return specific totals from the data set. As data is quantified, create trend graphs, waterfall or bridge charts, or visual status dashboards that measure performance or data distributions. Add additional value through benchmark and comparable analysis. Perform risk and opportunity analysis through forecasting key operational and financial levers that drive results. Scenario plan or stress test the model for endogenous mix changes or exogenous shocks. Ready, set, go!

Mastering Excel: Programming Language

No matter how much a technology changes on the outside, there is one immutable truth on the inside: the programming. Because Excel and its functions are essentially a front-end graphical interface to back-end object-oriented coding operations, learning how to use Excel teaches us many of the key principles of coding itself. Using formulas in Excel teaches us about passing arguments into a function and how it logically returns an output to be used in a main function or process (the spreadsheet). Creating nested formulas, especially nested IF statements, teaches us how to programmatically create a decision tree and how to follow it. Formula errors show us how using proper syntaxes, verifying and transforming data types for correct use in functions, and designing proper error handling are all critical for troubleshooting returned results.

Let’s go one step further and enter into the world Excel VBA. With “Record a Macro” we can begin to see how user input actually translates to program code. Taking these bits and pieces, we can then learn how to build procedures that automate some of our manual process. But what if those processes need to be more universal than referring to a specific cell and a specific worksheet? What if we tried to handle data in the programming itself and only output to the spreadsheet the final product? In trying to work these solutions out, we learn how to write code from scratch, create main and helper functions, use looping statements and decision logic, interface with the user for required information, use temporary files to enable intermediate process steps, debug the code, write more elegant and efficient code, and finally, if it all works out, compile the program and run our process. If our code is universal to Excel and not our spreadsheet, our code should “age” well, version over version, year after year, and from process to process. Why? Because the programming is the immutable truth. If you know how to pick an apple, it doesn’t matter what kind of apple it is, and in many cases, it doesn’t matter the fruit as the technology can be slightly adapted for the specific modifications necessary.

At the end of the day, Excel allows any user to build a custom-built “program” within Excel itself. Excel in its most basic usage of just inputting data or writing things down in Excel is a feasible option very much like using Microsoft Word; in this limited usage, however, Excel is nothing more than a grid-based blank sheet of paper. However, it’s when we use excel to structure, format, manipulate, transform, calculate, and look up such inputted data per our user-defined request all at the click of a button or user selection – that we create something more like a “program” that returns for the user relevant and useful information. Creating such a miniature program should make us smile, because we’re on our way to bigger and better things, and because we’ve begun to understand the underlying foundations of a program’s process.

ExcelMacroExample

This VBA function stores the “list” of defined names an Excel workbook into an array variable (a matrix) for use in another function.

Conclusion

Do we really need to go this far? Is all the above jargon necessary? It’s not a matter of need. Rather, it’s a matter of seeing the universal defining structure and working order of Excel as program elements to a greater whole. As I said earlier, Excel is a safe, fun, and easy platform for all users to build transferable technical skills across platforms and technology. Look how much we can build…and beyond! We most certainly will not come out where we started. And that is the beauty of Excel as an introductory and gateway software.

Tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

One Response to Excel In A Nutshell

  1. Pingback: What Is…Windchill? | My Life Inside My Head

Let me know how this post helped you!