# Are Your Formulas All “Wrapped” Up?

The other day, there was a discussion on one of the LinkedIn Excel Groups regarding wrapping formulas in the formula bar. Today. I’ll take a look at wrapping and indenting formulas complete with screen shots and a workbook you may download at the end.

## The Data

First, I’ll need some data. I use Dick Kusleika’s Random Data Generator to get some quick data. I selected the fields I needed to generate and voila! 20K records very quickly.

I converted the entire range of data to an Excel Table. I named the table, “tblData”

## The Analysis

To analyze the data I went to another sheet and setup some criteria fields based on the Field Headers.
Think of this section as a Page Field in a Pivot table. You do not necessarily have to setup your analysis in this manner. It is just a structure I use as it makes sense to me.

“Page Field” – Any criteria that is common to all formulas may be left in his section

“Row Labels”
– Any criteria that is unique to each formula may be placed in this section

## The Criteria

I entered all of the crieria in each cell that I am interested in

## The Formula – Long Form

I wrote a formula using Excel Tables and Structured References

Not too bad. But it is a little long and hard to read. The image causes us to have to scroll left right on the blog post to see the whole thing so we lose clarity of what is on the left. I’ll “wrap” the formula in the formula bar to see if that clears things up.

## The Formula – Wrapped Form

To “Wrap the formula in the formula bar, click before the text where you want to insert a break, then hold down the [ALT] key and press [ENTER]. I’m going to add a break for each Field/Criteria set so each set is on its own line in the formula bar.

Looking better. I’ll see what it looks like if I indent each line as well. To indent a line, use the space bar to mve the text to the right.

## The Formula – Wrapped And Indented Form

I indented all lines after the first line to line up the name of the table.

I think this version is the easiest to read and understand.

## The result

I drilled in to the data, In this instance, there was only 1 line item that met the criteria so validation of the formula results in this instance was easy.

Let us know how you use wrap and indent in your formulas in the comments section below.

# Are Your Formulas All "Wrapped" Up?

The other day, there was a discussion on one of the LinkedIn Excel Groups regarding wrapping formulas in the formula bar. Today. I’ll take a look at wrapping and indenting formulas complete with screen shots and a workbook you may download at the end.

## The Data

First, I’ll need some data. I use Dick Kusleika’s Random Data Generator to get some quick data. I selected the fields I needed to generate and voila! 20K records very quickly.

I converted the entire range of data to an Excel Table. I named the table, “tblData”

## The Analysis

To analyze the data I went to another sheet and setup some criteria fields based on the Field Headers.
Think of this section as a Page Field in a Pivot table. You do not necessarily have to setup your analysis in this manner. It is just a structure I use as it makes sense to me.

“Page Field” – Any criteria that is common to all formulas may be left in his section

“Row Labels”
– Any criteria that is unique to each formula may be placed in this section

## The Criteria

I entered all of the crieria in each cell that I am interested in

## The Formula – Long Form

I wrote a formula using Excel Tables and Structured References

Not too bad. But it is a little long and hard to read. The image causes us to have to scroll left right on the blog post to see the whole thing so we lose clarity of what is on the left. I’ll “wrap” the formula in the formula bar to see if that clears things up.

## The Formula – Wrapped Form

To “Wrap the formula in the formula bar, click before the text where you want to insert a break, then hold down the [ALT] key and press [ENTER]. I’m going to add a break for each Field/Criteria set so each set is on its own line in the formula bar.

Looking better. I’ll see what it looks like if I indent each line as well. To indent a line, use the space bar to mve the text to the right.

## The Formula – Wrapped And Indented Form

I indented all lines after the first line to line up the name of the table.

I think this version is the easiest to read and understand.

## The result

I drilled in to the data, In this instance, there was only 1 line item that met the criteria so validation of the formula results in this instance was easy.

Let us know how you use wrap and indent in your formulas in the comments section below.

# Gangnam Cell Styles…Delete

A reader on one of the LinkedIn Excel Groups recently asked how to delete cell styles from a workbook. Today, I’ll show a possible solution.

## List Cell Styles:

I opened a workbook, Alt+F11 for the Visual Basic Editor and cooked up the code below. I then opened another workbook and made sure the first sheet was active by clicking on cell \$A\$1.

I then ran this code:

``1:  Option Explicit2:  Sub ListStyles()3:    'List all styles in a workbook4:    Dim C As Range5:    Dim rng As Range6:    Dim i As Long7:    Dim lRows As Long8:    With Application9:      .ScreenUpdating = False10:      .EnableEvents = False11:    End With12:    With ActiveWorkbook13:      'Add a temporary sheet14:       .Sheets.Add before:=Sheets(1)15:      'List all the styles16:       For i = 1 To .Styles.Count17:        ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = _18:          .Styles(i).Name19:      Next i20:    End With21:    'Tidy up22:     'Destroy objects23:     Set rng = Nothing24:     Set C = Nothing25:     'Excel environment26:     With Application27:      .DisplayAlerts = True28:      .EnableEvents = True29:     End With30:  End Sub  ``

This returned 47 different styles. I am not going to list them all here. They are included in the workbook that you may download at the end of the post. Your workbook may have more or less depending on any customizations you have already made to your workbook or if you are working on a workbook you received from someone else.

Once you have a list of the styles, you may edit the list for thes styles you wish to keep or leave them all on the list if you wish to delete them all.

Let’s see what happens if we delete all of them, shall we? This will only impact Cell Styles. It will not impact Table Styles or Chart Styles.

## Delete Cell Styles:

Here’s the code I am going to use to delete all cell styles from the cell styles group.

