Books at Amazon.com

 

Creating Exponential Notation Axis Labels

 

The way most computer programs render numbers in scientific notation is not particularly attractive, for example 1.23E-04. Most of us learned true exponential notation in high school, and many publications require the use of this notation, rendered with a true superscripted exponent, for example 1.23×10-4. It is possible with today’s applications to produce superscripted text, but it can be a difficult task. In addition, there seems no way to superscript characters within an axis label. Because Excel doesn’t offer such labels off the shelf, it is often overlooked for purposes of scientific and engineering charting.

In our test chart, the X and Y scales are logarithmic, ranging from 0.001 (10-3) to 1000 (103). Using the “General” number format, these numbers look fine, in fact they are somewhat symmetrical.

But this was a special case. In general, the axis limits will not lead to a pleasing visual experience such as this. And even our nice chart axes look rather ugly in Excel’s scientific notation. Not only are they unattractive, they take up more space, squeezing out valuable plot area real estate.

We’ll use a helper series (“dummy” series) to build our own custom axis labels, following the Arbitrary Axis technique from elsewhere on this web site. The first step is to hide the built in axis labels. Double click on one axis, select the Patterns tab, and select None for Tick Mark Labels. Leave the tick marks themselves; I like to use “inside” minor ticks and “cross” major ticks on a log scale. If you change the first axis, you can immediately select the second axis and press the F4 key, and Excel will repeat your actions on the second axis. You’ll have to shrink the plot area to make room for the new labels. When the original labels were removed, Excel took away the space for them.

Now construct the ranges for the fake X and Y axes. Each uses three columns: one for the X values for the axis ticks, one for the Y values, and one for the labels. In my worksheet, the X axis data were in A18:C25 (below left) and the Y axis data were in A27:C34 (below right). I used formulas to construct the labels. The formula in C19 is ="10"&LOG(A19), and it is filled down through C25. The formula in C28 is ="10"&LOG(B28), and it is filled down through C34.

 

X Axis

 

1.00E-03

1.00E-03

10-3

1.00E-02

1.00E-03

10-2

1.00E-01

1.00E-03

10-1

1.00E+00

1.00E-03

100

1.00E+01

1.00E-03

101

1.00E+02

1.00E-03

102

1.00E+03

1.00E-03

103

 

Y Axis

 

1.00E-03

1.00E-03

10-3

1.00E-03

1.00E-02

10-2

1.00E-03

1.00E-01

10-1

1.00E-03

1.00E+00

100

1.00E-03

1.00E+01

101

1.00E-03

1.00E+02

102

1.00E-03

1.00E+03

103

Let’s walk through construction of the Y axis. The easiest way to add a series is to copy the data and paste it onto the chart. Copy the range with the X and Y data for the Y axis (not the labels), A27:B34. Select the chart, and choose Paste Special from the Edit menu, and add a new series, with categories in the first column and series names in the first row. The new series is shown by the blue squares and lines along the Y axis of our chart, below.

Now add the data labels. The following is the manual process; at the end of this page I have a macro that adds and formats the labels for you, making use of the X and Y axis label text in column C above. Even if you plan to use the macro, it’s useful to understand how the procedure works, so read on.

Double click on the new series, click on the Data Labels tab, and select the Value or Category option. It doesn’t matter which, because we aren’t using either. The default label position for an XY Scatter series is to the right of the points. Double click on a label, click on the Alignment tab, and choose the Left option for Label Position. Then one by one, select each single label, and type in the desired label text.

This is as good a place as any to hide (not delete) the helper series. Double click on the series, and on the Patterns tab, select None for Marker and None for Line.

The sequence below illustrates how we convert our labels to exponential notation. The chart is selected in pane A. Pane B shows the data labels for the entire series selected after a single click on any of the labels. Pane C shows a single data label selected after a second single click on the label. Pane D shows text within the label selected using the mouse. Finally, Pane E shows the selected text superscripted using the Format menu or the shortcut CTRL+1 to open the Format Font dialog.

A. Chart selected

B. Labels selected

C. Single label
selected

D. Text partly
selected

E. Partial superscript

That’s a pretty simple, if tedious, process to follow, and it results in the following chart, with exponential notation in the Y axis labels. In addition to superscripting the exponents, I increased the font size by a point, compared to the labels in pane E above. Change the font size of the individual characters while they are selected, at the same time that you add the superscript format.

I put together the following macro, which applies labels to the points, and superscripts all but the first two characters. It is called from the VB Editor’s Immediate Window, but can be called from another sub. The syntax is:

AttachLabelsToPoints , , 

where myChart is either the index of the chart’s parent ChartObject or zero for the active chart, mySeries is the index of the series to be labeled, and myOffset signifies the number of columns between the series’ X values and the labels. Selected the chart, and run this for the first extra series (the Y axis):

AttachLabelsToPoints 0, 2, 2

Add the X axis helper series as above (copy the range, select the chart, Edit > Paste Special > New Series) and run this for the second extra series (the X axis):

AttachLabelsToPoints 0, 3, 2

After running the macro, all that is required is to position the labels. Double click on the labels for the Y axis, and on the Alignment tab, select Left. Double click on the labels for the X axis, and on the Alignment tab, select Below. The final result is a professional chart:

