Ebook

Switching from Excel to Sigma

CONTENT

Download PDFSo you're making the switch to Sigma from traditional spreadsheets.

Congrats! You're not alone. We've worked with tons of customers in your place. Sigma will unlock new possibilities for your team, providing you with a familiar spreadsheet experience backed by the full power of SQL and the cloud data warehouse.

Before you get started, we’ve got a few tips tips to make things easier for you and your team during the transition.

When working with large data sets, calculating everything by column—instead of trying to select millions of cells—helps prevent errors.

A Harvard paper on the effect of debt on market growth was released in 2010. It was a hit. Almost immediately it began to be cited everywhere. The only problem? The data was incorrect due to a selection error. Instead of including all of the data in their calculations, the authors only included a fraction. Oops.

That’s why Sigma calculations are performed on columns of data, instead of individual cells. By selecting entire columns, you can avoid most selection errors—one of the most common Excel errors—making it easier to work with large data sets. You shouldn’t have to individually select cells from a billion rows. Even the thought is exhausting.

Plus, when connecting to live data you never know how many rows will appear in a worksheet. Every day more data is flowing into your data warehouse, which means your worksheet may have 1 billion rows on Monday, and 1.3 billion rows by Friday.

If you had to manually select individual cells of data, worksheets would quickly be out of date. By working with entire columns you know that all of the data is always being considered, no matter how many rows are added.

To create a calculation in Sigma, simply create a new column and enter the formula into the Formula Bar (sound familiar?). The formula is then applied to every row of data. It’s that easy.

For a full breakdown on Sigma’s Formula Bar, check out our video on using formulas. Now you’re probably thinking “what about applying the calculation to entire groups of data, like adding together all the revenue in a month?” That leads us to our next key difference between Sigma and Excel.

Now you’re probably thinking “what about applying the calculation to entire groups of data, like adding together all the revenue in a month?” That leads us to our next key difference between Sigma and Excel.

With traditional spreadsheets, data is flat. It’s easy to sort and select data, but the only way to create calculations based on subsets of that data is to create a pivot table. With Sigma, you can organize data into levels and groups—letting you quickly calculate aggregates to learn more about your data.

What are groups, you ask? What does adding a new level mean? Put simply, it is an innovative way to organize your data within Sigma’s interface. Groups organize all of the data in the spreadsheet based on a common characteristic in a chosen column.

Let’s go back to the example of wanting to see all of the revenue in a month. If you have line item data, you can get monthly revenue in three steps. First, we’ll find what month and year for each order by taking the invoice date and truncated it to month. This means Sigma will interpret the data as just having the month and year. We’ll call this new column ‘Month’

Now that we have the ‘Month’ column, we’ll use that as a grouping key. This will group all of the line items in the same month together. The ‘Month’ column will now sit on a new level, showing that it is being used to organize the worksheet. Calculations placed on this new level apply to all of the rows in the group. Functions referred to as aggregation function summarize all of the data in the group it is applied to. Sum is one type of aggregation function. Creating a new column and using a formula to sum all of the line item totals gives you the revenue total for each month.

Fresh & Secure

Copy and paste errors are another common mistake made in traditional spreadsheets. Accidentally changing data when trying to create calculations has happened to everyone.

Having the source data be in the same place that you are trying to create calculations in is a recipe for disaster. That’s why so many people immediately create a copy. Then another copy, just in case, which is exhausting and unnecessary.

With Sigma, you are never changing the data. The data stays safe in the data warehouse. Sigma performs a series of calculations on the data and shows you the results, but the underlying data remains unchanged.

The fact that the data is directly from the warehouse also means that the data is always up to date. One of the benefits of doing analysis based on columns is that new data will automatically be included in the calculations. Rather than redoing the same analysis every month, you just have to open the worksheet.

Getting to interesting insights often means connecting data sets from several different places. That’s possible in spreadsheet programs, but it can be fraught. VLOOKUP is a common function used for that, but it can slow down your document and can be confusing to get right.

Sigma makes combining data sets, also known as joining data, an easy process. Using a fully visual process, you can use the most common SQL joins as well as a Lookup join that is similar to VLOOKUP.

To join data together, each dataset just needs to have a unique identifier in common. This is often something like Customer ID or Order Number. This overlapping piece of information is how Sigma knows how to match up the rows of data and is known as a Join Key.

