• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Highlight Multiple Periods in Line Chart|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / Highlight Multiple Periods in Line Chart|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsHighlight Multiple Periods in Line …
sp_PrintTopic sp_TopicIcon
Highlight Multiple Periods in Line Chart
Avatar
Tom Borg
Member
Members
Level 0
Forum Posts: 11
Member Since:
July 15, 2020
sp_UserOfflineSmall Offline
1
March 25, 2021 - 5:16 am
sp_Permalink sp_Print

I have a simple line graph and have highlighted the period which contains the highest value.  I used the formula, "=IF(MAX($B$5:$B$24)=B5,B5,NA())" to identify the maximum point, and the formula, "=ISNUMBER(C5)" to identify the period to shade. 

How can I rewrite the 1st formula to identify the 3 highest numbers in column B, so that the shade helper column will then include all three periods?

Thank you.

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 25, 2021 - 12:25 pm
sp_Permalink sp_Print

Hi Tom,

You can use this formula in cell C5 then copy down (if you're not on 365 you need to enter the formula with CTRL+SHIFT+ENTER):

=IF(MATCH(B5,LARGE($B$5:$B$24,{1;2;3}),0),B5,NA())

Mynda
sp_AnswersTopicAnswer
Answers Post
Avatar
Tom Borg
Member
Members
Level 0
Forum Posts: 11
Member Since:
July 15, 2020
sp_UserOfflineSmall Offline
3
March 26, 2021 - 2:24 am
sp_Permalink sp_Print

Thank you Mynda, that worked perfectly!

Avatar
Tom Borg
Member
Members
Level 0
Forum Posts: 11
Member Since:
July 15, 2020
sp_UserOfflineSmall Offline
4
March 26, 2021 - 2:36 am
sp_Permalink sp_Print

A quick follow-up question: Regarding the shaded periods, is there a way to have the shading go below the x-axis so that it includes the time period label?

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
March 26, 2021 - 6:28 pm
sp_Permalink sp_Print

No! Sorry. You cannot format the axis labels area.

Mynda

Avatar
Microsoft Excel Recalc Or Die

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 24, 2019
sp_UserOfflineSmall Offline
6
March 28, 2021 - 4:01 pm
sp_Permalink sp_Print

Hi Mynda and Tom,

This would be your workaround ( I am attaching the file also)

explanation.pngImage Enlarger

 

Basically, you have to add two columns, one for positioning the label and another one with blanks (double quotes) to just show that. So in the label it doesn´t show anything. Once the labels are put, at first you will see a 0 (cero) go to the label options and switch it off (value) but first, activate the value from cells option and then select the dummy column with blanks. Then, you resize the label area (or label box) with height: 0.66 and 0.81 width and lastly, apply the same color of the bars.

Hope it works for you.

sp_PlupAttachments Attachments
  • sp_PlupImage explanation.png (281 KB)
  • sp_PlupImage 2-1.png (193 KB)

The following users say thank you to Microsoft Excel Recalc Or Die for this useful post:

Mynda Treacy
Avatar
Microsoft Excel Recalc Or Die

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 24, 2019
sp_UserOfflineSmall Offline
7
March 31, 2021 - 3:31 am
sp_Permalink sp_Print sp_EditHistory

Hi Tom,

My workaround above is a bit "static", case in the future you need it to be more dynamic, scatter plots is the way to go.
with this type of chart, there are many possibilities for "hacking" it´s options. Below it´s something I recently developed where the requirement was a bit similar to yours.  I will attach the file case you want to review it.

Image Enlarger

         

 

Regards,

At Mynda, I got this message: "015_highlighting_axis_item.xlsx: Error 106 - Upload file size exceeds maximum allowed size"

Would it be ok to share an external link for downloading the file ?

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4443
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
March 31, 2021 - 3:00 pm
sp_Permalink sp_Print

Sure, happy for you to share an external link. Nice solution!

Avatar
Microsoft Excel Recalc Or Die

Active Member
Members
Level 0
Forum Posts: 5
Member Since:
May 24, 2019
sp_UserOfflineSmall Offline
9
April 5, 2021 - 6:41 am
sp_Permalink sp_Print

Hi Mynda and everyone,

Sorry for the delay, here it´s the link to download the workbook: https://1drv.ms/x/s!Am3AC3QcIM.....A?e=SfUFZs

related to the Gif image above.  Note: this workbook was done using Excel´s new functions: # dynamic arrays #.  It can be done using Excel tables and cell ranges as well.

Take care everyone.

The following users say thank you to Microsoft Excel Recalc Or Die for this useful post:

Mynda Treacy
Avatar
Tom Borg
Member
Members
Level 0
Forum Posts: 11
Member Since:
July 15, 2020
sp_UserOfflineSmall Offline
10
April 9, 2021 - 2:47 am
sp_Permalink sp_Print

Another follow-up:  When I have the columns highlighted, and combine it with a line graph, it works great.  But if I want to have one of the lines to be shown as a column chart, any columns in the shaded area are behind the shading so they are not visible and I cannot get them to display on top of the shading.  Mynda, do you have a solution for that?

Thanks,

Tom

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27211

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.