August 5, 2021

Top 10 Sigma Functions vs. Excel

Julian Alvarado
Sr. Content Marketing Manager, Sigma
Top 10 Sigma Functions vs. Excel

62% of people use spreadsheets when exploring data. Spreadsheets are easy to use and offer flexibility for business users. Even people with technical skills like using spreadsheets — 88% of people who write SQL still use Excel when exploring data. The spreadsheet UI is ideal for data exploration, and nearly everyone knows how to work with this familiar interface.

Sigma’s spreadsheet-based interface is one of the primary reasons our customers love using our platform. The collaborative canvas enables everyone to iterate on analyses, create visualizations, tell data stories, and find answers to critical questions — all using an intuitive spreadsheet UI. Let’s look at the evolution of the spreadsheet for data analytics and examine the most popular functions in Sigma and Excel to see how they compare.

The Evolution of the Spreadsheet

While spreadsheets have been the data analytics tool of choice for decades, traditional spreadsheets like Excel experience several problems in the modern world. These issues are primarily a result of the Big Data revolution and advances in cybercrime.

Big Data demands more power

First, spreadsheets can only handle about one million rows of data. This isn’t sufficient for the large data sets generated by today’s applications, devices, IoT solutions, and other data sources. Limited rows become especially problematic when combining data sets from multiple sources to see a fuller picture of a situation. Spreadsheets then become slow and crash under heavy data loads.

Additionally, spreadsheets are static. Once data is loaded in, that’s it — it doesn’t update as conditions change or new information is generated. As a result, decision-makers often end up working with outdated information, leading to inaccurate conclusions.

Traditional spreadsheets are vulnerable to security breaches

Another major problem with traditional spreadsheets is that they’re incredibly difficult to secure. Sharing spreadsheet files with others creates version control, data governance, and security issues. Spreadsheets may be accidentally emailed, uploaded, or downloaded by anyone at any point. If users are working with personal information or sensitive data, this vulnerability opens the company up to significant risk.

Spreadsheets are evolving

But spreadsheets for data analysis are evolving to meet the needs of modern decision-makers and address the problems associated with Excel and other traditional spreadsheets. First, Google Sheets turned the spreadsheet into a real-time, collaborative tool. We saw Airtable reach unicorn status by reinventing the spreadsheet to help non-technical folks write code. And Smartsheet has turned the spreadsheet into an effective and intuitive project management platform.

Now, Sigma’s spreadsheet-like interface harnesses the speed, scale, and flexibility of the cloud and translates familiar, Excel-like formulas into powerful SQL to democratize analytics for all. Sigma connects directly to all major cloud data platforms and leverages their power. It crunches through billions of rows of data at lightning speed and works with the live, accurate data in the cloud data platform, automatically keeps analyses up to date.

Sigma also automatically translates spreadsheet-like formulas and functions into optimized, machine-generated  SQL to communicate with cloud data platforms, enables complex, iterative analysis at scale. And it makes it easy to share, reuse, and build on others’ analyses safely, governed with robust access roles and user permissions.

Sigma vs. Excel Functions

Sigma was built by people who love spreadsheets for people who love spreadsheets. The functions that Sigma offers are the ones you know and use in Excel. Let’s compare the most popular functions that Sigma users employ and the most popular Excel functions for data analytics.

Top 10 Sigma Functions

  • SUM — The SUM function adds values together. You can add individual values, cell ranges, or a combination of all three.
  • IF — The IF function enables you to make logical comparisons between a value and what you expect to see. An If statement could have two results: the first result appearing if your comparison is True, the second appearing your comparison is False. This function is valuable because it allows you to automate decision-making.
  • DATETRUNC — DATETRUNC truncates a date to the specified date part, such as the month.
  • LEAD — Use the LEAD function to shift a column upward. It's helpful to reference something in another row, like week-over-week numbers.
  • COALESCE — The COALESCE function returns the first non-null value from the arguments provided. You can use this function to replace a blank value with a different value while leaving non-blank values unchanged.
  • FIRST — The FIRST function repeats the first row value of the given column for every row in the output column. It’s often used in single-value visualizations or other use cases when you want to know the latest. For example, if you calculate yearly sales numbers and sort so that the most recent year is on top, First displays the most recent sales totals in a Single Value chart.
  • Max — MAX allows you to find the maximum value for a column or group. It will count numbers but ignore empty cells, text, TRUE and FALSE values, and text values.
  • SUMIF — SUMIF returns the sum of cells that meet a single condition based on dates, numbers, and text. It supports logical operators and wildcards for partial matching. SUMIFS does the same thing, but it can test many conditions instead of just one.
  • CONTAINS — The CONTAINS function allows you to find the position of a substring in a text string.
  • RANK — RANK returns the rank of a numeric value when compared to a list of other numeric values. IT can rank values from largest to smallest as well as smallest to largest.

Top 10 Excel Functions for Data Analysis

  • IF — Excel’s IF function works the same as Sigma’s.
  • SUMIFS — This one also works the same as Sigma’s.
  • COUNTIFS —COUNTIFS is similar to the SUMIFS function, but it counts the number of values that meet specified criteria.
  • TRIM — The TRIM function removes all spaces from a cell except the single spaces between words. Sigma also supports TRIM.
  • CONCATENATE — This function combines the values from multiple cells into one, and it’s helpful for piecing together various information (such as a customer’s full name or address). Sigma’s equivalent function is CONCAT.
  • LEFT/RIGHT — Using the LEFT function, you can pull a specific number of characters from a cell into another cell, starting at the leftmost point. The RIGHT function accomplishes the same except starting at the rightmost point. Sigma also supports these functions
  • VLOOKUP — VLOOKUP will look for a value in a table and return information from another column relating to that value. It’s useful for combining data from different lists into one or comparing two lists for matching or missing information. 
  • IFERROR — The IFERROR function is useful for catching errors and returning a more helpful message than the standard message when an error is detected. This function is an easier way to manage errors than using more complicated nested IF statements.
  • VALUE — The VALUE function converts numbers stored as text into number format.
  • UNIQUE — The UNIQUE function returns a list of unique values from a range or array. (This function is only available in the Office 365 version of Excel.)

Analyze Data With the Ease of a Spreadsheet and the Power of the Cloud

Sigma is reimagining the analytics workflow by allowing cross-functional teams to investigate data at cloud scale and speed using their existing spreadsheet skills. Sigma allows users to seamlessly drill from high-level dashboards into billions of rows of data at granular detail. It provides a collaborative canvas where teams can communicate confidently using data.  

Sigma Workbooks accelerates data-driven decisions by putting the power of data analytics across billions of rows of live data directly into the hands of marketers, accountants, product managers, and other cross-functional business team members. Inspired by consumer applications, the easy-to-use interface combines the simplicity of a spreadsheet with the power of the cloud to enable everyone to collaborate and iterate on analyses, share data narratives, and find answers to critical questions in seconds.

Let’s Sigma together! Schedule a demo today.

We are Sigma.

Sigma is a cloud-native analytics platform that uses a familiar spreadsheet interface to give business users instant access to explore and get insights from their cloud data warehouse. It requires no code or special training to explore billions of rows, augment with new data, or perform “what if” analysis on all data in real⁠-⁠time.