Excel tips and tutorials
Pivot table filtering was improved in Excel 2010, when Slicers were introduced. Instead of using the drop down lists in the pivot table headings, you can click on a Slicer, to quickly filter the pivot table. You couldn’t use Slicers to filter a table in Excel 2010 though.
Continue reading “Use Slicers to Filter a Table in Excel 2010”
There is a new sample file on my website, in response to a lookup question that someone asked on my Contextures Facebook page. The sample file shows how to get mileage from Excel lookup table, when you pick two cities.
This workbook shows how to calculate annual costs and savings in Excel. It’s amazing how all those little expenses can add up over a year.
Continue reading “Calculate Annual Costs and Savings in Excel”
A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site. This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.
If you want to insert a block of cells on a worksheet, what method do you use most often? Do you insert rows with Excel fill handle, or with a command?
One of the new features in Excel 2013 is that each file opens in a separate window. Having each file in its own window makes it easier to compare files side-by-side, and most of the time I like the separate windows.
It’s been a bad week for Excel, with big news stories about spreadsheet errors. Two Harvard professors were in the news after a student at the University of Massachusetts found errors in their economic research paper.
Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.
Continue reading “Click to Move Excel List Items Up or Down”
If you’re using a pivot table, there are built in features that lets you show a running total, or a percent running total. Here’s the command to show a % Running Total in a pivot table.
Continue reading “Create a Running Total in an Excel Column”
With Excel’s conditional formatting, you can highlight cells based on specific rules. There are some built-in rules available, and you can use formulas to create your own formatting rules.
To see the steps for setting up the conditional formatting, watch this short video. The written steps are below the video.
To download the sample file, go to my Contextures website: Highlight Duplicate Records in a List
In this example, we want highlight duplicate records in a table. There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.
So, we’ll create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.
In the sample data, there are two identical rows, and these should be highlighted after we apply our conditional formatting.
The first step is to use the CONCATENATE function to combine all the data into one cell in each row.
Add a new heading in cell G1 – AllData – and in cell G2, enter this formula, to combine the data from all the cells in that row.
=CONCATENATE(A2,B2,C2,D2,E2,F2)
Next, copy the formula down to the last row of data.
Then, a conditional formatting rule is set, to color the rows that are duplicate records. We’ll use the COUNTIF function to check for duplicates in the AllData column.
=COUNTIF($G$2:$G$8,$G2)>1
If there is more than one instance of a data combination, that indicates a duplicate row, and the cells in columns A:F will be coloured. The two rows with duplicate records are highlighted, so our conditional formatting formula worked!
For detailed instructions, and to download the sample file, go to my Contextures website: Highlight Duplicate Records in a List
_________________________
