Alteryx Community Resources
Get up-and-running on the Community and with Alteryx in record time.Alteryx & Excel Guide
- 1.Getting Started
- 2. Inputting Excel Data
- Input a Single Excel Sheet
- Input Multiple Excel Sheets
- 3. Formatting Like Excel
- Pivot Tables
- Merging Cells
- Total Rows
- Formatting Sheets
- 4. Replicating Common Excel Formulas
- VLOOKUPs
- IF Statements
- Concatenate
- 5. Outputting Excel Data
- Output a Single Excel Sheet
- Output Multiple Excel Sheets
- 6. Troubleshooting
1. Getting Started
If you are an experienced Excel user but are new to Alteryx, a great place to get started is the Alteryx for Excel Users Interactive Lessons in the Academy section of Community. This page contains bite-sized lessons to help you translate your Excel skills to Alteryx, with topics like VLookUps with Designer and Summarizing Data.
Additionally, there is The Alteryx for Excel Users Product Guide, which shows everyday Excel tasks alongside how to accomplish them in Alteryx.
2. Inputting Excel Data
Let’s get your Excel spreadsheets into Alteryx!
Input a Single Excel Sheet
The most basic task is to import one Excel sheet into Alteryx. You can do this using the Input Data tool—check out the Help Documentation for the tool to see all its functionality. The Input Data tool also lets you import data from a named range in your workbook or import a list of sheet names.
Input Multiple Excel Sheets
What if you have many sheets you want to import? Then you can use the macro on the Gallery. If you would like a detailed walkthrough on how to use this macro, there is a blog series on the macro that you can reference (part 1 and part 2).
This knowledge base article demonstrates other ways to import multiple excel sheets—using the Dynamic Input tool for importing multiple sheets and a Batch Macro for importing multiple sheets from different workbooks. If the Excel sheets you want to import have different schemas for different tabs or files, then you may need to use different methods or multiple macros to get the data into Alteryx.
3. Formatting Like Excel
When you are used to Excel features and default formats, it can be challenging at first to replicate what you need to have in Alteryx. Once you start learning and practicing, you will see that Alteryx can handle all your Excel tasks and much more!
Pivot Tables
Pivot tables are an Excel staple, and it may not be immediately clear how to replicate the functionality in Alteryx.
To create a simple pivot table, check out the suggestions in this discussion post. The Summarize tool works well for aggregating data into a summary table. For a list of all the actions the tool can perform, refer to the help documentation here.
Merging Cells
If you need to create outputs where cells are merged in Excel, then you are in luck! There is a three-part series on the Engine Works blog which goes through the process of creating merged headers and columns step-by-step:
- How to Create Merged Cells in Reports, Part 1
- How to Create Merged Cells in Reports, Part 2
- How to Create Merged Cells in Reports, Part 3
After reading through the series, you will be able to create an Excel output from Alteryx that looks like this:
Total Rows
The blog article How to Generate and Format Totals and Subtotals walks through the following scenarios:
- Inserting a total column
- Inserting a total row
- Inserting a subtotal for a hard-coded subcategory
- Inserting dynamic subtotals based on the values of a particular column
- Automatically generating a running total grouped by rows
Inserting dynamic subtotals based on the values of a particular column
Formatting Sheets
If you have a use case where multiple tables are needed in the same excel sheet, the Table tool can help you accomplish this. To see how to format the tables, check out this solution: Multiple Tables in one Excel sheet (mixed layouts).
The blog post One File, Three Sheets, Three Different Formats walks through three different desired Excel sheet formats—how to get to that format in Alteryx and then how to output them in one file. This workflow utilizes the Table and Layout tools to bring the sheets together.
For a more advanced use case, check out this blog post: The Blob: Reporting That Isn’t From a Horror Film. It will take you through how to output data to a heavily formatted Excel template.
The workflow and output file from the blob blog
4. Replicating Common Excel Formulas
Alteryx is a powerful tool that can be used to accomplish the same tasks that Excel can. However, as you are learning, it is not always obvious which tool to use in Alteryx to replicate the functionality of your Excel workbook. So here are some common Excel functions and their Alteryx counterparts.
VLOOKUPs
The VLOOKUP formula is a popular one in Excel. So how can you do a VLOOKUP in Alteryx? By using the Find Replace or the Join tool! Here’s a knowledge base article for you with the details: Alteryx for Excel Users: How to do a VLOOKUP in Alteryx.
The VLOOKUP function replicated in Alteryx in the above knowledge base article
Understanding VLOOKUPs as database joins will help accelerate your Alteryx learning. Check out the Joining Data interactive lesson to deepen your knowledge of joining data.
IF Statements
There are many kinds of IF statements, so here is a list of popular discussion threads on IF statements. They all utilize the Formula tool:
- Multi-Condition IF THEN ELSE statement
- Writing an If statement with multiple OR
- IF Contains
- How to use multiple IF statements with multiple AND statements
- Nested If Then Else Statement
If you didn’t find your specific IF function question answered above, take a look at the Writing Conditional Statements interactive lesson.
Concatenate
The two discussion forum posts below cover simple examples of concatenation with Alteryx using the Formula tool:
- Concatenate - using Formula tool to concatenate values from different fields
- Concatenate - contains a workflow demonstrating how to concatenate using the Formula tool or the Summarize tool
- Concatenating Text - towards the end, this shows an example of more advanced concatenation methods
A proposed solution in the Concatenating Text discussion post
Complex concatenation tasks may require using the Transpose, Cross Tab, and/or Summarize tool. If you have lots of columns to concatenate, a Transpose and Summarize tool together works well, as shown in this example. Finally, here is an interactive lesson on Changing Data Layouts that is useful for learning these tools.
5. Outputting Excel Data
Let’s start with the most basic Excel output task and work up to more complex scenarios.
Output a Single Excel Sheet
To output your data to a single Excel sheet, you can use the Output Data tool. The output data tool allows for the following Excel file formats:
- Microsoft Excel Binary, .xlsb
- Microsoft Excel 1997-2003, .xls
- Microsoft Excel, .xlsx
- Microsoft Excel Macro-Enabled, .xlsm
Some of these file formats require you to install drivers. You can find the list of file formats and needed drivers here.
Depending on your chosen format, you will have different options in the Output tool configuration. Here is a comprehensive list of the output options and their meaning.
Output Multiple Excel Sheets
You can review the knowledge base article Output to multiple sheets within an Excel file to learn how to configure the Output Data tool for multiple sheets. In this example, a field in the dataset was used to create the different sheets.
The discussion post solution to How to output multiple tabs within a single Excel file based on a field name covers how to use the formula tool to create a file path column which is used in the Output Data tool options to output multiple sheets to the same workbook. (The same method could also be used to output to different workbooks.)
The Output Data tool option solution
6. Troubleshooting
If you ever get stuck when you are in Designer, don’t forget to use the search bar! You can even search Excel terms, like VLOOKUP, and it will show related tools and Community search results.
If you are getting an error when working with Excel files in Alteryx, take a look at the below posts and solutions to help you troubleshoot.
Errors when inputting data:
- Error: "has a different schema than the 1st file in the set and will be skipped"
- How to check for encoding or formatting issues with Excel worksheets
- Troubleshooting: Alteryx Designer Misinterprets Dates from Excel File
Errors when outputting data:
- Error in the output tool: "'Unable to open file for write' when writing Excel file to Shared Drive"
- Output to Excel error: "We found a problem with some content..." Do you want us to recover as much as we can?...."
- Error: "Unable to overwrite a cell that contains a formula" when Overwriting Values in Excel
Do you have a discussion forum thread, blog, or other content on Community that has helped you when working with Alteryx and Excel? Drop it in the comments below.
