Contextures News 20190507

Excel Last Number Formula

May 7, 2019

Get last number in column, interactive dashboards, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Last Number Formula

I'm updating my Excel Weight Tracker files, which were created long before Excel introduced named tables. Switching to tables has made it easier to show a summary on the dashboard. The old dynamic ranges aren't needed now - we can just refer to table columns instead.

The WeightData table, shown below, has columns where you enter the date and your weight every week. What formula would you use to show the latest weight on the Dashboard sheet? My formula is below the screen shot.

The new dates and weights are entered at the end of the table, so I used the LOOKUP function to get the last number from the Weight column.

=LOOKUP(9.99999999999999E+307, WeightData[Wt])

That strange number, 9.99999999999999E+307, is the largest number that can be typed into an Excel cell. The LOOKUP function won't find that in the weight column, so it returns the last number it finds.

To see this formula, and all the others, get one of the Weight Tracker files - either the Pounds/Kilos version, or the Stone version.

Date Grouping

When you create an AutoFilter or Pivot Table, Excel likes to "help" you, by grouping the dates into years and months. If you'd prefer to see individual dates, follow these steps to change your Excel options.

• On the Ribbon, click the File tab, then click Options
• For AutoFilters (Workbook-level setting):
  • Click the Advanced category, then scroll down to "Display Options for This Workbook"
  • Remove the check mark from "Group Dates in the AutoFilter menu". (Watch video)
• For Pivot Tables (Excel-level setting):
  • Click the Data category, and in "Data options", add a check mark to "Disable automatic grouping of Date/Time columns in PivotTables"
  • NOTE: If you don't have that option, you can manually ungroup the dates.
• Click OK to apply the new settings.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

History - Excel was launched on September 30, 1985. Earlier that year (May 2nd), Bill Gates unveiled Excel at a conference, and you can see the details in InfoWorld (pages 28-33). Wow, 50% of Lotus users created macros! What's your guess on the Excel %? (Level - All)

Charts - There's a new visualization type (Key Influencers) in Power BI, and Chandoo shows how to create the same thing with basic Excel tools. Nice result, and cool tricks for setting it up. (Level - Int/Adv)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

The Perfect Gift

To celebrate our anniversary last week, we got the perfect gift for each other - matching USB hubs. (I know, so romantic!) From our decades of experience, we know that making your work life a little easier can also make you happier after work!

P.S. Don't make my mistake, and accidentally buy a laptop with only 2 USB ports.

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190507ctx.html
I'll also post any article updates or corrections there.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
[email protected] gmail.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.