Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Maveryx Community Resources

Get up-and-running on the Community and with Alteryx in record time.

Alteryx & Excel Guide

MeganDibble
Alteryx Community Team
Alteryx Community Team
Created

 

Banner (3).png

 

 

Heading 1 (3).png

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.

 

Screen Shot 2022-08-31 at 10.34.46 AM.png

 

Additionally, there is The Alteryx for Excel Users Product Guide, which shows everyday Excel tasks alongside how to accomplish them in Alteryx.

 

MeganDibble_3-1661897873681.png

 

Heading 2 (1).png

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).

 

MeganDibble_5-1661897873717.png

 

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.  

 

Heading 3 (1).png

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:

 

After reading through the series, you will be able to create an Excel output from Alteryx that looks like this:

 

atcodedog05_2-1660071146009.png

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

 

MeganDibble_9-1661897873809.png

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.

 

NicoleJ_5-1658158593122.png

The workflow and output file from the blob blog

 

Heading 4 (1).png

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.

 

MeganDibble_11-1661897873827.png

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:

 

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

 

MeganDibble_12-1661897873839.png

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.

 

Heading 5 (1).png

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.)

 

MeganDibble_14-1661897873850.png

The Output Data tool option solution

 

Heading 6 (1).png

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.

 

Screen Shot 2022-08-31 at 10.26.17 AM.png

 

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:

 

Errors when outputting data:

 


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.

 

Comments
Qiu
20 - Arcturus
20 - Arcturus

@MeganDibble 
This is amazing.

Very much finalized and I have bookmarked it for tuture reference.

atcodedog05
22 - Nova
22 - Nova

Hi @MeganDibble 

 

This is a very well-curated one-stop helpful guide 🙂

I will definitely be referring to this and recommending it to a lot of people 😀

alberto_herni
9 - Comet

Excellent post!

IraWatt
17 - Castor
17 - Castor

Great article, will start recommending it on community 👍

allwynthomas24
11 - Bolide

This is truly an amazing reference for creating Excel based workflows. I have added the link to my learning resources. Thanks a lot @MeganDibble for drafting such a good extract. 

T_Willins
14 - Magnetar
14 - Magnetar

Thank you @MeganDibble for drafting this!

smoskowitz
12 - Quasar

@MeganDibble --

 

This is wonderful! So much valuable information in one place! This is now bookmarked!

 

Thanks,

Seth

DawnDuong
13 - Pulsar
13 - Pulsar

Thanks @MeganDibble  We should make this a "sticker" post in the Designer forum to reduce the number of repeated "favourite" questions.

mstoler
8 - Asteroid

Hello,

Is the above available as a pdf file, the Alteryx + Excel Guide?

 

Thank You,

 

Michael

Bluebird_Tim
7 - Meteor

@MeganDibble  can you update the guide to add a section for thousand separator?