Today, I created yet another report in Excel. As usual, I had used the Format as a Table command and then filtered out some data. The next step was to count the number of rows that were still visible. I used the formula =SUBTOTAL(3,A2:A144) and had the required information. This website has a good explanation of the process, along with how to count rows that have been manually hidden (instead of filtered).
Category: Office
Compare two columns for missing values in Excel
At work this morning, I had to make a list of managed computers with 32-bit operating systems which were not checking into a console. In this article from ExtendOffice, they walked through the process of comparing two columns for duplicate data by using a formula in the third column.
Once I had the formula 1 in place, I formatted the data as a table. Clicking the third column’s header provided the option to uncheck “Select All”, then scroll down and select (Blanks). The resulting view showed the items missing from my list.
Change font case in Excel
I needed to change the format of some text in Excel 2016 today. Excel doesn’t have the same font controls as Word, so it’s not as intuitive as one would expect.
The process in Excel is to use formulas (in my case, it was UPPER) to create the text you wish to see in the world, then paste those values into their final destination.
Thanks to Andre at GroovyPost for the tutorial!
Making a drop-down list in Excel
If you’re creating a form in Excel, a drop-down list can be super helpful. This page has a great walkthrough for doing just that.