You’ve never programmed in your life, and the last time you did any math was in high school or college freshman year. Yet, with the increasing invasion of tech and data into business, it helps to understand a few things about computing. Here are some concepts that can improve your productivity by leaps and bounds, even if the closest you ever get to coding is an Excel spreadsheet.
#1: Data types
Broadly, the information we store about our business mostly comes in three forms: text, numbers, and dates. When working with any kind of data, think about the format that you expect it to come in – for example, if you want to sort a numerical value, make sure that the software you’re using recognizes that piece of data as a number.
In Excel, you can format cells in many different ways. Numbers are particularly tricky – they can be formatted either as numbers or as text, and you will want to do this differently depending on what you’re using the number for. If it is a product ID, for example, the number has no meaning except as an easy way to get to a particular product. So, you can store it as text since it has no meaning in a mathematical sense. The price of a product, however, needs to be stored as a number because its value matters – you may want to know how many products above $100 you have in stock, for example.
#2: Date functions
Lots of times, dates matter in a business. Many businesses have patterns around time; there will be trends in volume based on the time of year, day of week, or even the hour in the day. Restaurants peak during lunch and dinner hours. Paddleboard rental kiosks are busiest in summer if you’re in a temperate country. Even in always-sunny Singapore, your kiosk still has a seasonality; its business will peak during weekends and school holidays.
To extract and analyze these patterns, often you will need to group dates by date parts – comparing one Sunday with another, or breaking your year down into weeks to compare with the previous year. Being very familiar with date functions – how to extract the year, month, day and day of week from a date; how to add and subtract days from a date; how to get to the nearest Monday, Sunday or whatever day your business measures its weeks in – will help you quickly get to insights about trends and patterns in your business.
#3: Conditional logic
In Excel, you have the ability to use the “IF” function to do different things based on a given value meeting a condition (or not). Indeed, “if” statements are a common cornerstone of programming. After all, computers make our lives easier because they can make these types of straightforward decisions at scale – provided that we define what they’re supposed to decide, in a perfectly explicit manner. That’s what the “if” statement is for.
Use “if” functions strategically in your Excel spreadsheets to break down complex or repetitive analyses and procedures into simple steps. For example, let’s say you own a bicycle rental kiosk, and you charge $5 per hour on a weekday and $7 per hour on a weekend. You have records of all your rentals and how many hours each one lasted. Instead of going through all your rental records and typing (or writing) in the price of each, try using Excel functions to extract the day of week, then an “if” function to assign the price per hour to $7 if the day of week was a Saturday or Sunday, or else $5 if it wasn’t.
#4: String functions
Lots of times, you can find patterns in text. For instance, you might combine multiple pieces of information about a product into its name. Thinking about how you can systematically break those pieces of information into their various components can help you come up with more organized ways of creating product names, in this case.
To get part of the information out of a piece of text with more than one item in it, look for delimiters within the text. Any character that splits text into parts in a repeatable way can be a delimiter. For example, you can split this paragraph into its sentences by finding all the full stops.
After you know how you want to split up a piece of text into smaller and simpler parts, you need ways to extract those smaller parts in order to group and analyze your data further. The FIND, LEFT and RIGHT functions in Excel are useful for isolating parts of a long, complex piece of text.
Pseudo-code is still coding, made simple!
Tools such as Excel – and to a certain extent Tableau and Alteryx – liberate you from having to learn the syntax of programming languages. They let you use the same thought processes that you would need when writing your own script, without having to create and debug actual code. Therefore, even as a non-programmer, you can automate tasks that you’d otherwise need to outsource to a developer or engineer. Frame your processes in a structured, scalable way – and you can shave hours off your days by creating simple Excel automations.