Public Sub AttachLabelsToPoints(myChart, mySeries, myOffset)
    ' myChart: Index of ChartObject, 0 for active chart
    ' mySeries: Index of series within SeriesCollection
    ' myOffset: Labels located to right of X data by myOffset columns
    
    'Declare variables
    Dim Counter As Integer, ChartName As String
    Dim xVals As String
    Dim Counter1 As Integer, Counter2 As Integer
    Dim myString1 As String
    Dim cht As Chart, srs As Series, rng As Range
    
    ' Disable screen updating while the subroutine is run.
    Application.ScreenUpdating = False

    ' Define chart and series
    If myChart = 0 Then
        Set cht = ActiveChart
    Else
        Set cht = ActiveSheet.ChartObjects(myChart).Chart
    End If
    Set srs = cht.SeriesCollection(mySeries)

    'Store the formula for the first series in "xVals"
    xVals = srs.Formula

    'Extract the range for the data from xVals
    Counter1 = 0
    Counter2 = 0
    Counter1 = InStr(xVals, ",") ' 27
    xVals = Mid(xVals, Counter1 + 1)
    If InStr(xVals, "(") = 1 Then
        xVals = Mid(xVals, 2)
        Counter1 = InStr(xVals, ")")
    Else
        Counter1 = InStr(xVals, ",")
    End If
    xVals = Left(xVals, Counter1 - 1)
    
    ' Define range with X values
    Set rng = ActiveSheet.Range(xVals)
    
    'Attach a label to each data point in the series
    Counter1 = rng.Cells.Count
    For Counter = 1 To Counter1
        ' Get label text from cell
        myString1 = rng.Cells(Counter, 1).Offset(0, myOffset).Value
        With srs.Points(Counter)
            ' Apply label to point
            .HasDataLabel = True
            .DataLabel.Characters.Text = _
                myString1
            ' Superscript part of the label
            With .DataLabel.Characters(3, Len(myString1) - 2).Font
                .Superscript = True
                .Size = .Size + 1
            End With
        End With
    Next Counter
    
    ' Clean up the mess
    Set rng = Nothing
    Set srs = Nothing
    Set cht = Nothing
    Application.ScreenUpdating = True
    
End Sub

 

How to convert scientific notation to text or number in Excel?

Convert scientific notation to text with adding single quote before the number

Before you enter the numbers, you can type a single quote ’ first, and the numbers will not become the scientific notation, see the following screenshots:

Convert scientific notation to text with Format Cells function

If you have a lot of numbers which are displayed as the scientific notation, and you are tired of entering them repeatedly with the above method, you can convert them with the Format Cells function in Excel.

1. Select the data range that you want to convert.

2. Right click, and choose Format Cells from the context menu, see screenshot:

3. In the Format Cells dialog, under the Number tab, click Custom from the Category list box, input the number 0 into the Type box, see screenshot:

4. Then click OK button, and the cell numbers which displayed as scientific notation have been converted to normal numbers.

Convert scientific notation to text with formulas

The following formulas also can help you to convert the list of scientific notation to text.

1. Please enter this formula: =trim(A1) into a blank cell, B1 for instance, see screenshot:

2. Then drag the fill handle over to the range that you want to apply this formula, and you will get the result as you want:

Note: This UPPER function: =UPPER(A1) also can help you, please apply any one as you like.

Convert scientific notation to text with Kutools for Excel

If you have installed Kutools for Excel, you can use the Convert between Text and Number feature to finish this job.

After installing Kutools for Excel, please do as this:

1. Highlight the data range that you want to convert.

2. Click Kutools > Content > Convert between Text and Number, see screenshot:

3. In the Convert between Text and Number dialog box, check Number to text option, and then click OK or Apply button, the numbers displayed as scientific notation have been converted to normal numbers in the original range. See screenshot:

Tips:

If the length of your number is longer than 15 digits, it will not be converted to the complete numbers as you need. You just need to type the single quote before your input number or format your selected range as Text format in the Format Cells dialog box before you entering the numbers.

To know more about this Convert between Text and Number feature.

Download and free trial Kutools for Excel Now!

Demo: Convert scientific notation to text with Format Cells function

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Download and free trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse:

  Quickly insert

  complex formulas, charts

   and anything that you have used before;

  Encrypt Cells

  with password;

  Create Mailing List

  and send emails…

• Super Formula Bar

  (easily edit multiple lines of text and formula);

  Reading Layout

  (easily read and edit large numbers of cells);

  Paste to Filtered Range

  …

• Merge Cells/Rows/Columns

  without losing Data; Split Cells Content;

  Combine Duplicate Rows/Columns

  … Prevent Duplicate Cells;

  Compare Ranges

  …

• Select Duplicate or Unique

  Rows;

  Select Blank Rows

  (all cells are empty);

  Super Find and Fuzzy Find

  in Many Workbooks; Random Select…

• Exact Copy

  Multiple Cells without changing formula reference;

  Auto Create References

  to Multiple Sheets;

  Insert Bullets

  , Check Boxes and more…

• Extract Text

  , Add Text, Remove by Position,

  Remove Space

  ; Create and Print Paging Subtotals;

  Convert Between Cells Content and Comments

  …

• Super Filter

  (save and apply filter schemes to other sheets);

  Advanced Sort

  by month/week/day, frequency and more;

  Special Filter

  by bold, italic…

• Combine Workbooks and WorkSheets

  ; Merge Tables based on key columns;

  Split Data into Multiple Sheets

  ;

  Batch Convert xls, xlsx and PDF

  …

• More than 300 powerful features

  . Supports Office/Excel
  2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
  30-day free trial. 60-day money back guarantee.

Read More… Free Download… Purchase… 

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint

  , Publisher, Access, Visio and Project.

• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More… Free Download… Purchase…