Inside this article
Pivot Tables
SUMIF() and COUNTIF()
VLOOKUPs meet Lookups
Beyond using Excel as a calculator, more advanced usage generally results in creating data models. This lets you connect information across your tables and use it to help you make decisions or present information. Let's explore how Coda can help you build effective, flexible, integrated, and visually appealing data models.
Pivot Tables
Many data models begin with a pivot table. In Coda, you can create a similar experience using a few different features:
- Grouping - This allows you to create nested bundles of your data so you can easily categorize and see patterns.
- Layouts - Depending on your intentions, you may want to view your data in a Card or Gantt layout - these allow you to visualize your data differently to meet specific workflow needs.
- Views - The sky is the limit when it comes to views. When you create a view, you can filter it however you like to answer specific questions about your data. Whatever updates you make in views or their corresponding tables automatically populate the other location.
Unlike pivot tables, you can see all of the underlying data and use the Summarize menu for quick calculations in each of these features as well.
SUMIF and COUNTIF
In Excel, these functions are a core part of how you interact with your data. In this case, the function and what you are looking for are combined. This can make the formulas you build a little convoluted. To be clear, both of these functions are still available in Coda, but we've also introduced the Filter formula which lets you be clearer in your queries and open up some possibilities.
In Coda, you'll follow this pattern:
[TABLE NAME]. Filter(what you are interested in). What you want to do with it
In other words:
Table → Filter → Action
The what you want to do with it can be Sum( ), Count( ), Average( ), BulletedList( ), and much, much more.
So, what do you get with this updated syntax?
- More readable formulas
- Easier to parse your formula requests
- Reads just like your table
VLOOKUPS meet Lookups
VLOOKUP
There are a few different ways to accomplish a VLOOKUP type experience in Coda. To help you find your way, we've highlighted the anatomy of a VLOOKUP below:
It's important to remember that, in Coda, your lookups don't need to be vertical or to the right as with VLOOKUP in Excel. They'll always pull from your display column.
- @Reference - You'll use this if you want to call out a specific row or person in the context of your doc.
- Lookup Columns - You'll use this to pull data from one table into another.
- Lookup Formula - You'll use this if you want to perform a lookup query anywhere in your doc.
- Filters - You'll use this to slice and dice your data to answer the questions you have.
HLOOKUP
HLOOKUPs are pretty much the same as VLOOKUPs except that they work on the horizontal access. You can use the exact same logic that you applied in VLOOKUP above.
- @References - You'll use this if you want to call out a specific row or person in the context of your doc
- Lookup Columns - You'll use this to pull data from one table into another
- Lookup Formula - You'll use this if you want to perform a lookup query anywhere in your doc.
- Filters - You'll use this to slice and dice your data to answer the questions you have.
Index Match
Index Match lets you look up based on rows and columns which makes it much more similar to the logic behind Coda. Coda has the improvements that Index Match brings to Excel already baked into it. Take a look at the anatomy below:
- @References - You'll use this if you want to call out a specific row or person in the context of your doc.
- Lookup Columns - You'll use this to pull data from one table into another.
- Lookup Formula - You'll use this if you want to perform a lookup query anywhere in your doc.
- Filters - You'll use this to slice and dice your data to answer the questions you have.