Being able to join together data from different sources is a powerful feature that will let you start exploring your data in new ways. Learn more about how to join data together in Sigma.

Are you an Excel formula wizard? Good news: the formulas you know and love are available in Sigma. Sigma has 100+ functions, giving you the functionality of your favorite spreadsheet program as well as the functionality of SQL. Check out the most popular functions in Sigma.

How you apply formulas in Sigma is similar to spreadsheet programs, but in Sigma formulas define an entire column rather than a cell. This means applying common formulas like Sum and Avg are a little different than in Excel. Aggregate functions like Sum and Avg apply to all of the data in the level below. To get the results of an entire sheet, you can create a total. To get the results for a specific group of data, you can group the worksheet, then create a column with the formula on the new level.

Sigma formulas take you beyond what you can do in Excel. Window Functions are a powerful feature of SQL that we have turned into functions you can use in the formula bar. Functions like CountDistinct and BinRange open up new ways to analyze your data.

Writing functions in Sigma is similar to Excel, but we’ve taken steps to make it easier. We use several operators that make common actions easier. Easily create an If function to check for a product where the color is red and the value is over $5 using the operator AND. Just set the test to be [Color]=”Red” AND [Value] > 5. Operators allow you to express information without having to nest functions unnecessarily.

Even Excel wizards can’t resist the power of Sigma.

Get the big picture data with Totals. Totals are single cells that reflect the value of all of the data from the columns used in the formula. Aggregate formulas placed on levels give the results for the groups of data. Aggregates placed in a Total gives the results for all of the data in the worksheet. When you filter the worksheet, the value of the totals change to reflect the data currently visible.

To make it even more powerful, totals can be referenced in formulas. You can create a total, and reference that total in a calculation on a group level to find percent of total.

Visualize your totals to give them weight on dashboards. Single value charts can show the value of totals, and create comparisons between two totals.

Totals are helpful any time you want to calculate a value based on all of the data in a worksheet.

Are you in love with pivot tables? A switch to Sigma doesn’t mean giving them up, it means only having to make the same table once. Build pivot tables in Sigma and keep the data updated with the most recent data from your warehouse. Rather than building the same pivot table every month, spend your time on creating a unique analysis.

Sigma pivot tables are built similarly to Excel pivot tables. You select the columns of data to serve as your Columns, Rows and Values. Apply an aggregation to the Value data directly in the pivot table, and add totals to the columns and rows with just a few clicks.

Visualize the live data from your cloud data warehouse with charts in Sigma. Like in Excel, you define which data serves as the axis of the chart, and which data serves as the graphed values. Easily swap between different chart types to see how your data looks in different visualizations.

Charts let you build line charts, map charts, scatter plots, pie charts, bar charts, grouped bar charts, and combination charts with bars and lines. It’s also where you can build pivot tables, tables, and the ‘big number’ charts popular on dashboards.

The benefit of building in Sigma is that data is always fresh. Your weekly reports can be built once, then emailed out to your team every Monday morning. It’s time to reclaim hours of your day, not waste them repeating the same workflows.

Parameters in Sigma are like input cells in Excel. You set the value in one place in the spreadsheet, then reference it in calculations throughout. It gives people a quick way to change assumptions and have those changes propagate to all of the calculations.

Parameters can be open input or you can specify a list of values as input options.

A powerful feature of parameters is that they can be exposed in dashboards. Create a parameter in a worksheet, and then put that parameter on the dashboard next to the visualizations it will affect. Dashboard viewers can change the parameter inputs and see how the charts and visualizations update. Check out how parameters work on this live dashboard.

We make it easy to bring your local data into the cloud. We know that sometimes, all of the data you want to work with isn’t in the cloud data warehouse. For those times, Sigma has CSV upload. Connect data from your spreadsheets to data in your cloud data warehouse.

Once you upload a CSV into Sigma, you create a worksheet from the data. Join this data to any of the data in your cloud data warehouse, marrying live data with your CSV data.

With CSV upload, Sigma offers you a way to merge existing data processes with the organizational data in the cloud. Sales managers can write up their sales goals in a spreadsheet, then upload those values to track against live sales opportunities. Analysts that download data from various systems can take that spreadsheet and marry it with the live data from the warehouse. It creates a personalized data experience all in one tool.

Rather than forcing you to export data into Excel, Sigma brings everything together while keeping data live.