# Categories, variables and formulas

Hover over a variable and click on the

**Categories**button to add a category breakdown to a variable.If the variable

*already has a category breakdown*, it will be coming from its connected data item/s (if the category exists in the data), or from its formula (if the formula refers to a variable that has the category).- You can remove the category breakdown by hitting the '-' next to the category after clicking on the
**Categories**button. However, if the category is coming from data, or from the formula, follow the steps below:- If your variable is connected to data, and the category is coming from data, you can either aggregate the category in the advanced data options on the variable, or you can remove the category from the model (from the left sidebar), which will remove the breakdown from all variables that had that category.
- If your variable is inheriting the category from its formula (e.g. if it refers to a categorical variable), you can aggregate the category away in the category modifier.

You can also create new variables when viewing a category in the bottom-panel of the spreadsheet. Click on the

**+**in the far-right column, and create a new categorical variable by choosing its format from the drop-down (e.g. Currency, %).When a variable is broken down by a category, and you set a formula at the top (aggregate) row, it will be

**automatically applied in the same way across all category items**. This is powerful as it means you only have to set the formula once (less duplication, less errors!).where the formula is set at the top row (aggregate) level, the category item rows inherit the formula

If however, you wish to set a different formula/value for certain category items, simply edit the formula for that particular row/cell. These edits will show up as an

**Override**, and you can easily reset the override (back to the default formula), by hitting "Reset override ↩️" or "Reset row ↩️".If you do not have a top (aggregate) row formula/value, then you have no "default", so different formulas/values for category item rows/cells are not considered

**Overrides**.- What if I have 2+ categories on a variable (e.g. Country and then Product), and then override for one of the Countries (e.g. New Zealand).
- All the Products under New Zealand will now take their formula from the override for New Zealand (not the top-level / aggregate row of the variable).

- How do I override an entire category item row?
- Either edit the formula in the formula column (which sets the formula for all months / time steps), or override a cell and then drag/fill-right that formula to all other cells in the row.

- Are different formulas/values in specific time step columns of the top-level / aggregate row of the variable considered overrides?
- No, this just serves as a different formula for that time step.

You can easily drag to reorder the category breakdown on a variable. For example, if you have a variable broken down by Department and then Region, you can swap the order around so it is Region and then Department instead. This will not change any calculations, it will just change the category ordering that you see when you expand the variable.

Category modifiers allow you to slice & dice variables within formulas, using the related

**Categories**and**Category items**. You can:- Group by linked categories
- Filter by category items
- Aggregate away categories

You can use your model's

**categories**and**category items**in**formulas**.- You can access the number of
**category items**by clicking on the three dots and selecting**Size**:

- You can also use categories in
**if-statements**(use this to dynamically define a variable's categorical values without requiring a formula for each category item)

You can also dynamically filter and sort categories in the bottom panel (after opening the category from the left-hand panel). You can sort either by clicking Sort/Filter, or from the column header directly. If you sort the category items, you have the option to save the order, in which case it is applied to any variables in the models that have that category applied.

You can also sort by category item value on a variable in the spreadsheet. For instance, if you have Spend broken down by Vendor, you can quickly sort the Spend variable so the breakdown is from biggest to smallest vendor.

Choose to sort based off the values for the

**Entire Model Period**, or just the**Last Actual Date**, and choose**Descending/Ascending**order (Descending is the default - biggest at the top!).Find this setting in the "More Options" menu on a variable, or ⌘/ctrl + 4. To remove the sort just hit the 'x' next to Entire Model Period / Last Actual Date.

Note that we use the

**Time Aggregation**setting to determine how values are summed up for the**Entire Model Period**. The default is**Sum**, but if you've set a**Time Aggregation**setting of**Final**, for example, we will sort using only use the Final value in the Entire Model Period.