``1:  Option Explicit2:  Sub ClearStyles()3:    'Deletes all styles from the active workbook4:    Dim lRows As Long5:    Dim C As Range6:    Dim rng As Range7:    With Application8:      .ScreenUpdating = False9:      .EnableEvents = False10:      .DisplayAlerts = False11:    End With12:    'Make sure to click on sheet with list of styles to be deleted13:    'Assumes list begins in \$A\$114:     With ActiveSheet15:      lRows = .Cells(Rows.Count, 1).End(xlUp).Row16:      Set rng = Range(.Cells(1, 1), Cells(lRows, 1))17:     End With18:    With ActiveWorkbook19:      For Each C In rng20:        On Error Resume Next21:        .Styles(C.Text).Delete22:        .Styles(C.NumberFormat).Delete23:      Next C24:    End With25:    'Tidy up26:     'Destroy objects27:     Set rng = Nothing28:     Set C = Nothing29:     'Excel environment30:     With Application31:      .ScreenUpdating = True32:      .DisplayAlerts = True33:      .EnableEvents = True34:     End With35:  End Sub``

## Bam!!

Deleted 46 out of 47. Apparently cannot delete all styles or at least the “Normal” style.

Chart styles still work

Excel Table styles still work

You may now add any custom styles to your workbook. But please, no Gangnam Style ðŸ™‚
(Sorry Psi)

More on Styles and VBA from Jan Karlel Pieterse

How do you work with Cell Styles and VBA? Let us know in the comments section.

# Are You Series(ous)?

Do you work with different series in your Excel charts?

Here is what I would like the final chart to loo like. Note the white dashed lines in front of the columns:

#### Setup:

I started with the chart I ended with in my last post, Points Of Interest.

I then added some data to a separate worksheet in my workbook to replicate the horizontal gridlines.

The horizotal gridline scale is in units of 10, so I will use the same scale for my series’ that I create for the “fake” gridlines.

Note in Col A that I used the exact same scale that I used for Series 1 which is what was used to plot the column charts (Really 1 chart)

Also note that I put data that is used for formatting on a separate worksheet from “Value Data”. This makes it easier to maintain data as it needs to updated overtime

#### 1. Horizontal gridlines

I am going to add fake horizontal gridlines to the chart, so first I’l remove the current horizontal gridlines.

• Click on the chart (This will activate the Chart Tools Group on the Ribbon)
• Click on “Layout” in the chart tools group
• Click on gridlines
• Click on Primary Horizontal Gridlines
• Click on “None”

#### 2. Add “Fake” horizontal gridlines

I copied all data from the formatting tab including the horizontal axis column, clicked on my chart and pasted the new series.

I could click on each of the new series on the chart and change the chart type from column to line and apply all of the formatting. But I would like to do it with VBA. So I’ll step through each part of the process separately

#### 3. Change the chart type:

First I need to chage the chart type for each series that is not a value series which was series 1. So I’ll loop through the SeriesCollection starting at number 2

``1:  Option Explicit2:  Sub SetChartType()3:    Dim wb As Workbook4:    Dim ws As Worksheet5:    Dim i As Integer6:    Set wb = ThisWorkbook7:    Set ws = wb.Worksheets("Sheet1")8:    With ws9:      .ChartObjects(1).Activate10:      For i = 2 To .ChartObjects(1).Chart.SeriesCollection.Count11:        .ChartObjects(1).Chart.SeriesCollection(i).ChartType = xlLine12:      Next i13:    End With14:    'Tidy up15:      Set ws = Nothing16:      Set wb = Nothing17:  End Sub``

#### 4. Format the color of the lines

I would like each of the horizontal lines to be white

``1:  Option Explicit2:  Sub SetChartColor()3:    Dim wb As Workbook4:    Dim ws As Worksheet5:    Dim i As Integer6:    Dim lWhite As Long7:    Set wb = ThisWorkbook8:    Set ws = wb.Worksheets("Sheet1")9:    lWhite = RGB(255, 255, 255)10:    With ws11:      .ChartObjects("Chart 1").Activate12:      With ActiveChart13:        For i = 2 To .SeriesCollection.Count14:          .SeriesCollection(i).Select15:          With Selection16:            .Format.Line.ForeColor.RGB = lWhite17:          End With18:        Next i19:      End With20:    End With21:    'Tidy up22:      Set ws = Nothing23:      Set wb = Nothing24:  End Sub  ``

#### 5. Change the line type:

The chart is looking pretty good. But the lines are a little thick, I would like something more subdued. I played around a bit with different line weights and dash styles until I found values that I liked

``1:  Option Explicit2:  Sub SetLineProperties()3:    Dim wb As Workbook4:    Dim ws As Worksheet5:    Dim i As Integer6:    Dim lWhite As Long7:    Set wb = ThisWorkbook8:    Set ws = wb.Worksheets("Chart")9:    lWhite = RGB(255, 255, 255)10:    With ws11:      .ChartObjects("Chart 1").Activate12:      With ActiveChart13:        For i = 2 To .SeriesCollection.Count14:          .SeriesCollection(i).Select15:          With Selection16:            .Format.Line.Weight = 1.517:            .Format.Line.DashStyle = msoLineRoundDot18:          End With19:        Next i20:      End With21:    End With22:    'Tidy up23:      Set ws = Nothing24:      Set wb = Nothing25:  End Sub  ``

I added the major horizontal gridlines back to the chart and made them very thin and light grey

#### The final chart:

The lines may be a little thin for your tastes, just adjust the line weight until you get the effect you are trying to achieve.

I hope you enjoy the post and that you find something of value in it.