Tag: microsoft excel

Did you know … you can use Microsoft Excel to count the number of records within a range?

I’ve been generating reports to track our Microsoft Teams adoption – how many people are using Teams, how many messages are being sent in Teams, how many Teams are there. Some of these metrics have easily visualized count-per-unit-time summaries available. Some, like the number of Teams, do not.

Team Created On
Directory Services 1/19/2017
App Proxy 1/19/2017
LDAP 1/19/2017
ADFS 1/19/2017
Nagios 1/19/2017
File Cluster 1/19/2017
Exchange Online 1/19/2017
Active Directory 1/19/2017
Commvault 1/19/2017

But it’s easy to turn a list of groups and creation dates into visualizable data. Paste the data into Excel. To find the number of items where “Created On” falls in a range, we need to be able to define that range. 01 January 2017 is easy enough, but how do you get the end of January? Excel has a function, EOMONTH, that returns the last day of a month.

Date is any date object. Offset is an integer number of months prior (negative numbers) or after (positive numbers) Date for which you want the last day of the month. I can list the dates to start and end quarters with =EOMonth(Date,2). With 01 January 2017 in cell D2, the last day of January is =EOMonth(D2,0)

 I don’t want to type01 Feb, Mar, April … flash fill and the fill handle need a few values before they can figure out the rest of a sequence. But I can use the last day of the month to get the first day of the next month – just add one! With 31 January 2017 in cell E2, I want =E2 + 1 in cell D3. (Yes, there are other ways to do this – probably dozens.)

Now that we’ve got a formula for the start and end of the month, just fill down to produce the ranges we need to see how many Teams were created each month. Then we just need a formula to do the counting for us. I use the COUNTIFS function.

=COUNTIFS($B$2:$B$1000,”>=”&D2,$B$2:$B$1000,”<=”&E2)

Counts the number of items in the range $B$2:$B$1000 (the cell range is static as the formula is copied elsewhere, hence the

Fill down – you’ll see the range remains static, and the comparison is to the D and E columns on the current row.

Voila – easily visualized data. And a graph 😊

Did you know … Excel can transpose data?

Have you ever gotten data where the column headers are along the left-hand side and each record is a column? I’m sure there are people who prefer each record to be a column too, and they probably hate my data where the headers run along the top and each row is a record. Did you know Excel can swap between these two formats?

Here I have a spreadsheet where each column is a record. Highlight and copy the records. Yes you can use ctrl-c to copy … but there’s no way to make a screen-print of my keyboard actions.

Click elsewhere – below the data you copied, on a new sheet, even a new spreadsheet. Right-click & expand “Paste Special” – find the icon for Transpose and click it.

Look – now each row is a record, just like I wanted. You can even delete the original data if you want – the pasted information will still be there.

Did you know … Excel can convert between units of measure?

Spend enough time reading temperature probe data, and you get to where you just know 23 is room temperature, and 82 is going to cook the CPU. And sure you can type “23 C in F” into Google and get the Fahrenheit equivalent, but that’s hardly efficient with a long list of values. You could look up the formula and have Excel perform the computation, too. But did you know Excel can convert between many units of measure without you finding the conversion formula?

Excel’s CONVERT() function allows you to display values in whatever unit is most familiar to you. Usage is convert(CellToConvert,OriginalUnits,DesiredUnits)

Voila – the values in your chosen unit.

If you use the units of measure in column headers, you can use the header cells as the current and desired unit of measure values — remember to use the $ anchors, otherwise copying your formula will not yield the right answer!

Did you know … you can use mini-charts to visualize Excel data?

Using charts and images, data visualization, clearly and efficiently communicates data. But when you’re trying to visualize statistics for several items, your chart can be anything but clear and hardly efficient to read. In this example, I’ve created a line chart depicting the monthly score for eight different people. While you can pick out obvious high or low performance, there’s not a whole lot of information being communicated here.

Did you know Excel can create mini-charts, known as “sparklines” to visualize individual statistics and compare statistics across items? Select the data that you want to compare. From the Insert ribbon bar, look for the “Sparklines” section. I am going to use a “line” style sparkline.

The data range will be selected. Enter the range where you want the mini-charts to display – this can be the row under your data or the column next to your data, or it can be some completely different location.

By default, the y-axis range for each mini-chart depends on the values of the data contained in the chart. This makes comparing the charts a little difficult – the scale is different. In the example below, scores in the 30’s don’t look different than scores in the 80’s.

Click on one of the mini-charts, and a “Design” tab will appear on the ribbon bar. Select it. Under “Axis”, change the minimum and maximum values to “Same for All Sparklines”.

Now you can see how individual performance varied as well as compare individuals.

Blank values will show up as broken lines in the mini-charts. If you do not want to display a gap, return to the “Design” ribbon bar and select “Edit data”. Select “Hidden & Empty Cells”

Select what you want instead of gaps – you can treat null values as zero or have a line drawn between the values on either side of the missing value.