Product Guides

The Alteryx Guide for Excel Users

Working with data in Excel can be simple, but it can also be challenging and time-consuming, especially when working with multiple sources and formats of data. Data has to fit into rows and columns, has size limitations, and joining data from multiple spreadsheets requires complex formulas that leave your results vulnerable to errors. What's more, whenever your data changes, you have to repeat each step in the data prep and blending process all over again.

Alteryx takes a different approach with a workflow-based environment that allows you to prep, blend and analyze data from multiple data sources and data types, including unstructured data. And because you spend your time constructing a repeatable workflow that documents exactly how you work with data, you simply have to re-run the workflow any time your data changes — resulting in less time spent analyzing data, less errors, and more consistency.

We've listed some of the most common data-related tasks that many analysts attempt to do in Excel, and alongside them show how you would do it using Alteryx. These examples are meant to help Excel users understand how to translate their Excel knowledge into an Alteryx tool and workflow environment.

To learn more about how Alteryx can help empower Excel users, download the whitepaper Enabling Self-Service Data Analytics: 5 Ways Alteryx Improves Excel Processes. Then, follow along by downloading a free trial of Alteryx.

Input

Opening Files

In Excel
In Alteryx

To open the Excel program click File and then click Open. 

Alteryx for Exel Opening Files

Then simply locate the folder where the file is located. Then just click and open. 

Alteryx for Exel Opening Files 2

  DB File InputTo access data in Alteryx, you drag-and-drop an Input Data Tool onto the canvas, locate the database, and simply select.

Then click on the Connect a File or Database drop down in the configuration section. 

Open Files Alteryx

And locate your file wherever it might be located. 
Open Files Alteryx 2

 

Opening Different File Types/Structures

In Excel
In Alteryx

Excel can open a limited amount of structured data from local files. 
 

Excel Open Different File

The file types, sources and structure can be expanded by accessing data from the web, text and a wider range of other data sources. 

Excel Open Different File 2

DB File Input   Alteryx offers users a wide range of data access to multiple sources and structures of data. Using the Input Tool, users can access data locally from Excel, Access, XML, SAS, SPSS, MapInfo and even data stored in databases or HDFS. 

Alteryx Opening Different File Types

In addition, Alteryx has direct connectors to access data stored in a cloud systems like Amazon S3, Twitter, Foursquare, Marketo, Salesforce, Microsoft Sharepoint or other Big Data environments like Amazon Redshift, Impala, Spark and more.

 

Input Multiple Files from a Folder

In Excel
In Alteryx

In Excel if you have a folder that has multiple files of the same type (e.g. .CSV), you need to select each one of these and they will open, one at a time in separate worksheets. 

Excel CSV

 

DB File Input  To do this in Alteryx, drag an Input Tool onto the canvas, locate the folder and insert a wildcard (*). In this case we are bringing in two .CSV files.

Using the Input Tool locate the directory and file you are looking for. In this case it is POS_Data.

Alteryx CSV Input

Then once I bring in that file POS_Data, we can go to the configuration section and edit the directory path by adding a ( _*.csv). This will bring in all of the .CSV files in the same directory and union them together as long as they are the same table structure and format.

Alteryx CSV Input 2

 

 

 

Clean

Rename Columns

In Excel
In Alteryx

Renaming Columns can be quick and easy in Excel. You just have to click on the column cell and change the name.

Renaming Columns in Excel

Alteryx Select Tool  It’s also easy to do in Alteryx. Using a Select Tool, you simply rename the field whatever you want.

Renaming Columns in Alteryx

 

Change Data Types

In Excel
In Alteryx

