This tutorial explains how to create a custom sort order in Excel with two common examples.
Numeric values naturally have an inherent order that can be specified as ascending or descending. Text values are typically sorted in A-Z or Z-A order. However, there are instances when we need to establish a specific order for our text values.
Let’s look at how to create a custom sort in Excel for text values.
Download the practice file to follow along.
When using formulas in tabular data in Excel, you will need to copy a formula down the entire column, so that each row has that same formula.
There are a few techniques to copying formulas in Excel, and this article will detail what you should be aware of, and why you would apply each specific technique.
Use the following links to jump to a specific section in the article.
Download the sample file to practise.
[Read more…] about How to Copy a Formula Down in ExcelA frustration with PivotTables in Excel is the automatic resizing of column widths that occurs when a PivotTable updates. This often happens when a PivotTable is refreshed or filtered.
Let’s look at how to stop PivotTable columns resizing for specific PivotTables, and how to change the default behaviour and stop the PivotTable column widths changing in future Excel workbooks.
In this example, we have a simple PivotTable with product sales and a Slicer that enables us to filter the PivotTable by product category.
In the following image, the PivotTable is filtered to show products in the ‘Food’ category only.
When the filter is changed to show products in the ‘Cakes & Pastries’ category, the first column expands to accommodate the longer product names such as ‘Chocolate Chip Muffin’.
This is due to a PivotTable option that forces columns to autofit on update.
The rich data types make it easy to get a live currency exchange rate in Excel. You can then convert currencies with an Excel formula that updates when the exchange rate changes.
This tutorial will return non-adjacent columns with the FILTER function. Dynamic array functions such as FILTER and SORT can only return consecutive columns natively. However, there are various methods to specify the columns to return from the array. Functions such as CHOOSECOLS, CHOOSE and INDEX are wonderful for this task.
You can download the Excel workbook to follow along
