• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting X axis to show first letter of each month

D

DashboardNovice

Member
In the attached file, I am using the chart with golden bars. I have tried to format the X axis to show only the first letter of each month. I did Format Axis, Number, Date and then I chose 'M' and clicked the Add button. Nothing happened.

I also tried entering MMMMM and nothing happened.

1. How do you make the first letter of the month appear?
2. How does the Add button work? Why is that necessary? Why can't you just type in the required format like you do with Custom Format under the Format Cells window?
 

Attachments

Hi ,

I agree that you want to do it with the existing values ; this can be done using named ranges so that you do not use any helper cells.

See this example file , which has 3 charts ; the first uses worksheet cells as static addresses for the X-axis , the second uses a named range , and the third uses a named range for extracting the first letters of the months , and then uses that for the X-axis.

Narayan
 

Attachments

NARAYANK991 said:
Hi ,

I agree that you want to do it with the existing values ; this can be done using named ranges so that you do not use any helper cells.

See this example file , which has 3 charts ; the first uses worksheet cells as static addresses for the X-axis , the second uses a named range , and the third uses a named range for extracting the first letters of the months , and then uses that for the X-axis.

Narayan
Click to expand...
I took a look at your file. In the name box, I can see that you assigned a named range called "X_axis."

For the 3rd chart, after clicking Edit under the horizontal axis labels, you have: =Example.xlsx!Modified_X_axis

1. When I use F3, then select Modified_X_axis, I get an error message. How do you get around this? I noticed that you have the workbook name in front of the named range. How did you get that to appear in there, or did you have to actually type it out?

2. Why does the named range Modified_X_axis not appear in the pull down for the Name box?
 
Last edited:
Hi ,

The easiest way to put in a named range is to enter the sheet name and then the named range , just as you would a normal range reference ; thus , if you would normally enter :

=Sheet3!$A$7:$A$23

and now you have put in the named range X_axis to refer to the above range , then enter :

=Sheet3!X_axis

Excel will accept this , and on its own , change the reference to :

=Example.xlsx!X_axis

The named range Modified_X_axis will not appear in the pull down because it is not a physical range referring to cells anywhere within the worksheet ; it is a virtual range , which has been created in the internal memory where Excel stores its named ranges.

Narayan
 
NARAYANK991 said:
Hi ,

The easiest way to put in a named range is to enter the sheet name and then the named range , just as you would a normal range reference ; thus , if you would normally enter :

=Sheet3!$A$7:$A$23

and now you have put in the named range X_axis to refer to the above range , then enter :

=Sheet3!X_axis

Excel will accept this , and on its own , change the reference to :

=Example.xlsx!X_axis

The named range Modified_X_axis will not appear in the pull down because it is not a physical range referring to cells anywhere within the worksheet ; it is a virtual range , which has been created in the internal memory where Excel stores its named ranges.

Narayan
Click to expand...

I was able to replicate everything in red with the named range X_axis. This gave me the full month's name on the X axis.

But I still don't understand where Modified_X_axis comes into the picture. You mentioned it is a virtual range, but how did you create that range? Also, how does that range extract only the first letter of each month?
 
Hi ,

Because of its definition !

=LEFT(Sheet1!$B$4:$B$15,1)

extracts the first letter of each month. Only , it does not store them in any worksheet cells , which is why I referred to the range as a virtual range.

Suppose you had entered this formula in a worksheet range , say K5 through K16 , then they would display the letters J , F , M ,... ; you could name this range Modified_X_Axis and use it for your chart.

Narayan
 
NARAYANK991 said:
Hi ,

Because of its definition !

=LEFT(Sheet1!$B$4:$B$15,1)

extracts the first letter of each month. Only , it does not store them in any worksheet cells , which is why I referred to the range as a virtual range.

Suppose you had entered this formula in a worksheet range , say K5 through K16 , then they would display the letters J , F , M ,... ; you could name this range Modified_X_Axis and use it for your chart.

Narayan
Click to expand...

Where is the LEFT function actually entered? How do you connect that with the virtual range Modified_X_Axis? How do you actually set up the virtual range?
 
Hi ,

The formula is entered in the Refers To box , when you define the named range.

I went into the Name Manager , clicked on New , typed in Modified_X_Axis , and in the Refers To box , entered the formula :

=LEFT(Sheet1!$B$4:$B$15,1)

where the range Sheet1!$B$4:$B$15 contains the full names of the 12 months.

Narayan
 
NARAYANK991 said:
Hi ,

The formula is entered in the Refers To box , when you define the named range.

I went into the Name Manager , clicked on New , typed in Modified_X_Axis , and in the Refers To box , entered the formula :

=LEFT(Sheet1!$B$4:$B$15,1)

where the range Sheet1!$B$4:$B$15 contains the full names of the 12 months.

Narayan
Click to expand...

Nice! That was amazing. I didn't know you could do that. Before this thread I have never even heard of a virtual range. I couldn't even find much on it with an Internet search.

To help me in the future, when would you use a virtual range? In what type of situations?
 
Hi ,

You can use this in almost every circumstance , since it does not use any physical worksheet cells.

Only when you start using VBA , will there be some disadvantages , since with named ranges which refer to physical worksheet cells , you can assign values to them ; for example , suppose you have a named range , called Physical , which refers to any worksheet cell address , say J5.

Thus in the definition of Physical , in the Refers To box , you would have : =Sheet1!$J$5.

In VBA , you can have a statement :

Range("Physical").Value = 7

and the cell address J5 on Sheet1 would get the value 7.

But if you have a virtual named range , called Virtual , then you cannot have an assignment such as :

Range("Virtual").Value = 7

Narayan
 
NARAYANK991 said:
Hi ,

You can use this in almost every circumstance , since it does not use any physical worksheet cells.

Only when you start using VBA , will there be some disadvantages , since with named ranges which refer to physical worksheet cells , you can assign values to them ; for example , suppose you have a named range , called Physical , which refers to any worksheet cell address , say J5.

Thus in the definition of Physical , in the Refers To box , you would have : =Sheet1!$J$5.

In VBA , you can have a statement :

Range("Physical").Value = 7

and the cell address J5 on Sheet1 would get the value 7.

But if you have a virtual named range , called Virtual , then you cannot have an assignment such as :

Range("Virtual").Value = 7

Narayan
Click to expand...
Thank you.
 
You must log in or register to reply here.
Back
Top