This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
on 08-31-2022 01:12 PM - edited 3 weeks ago by tduvall
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.
Let’s get your Excel spreadsheets into Alteryx!
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.
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.
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 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.
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:
The blog article How to Generate and Format Totals and Subtotals walks through the following scenarios:
Inserting dynamic subtotals based on the values of a particular column
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
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.
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.
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.
The two discussion forum posts below cover simple examples of concatenation with Alteryx using the Formula tool:
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.
Let’s start with the most basic Excel output task and work up to more complex scenarios.
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:
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.
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
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:
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.
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 😀
Great article, will start recommending it on community 👍
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.
This is wonderful! So much valuable information in one place! This is now bookmarked!
Thanks @MeganDibble We should make this a "sticker" post in the Designer forum to reduce the number of repeated "favourite" questions.
Is the above available as a pdf file, the Alteryx + Excel Guide?