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