If you want to force Excel to consider numbers as a string, you can precede the numbers with an apostrophe ('), or you have to select the cells and/or columns, go in the "Format Cells" menu to view and set other types of information, such as currencies, dates, etc. 

Excel Change Data Type

Alteryx Select Tool  In Alteryx, the Select Tool allows you to change the data type to whatever format you want. 
 

Alteryx Change Data Type

Autofield Tool  In addition to the Select tool, you can utilize the Auto Field Tool which, as the name suggests, is able to interpret the data and assign the type and size appropriate for the content. This is especially useful for fields that contain text strings.

Date Time field  Finally, there are some conversions which are a little more complex. Dates can be awkward, because the Select tool may not be able to understand your preferred date format. To satisfy this, there is a specific DateTime Tool.

 

Removing Columns

In Excel
In Alteryx

To remove a column used in a calculated field, you cannot delete the original without having to copy and paste values, otherwise your formula will break.
 

Removing Columns in Excel

 

Alteryx Select Tool  In Alteryx this is not a problem. Just use the Select Tool and untick the box next to anything you don’t need or want to hide, and it will be removed from your workflow, without affecting formulas.

Removing Columns in Alteryx

 

Removing Rows

In Excel
In Alteryx

This is either a manual selection in Excel, or you need to use a quick filter to find what you don’t need.

Excel Remove Rows

 

Filter Tool  If you can list the criteria, you can use the Filter Tool. If it’s complicated, you can use a formula to build IF expressions or use the switch () function. 

Removing Rows in Alteryx

 

Create New Columns

In Excel
In Alteryx

The most common way of creating new fields in Excel is to insert a new column, name the column and write in a formula if necessary.

Create new Column Excel

 

Formula Tool  In Alteryx, we do not have to insert the column into our table, we can just add a new output field to our workflow using the Formula Tool. Simply type in the new field (Value Targets in this case) and your new field is calculated on every row.

Create New Column Alteryx

Tip 1: If you need to concatenate with first name and last name together, use a formula with the + symbol. Most other text‐based formulas use the same name and syntax, for instance Left () or Right (). The Alteryx manual (press F1) will help you identify those which don’t.

Text to Columns  Tip 2: If you’re splitting a field with a regular format (for instance, maybe splitting a comma separated address field), use the Text To Columns Tool.

 

 

Formulas/Calculations

IF Statements

In Excel
In Alteryx

Everybody uses IF statements in Excel. It is one the most popular decision making functions in the platform. To create a new Sales Potential column that buckets scores as High, Medium or Low, you utilize this IF statement in Excel:

=IF(V2>2500, "High", IF(V2>1499, "Medium", "Low"))

Excel IF Statement

Formula Tool   The Formula Tool is a powerful processor of data and formulas. Use it to add a field to an input table, to create new data fields based on an expression or data relationship, or to update an existing field based on these same premises. To create the same IF statement in Alteryx, simply create a new output field called Sales Potential, and using the Total Sales field, input the following Expression:

IF [Total Sales] > 2500 THEN "High" ELSEIF [Total Sales] > 1499 && [Total Sales] < 2501 THEN "Medium" ELSE "Low" ENDIF 

Alteryx IF Statements

 

Auto Sum

In Excel
In Alteryx

Creating an Auto Sum in Excel requires that you select the related fields and rows and then click the Auto Sum symbol. Or if you need to you can write a =SUMformula.

Excel Autosum

Summarize Tool  The Summarize Tool can do a host of summary processes, including: grouping, summing, count, spatial object processing, string concatenation, and much more. In this example the Summarize tool is used to group transactions by customer and to total up the transactions to see the customer’s total spend.

Alteryx Autosum

 

 

Calculate Cumulative Sum of Rows

In Excel
In Alteryx

To create the cumulative sum of rows in Excel, you must create a new column, then set the cumulative range. In this case the starting point is =S2, and then you add =S2 + R3 for the second row. Once this is established, we can copy the formula for the rest of the related information.

Excel Cumulative Sum

Multirow Tool  The Multi-Row Formula Tool takes the concept of the Formula Tool a step further, allowing the user to utilize row data as part of the formula creation. This tool is useful for parsing complex data, and creating running totals, averages, percentages and other mathematical calculations. In this case, we create a new field, and then create an expression.

Multirow Alteryx

 

Calculate Percentages of a Total

In Excel
In Alteryx

Calculating percentages on each field based on a total in Excel, may require you to first build a table of your data, then build a pivot table based on that data. The other option is to write an SUMIF() statement to calculate the percentages for each Month and Amount. Below we highlight the Pivot Table example. 
 

Excel Calculate Percentage

Excel Calculate Percentage 2

Multifield Tool Alteryx The Multi Field Formula Tool makes it easy to execute a single function on multiple fields. This example builds new columns/fields in the data that display a percentage of each month. 

Alteryx Calculating Percentages 1

Alteryx Calculating Percentages 2

 

 

Transform

Filter

In Excel
In Alteryx

Doing a filter in Excel is easy to do. You simply select the worksheet or columns you want to filter and then select the filter drop down.
 

Excel Filtering

Filter Tool  The Filter Tool in Alteryx lets you do a basic filter to split data into two streams: True (data that satisfies the expression) or False (data that does not).

Alteryx Filter

 

Sort

In Excel
In Alteryx

Sorting by multiple columns in Excel is easy. Just highlight the worksheet and do a sort or custom sort.
 

Sorting in Excel

Sort Tool  The Sort Tool in Alteryx makes it just as easy, allowing you to organize your data just the way you need it. Just select the name of the column and choose between ascending or descending.

Sorting in Alteryx

 

Pivot Data

In Excel
In Alteryx

Pivoting data in Excel (such as transposing data or creating a cross tab) will typically require that you build a Pivot Table to get the data in the shape you want.

Pivot Data in Excel

Transpose Tool  The Transpose Tool allows you to pivot the orientation of the data table. It transforms the data so you may view horizontal data fields on a vertical axis. This tool is useful for extracting non-conforming data. There is no limit to the amount of records or fields that can be transposed. 

Transposing in Alteryx

Crosstab Tool Alteryx   The Cross Tab Tool pivots the orientation of the data table. It transforms the data so vertical data fields can be viewed on a horizontal axis summarizing data where specified. 

Alteryx Cross Tab

 

 

Reorder Fields

In Excel
In Alteryx

Reordering columns or fields in Excel requires that you highlight the columns, and then use the Shift key to drag and drop the columns to the space you want. 

Excel Reorder Fields

 

Alteryx Select Tool  If you need to reorder your columns in Alteryx, then you can do so using the Select Tool or you can do the same in the Formula Tool.

Reordering in Alteryx

 

Join

Append Data

In Excel
In Alteryx

With Excel, you must ensure that the columns of the two tables are exactly the same and in the same order. Then you copy the entire contents of the second table, and paste it into the first empty row of the first table.

Append Row Excel

Union Tool Alteryx  There is a much easier way to do this in Alteryx. The Union Tool and it allows you to combine multiple worksheets based on the field names while maintaining the position of each column. You can easily change the order of the column headers in each worksheet so they match up.

Append Row Alteryx

 

Blend Data - VLOOKUPS

In Excel
In Alteryx

With Excel, in order to join multiple worksheets, you can use the VLOOKUP formula or INDEX MATCH formula to bring in information to the data sets from a different source(s) or table(s). For those unaware of INDEX MATCH, this is used instead of a VLOOKUP formula if the lookup value is not in the left most column, and thus we cannot use the VLOOKUP formula. 

Vlookup in Excel

Join Tool Alteryx  If you need multiple matches to have their own rows, then a join would be better. You can input two data sets and join them using either the Join Tool, or if you’re connecting to a compatible data source, you can actually join them in the Input tool. 

With the Join tool you join two tables with a common field (primary key) to create a:
 

  • Inner Join (J): create new columns to the right of the source table (L) in which there are the values of the table to join (R) that satisfy the collation.
  • Left Join (L): are the records of the original table (L) that have not found a match in the table to join (R)
  • Right Join: (R): are the records of the table to be joined (R) that have not found a match in the source table (L).

Join in Alteryx

 

Lock Join Tool Alteryx  You can also utilize the In-DB Join Tool if you are working with data in a database. The Alteryx In-DB tools enables blending and analysis against large sets of data without moving the data out of a database and can provide significant performance improvements over traditional analysis methods. 

The In-DB Join Tool lets you do inner, left, right and even full joins. 
 

  • Inner Join (J): create new columns to the right of the source table (L) in which there are the values of the table to join (R) that satisfy the collation.
  • Left Join (L): are the records of the original table (L) that have not found a match in the table to join (R)
  • Right Join: (R): are the records of the table to be joined (R) that have not found a match in the source table (L).
  • Full Outer Join: are all records contained in both the (L) and (R) tables


You can learn more about the Alteryx In-database tools here.

 

 

Aggregate

Grouping Rows and Aggregating

In Excel
In Alteryx

If you want to summarize data in Excel you build a pivot table.

Aggregate in Excel

Summarize Tool   The Summarize Tool in Alteryx works just like a pivot table in Excel. You can group data, then sum, count, or perform other calculations on other fields.

Aggregate in Alteryx

 

If you haven't already downloaded Alteryx, you can get a free trial at www.alteryx.com/trial

To learn more or get help with other features in Alteryx from other Alteryx users, please visit the Alteryx Community.

We would like to extend a special thanks to our partner the Information Lab for their contribution on this informative information. To learn more about the Information Lab, you can visit their website at http://www.theinformationlab.co.uk/