Loading...

Follow Peltier Tech Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

If you work with any type of data, then you know that rarely does it come perfectly in the format you need. Power Query (also called Get & Transform in Excel 2016) is the solution to this problem. Power Query is an amazing ETL tool (Extract, Transform, and Load). If you’re not using it then you’re missing out on the best new feature in Excel.

For a more detailed look at what Power Query is and what you can do with it, check out this Introductory Guide to Power Query.

Power Query can connect to many different types of data, but in this post we’ll take a look at importing data from an Excel workbook and doing some basic data cleansing transformations on some messy data.

The Data

For this post, I’ve prepared some fake sales data (download This Week’s Garbage Data File.xlsx to follow along with the example). It’s presented in a typical fashion which is human readable, but not great for any type of further analysis. In this post, we’ll explore how we can use Power Query to get this data into a standard tabular format which will help facilitate any analysis done later on.

There are a few problems with the data we want to import, and we’ll need to fix them before our data is ready to use.

  1. The data has been summarized by date, but the date pertaining to each set of sales only appears once as a heading.
  2. There are leading spaces in front of the sales person which will need to be removed.
  3. The name of the sales person and their employee ID number have been concatenated together.
  4. There are rows of spaces throughout the data that will need to be removed.
  5. Sales for the different regions have been pivoted across four different columns. We will want our sales in one column with an extra column defining the region.
  6. We have total rows for each of the sections of data that will need to be removed.
Importing Our Data

The first step we need to take is to import our data.

Go to the Data tab and press the Get Data command then choose From File and then From Workbook in the resulting menus.

A file picker menu will appear and we can then navigate to the Excel file we want to import.

Once we’ve picked the file to import, we will be presented with the Navigator window. This will allow us to select the Sales sheet from our workbook which contains the data we want to import. A preview of the data can be seen on the right hand side when selecting sheets.

If our workbook had more than one sheet of data which we wanted to import, we can select multiple sheets using the Select multiple items check box. This would create a separate query for each of the sheets selected and save going through the first couple steps individually.

Now we can either choose to Load the data or Edit it. If we choose to load it, then it will load into an Excel Table with no transformations applied. We want to edit it, this is where we’ll be able to apply our data cleaning transformations. Note: In some builds of Excel, the Edit button is labeled Transform Data.

Press the Edit button and this will open up the Power Query editor.

The Power Query Editor

We are now inside the Power Query editor and we can begin building our data transformation to get our data into proper tabular format.

Any transformations you apply to the data will appear as a step in the Applied Steps area. We can step forward and backward through the query by clicking on any of the steps listed in this area.

On the left side of each step is an X that can be used to delete a step and on the right there is a settings icon which can be used to edit the step.

Getting the Date into its Own Column

The first thing we’ll do in our transformation is get the date associated with each section into its own column. Notice that each of these date headings in our data is separated with a colon. Since no other data in this column apart from the dates have a colon, we can use it as a delimiter to split out the data.

To split this data out, right click on the column heading then select Split Column and then choose By Delimiter.

Select Colon from the list of delimiters and press the Ok button.

Now we have a new column with dates followed by a series of null values until the next date. Right click on the new column and select Fill then choose Down. This will fill the dates down the column replacing the null values with the date from above.

Split Name and Employee Number

In the next step we’re going to split out the sales persons name and their employee number. The name and employee number are always separated by an opening parenthesis so we’ll use that as the delimiter to split the column.

This assumes that an opening parenthesis will only ever appear in our first column when it is separating the employee number.

Now we right click on the first column and select Split Column then choose By Delimiter.

This time we need to use a Custom delimiter since the opening parenthesis is not a standard option. Add in the “(” and press the OK button.

Notice that our new column with the employee number still has the closing parenthesis. We’ll need to clean this up. We can right click on the column and select Replace Values from the menu. Then we can replace the “)” character with nothing. This will remove it from our data.

Remove the Leading Spaces for the Name

Now we can remove the useless leading spaces that were used for indentation in the presentation of the data.

We just need to right click on the column and select Transform and then Trim. This will remove any leading or trailing space characters from our data.

Remove Unnecessary Rows

We’re now ready to remove the unnecessary rows in our data including the total rows and blank rows from our original data file. We also have some rows that are now useless as we’ve already extracted the useful data from them. We can remove all of these rows in the one step.

In fact, we’ll be able to do this by filtering on the first column. Click on the filter icon on the right side of the column heading and then deselect the useless rows (null, Sales for, Sales Person, and Total).

You might be asking why we filtered out the row containing Sales Person. Doesn’t that row contain useful column heading information? Couldn’t we use that and promote it to the column headings?

Yes it does, but we can’t use it to promote to column headers because one of the values in that row is the date from the first step in our transformation. That value will likely change if we’re importing a different set of data with this query.

If we were to promote it to a column heading then we’d have a column heading with the label “2018-01-01”. Any further step that references this column, would be referencing this value which won’t exist. This is were our query would break.

Instead we’ll remove this row and rename our current generic column headings to avoid this problem.

Rename Column Headings

Now we can rename all our columns to their appropriate names. Double click on the current column name to rename a column. We’ll name these Sales Person, Employee ID, Date, North, East, South and West going from left to right.

Unpivot the Region Columns

Now we have an almost tabular set of data. The only thing that remains is to unpivot our four region columns. All the values in these columns represent the same metric of sales and should be in one column with another column that tells us what region the sales amount was from.

Select the North, East, South and West columns by holding Ctrl and clicking on each heading. You can also select the first (North) column then hold Shift and select the last column (West). Now right click on any of the column headings and select Unpivot Columns.

This will result in two columns called Attribute and Value. We can rename these to Region and Sales respectively.

Change Data Types

Now our data is just about ready. At the end of any data transformation, it’s good practice to change the data types of columns to the appropriate type.

For the most part our data has the right data types, but our Sales column can be changed to a currency type. Click on the data type icon on the left side of the column heading and select the Currency type from the menu.

Close and Load the Query

Now that our data transformation is complete, we are going to need to save our query. Go to the Home tab in the query editor and press the Close & Load button. Just remember, you’re still going to need to save the workbook when you’re done in the query editor.

Clicking on the top part of the close and load button will load the data into a new sheet in an Excel table. You can also click on the lower part of the button and select Close & Load To to open the Import Data dialog for more loading options like loading the data directly into a pivot table or into the data model.

For now, we’ll just load the data into a table.

Now we’ve got our data in a ready to use tabular format!

Updating the Query for New Data

Now what happens when we get new data? (Download This Week’s Garbage Data File.xlsx to try this.)

We can use this query again to transform that data. Go to the Data tab and press the Queries & Connections button. Then right click on the query and choose Edit.

Now we can navigate to the Source step in the Applied Steps area.

Replace the source location in the formula bar with the path and file name of the new data and press the Enter button to confirm the changes. Then we can close and load the query. Our data will update based on the new file!

Note, this will overwrite the previous data, so if we want to retain the old transformed data we’re going to need to create a copy of the workbook before performing these updating steps.

Conclusions

Everything we did in this transformation was using the basic point and click interface in the Power Query editor. Once you get the hang of it, making queries to shape your data into the format you want becomes super easy. No workbook formulas, no difficult VBA and no copying and pasting required.

The best part is, if we need to repeat this transformation with another similar data set, then we can update the file path in the query and press the refresh data button and our new data will be transformed and loaded into Excel. Our messy data problem has become a simple refresh.

This is why Power Query is the best new feature in Excel that you need to start using.

About the Author

John MacDougall is a Microsoft MVP and former actuary who currently works on data analysis and debugging code at an ad tech company. He shares his knowledge and passion for Excel and Power BI through his website How To Excel.

The post Importing and Cleaning Data with Power Query appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Peltier Tech Blog by Jon Peltier - 1w ago
Cycle Plots

When you make a chart in Excel, sometimes plotting all of the series in the same space make it hard to see the behavior of an individual series (below left). A cycle plot separates each series into its own section of the main chart, so it is not obscured by other series (below right).

I first read about cycle plots in Naomi Robbins’ article Introduction to Cycle Plots on the Perceptual Edge web site.

Cycle plots aren’t standard Excel charts, but they’re not too hard to create. Soon after Naomi’s article was published, my colleague and former Excel MVP Charley Kyd wrote How to Create Cycle Plots in Excel to Chart Seasonal Sales Data.

A few years ago I wrote a tutorial, How To Make Cycle Plots Using Excel Charts, which showed a convoluted way of creating these charts, loosely based on Charley’s approach..

The tutorial below describes a technique which I hope is easier to follow and reproduce than the old technique. In addition, following the tutorial you’ll find information about Peltier Tech Charts for Excel, which has its own Cycle Plot features (in the Advanced Edition only).

Creating Cycle Plots in Excel

We’ll start with this simple compact data range for our cycle plot. The categories (X axis labels) are in the first column, highlighted in purple, series names in the first row, highlighted in red, and Y values are in the grid, highlighted blue.

We can make line, area, column, or bar charts from this data, and in fact, all of these types are suitable for cycle plots.

Cycle Plot Data

To make the cycle plot work, we need to divide the chart into sections, or cycles, or panels. Each series in the chart will be plotted in its own cycle, with its own category labels. We leave the series names in place (again highlighted in red below), and duplicate the category labels (purple) in the first column so they appear once per series. Then we stagger the Y values (blue) for each series so each aligns with its own set of category labels.

We then expand the data by inserting blank rows before, between, and after each cycle’s data, shown by the yellow shaded rows below. This provides a bit more horizontal separation between the data. It is helpful to put space characters into the first cells of the inserted rows (the darker highlights). When you make a chart from this data, Excel will see the space characters and make them part of the category labels for the chart.

Below is the data and chart, with the series names, category (X axis) labels, and Y values highlighted in red, purple, and blue as before. Note that each series is plotted in its own region of the chart, with its own duplicate set of X axis labels.

Secondary Axis for Labeling

Let’s add some secondary data, which will provide gridlines to separate the cycles (panels). The secondary range is shown below the chart, with the series names from the main chart to be used as secondary axis labels (highlighted in purple), a new series name “secondary” (red), and zero values (blue).

Copy this new range of data, select the chart, and use Paste Special from the Paste dropdown button on the Home tab of the ribbon. Choose the options shown in the Paste Special dialog below.

We see a new series, which at first is present just in the bottom left corner of the chart. We’ll have to hide this series (format it with no markers and no lines) and move it to the secondary axis.

We didn’t really have to add a new range of data for this secondary series. We could have used the top of the existing data range, as highlighted below. The series has no name in the legend, because it uses a blank cell (highlighted red) as its name, and it doesn’t appear in the chart even though the legend shows a yellow marker and line, because its Y values consist of blank cells (highlighted blue).

Right click on the new series if you can see it, or right click on any series in the chart, and select Change Series Chart Type. The dialog lists the series in the chart, along with their chart types (which we do not need to change), and whether the series is plotted on the secondary axes. Check the Secondary box next to the new series, and click OK.

The chart doesn’t really change much. The data is plotted where it was, but now there is a new vertical axis. Click the little plus “skittle” (that’s Microsoft’s term, and I wish more people knew it), and click on the right-pointing arrow next to Axes. The checked axes are the default ones when you’ve just moved a series to the secondary axis.

For our chart, we need to uncheck Secondary Vertical and check Secondary Horizontal. The secondary horizontal axis labels are the original series names, and they are neatly positioned above the plotted data for each series.

Formatting

Now a little formatting. If the secondary axis series is visible in the chart (below left), format the series so it has no markers and no line, to hide it (below right).

Using the plus skittle, click the arrow next to Gridlines and check the Secondary Vertical box. This gives us lines that separate the data for each series, effectively forming panels (below left).

Note that the white spaces to the left of series alpha and to the right of series gamma are wider than the spaces between series and the interior gridlines. We can fix that by formatting the primary horizontal axis (along the bottom of the chart). Select the axis and press Ctrl+1, Under Axis Options, choose On Tick Marks for Axis Position (below right). The spaces are now uniform.

Finally, remove the legend, which was made redundant by the secondary axis labels at the top of the chart.

Cycle Chart Types

I showed this technique using a line chart, but you can also use a column chart instead.

You can also use a bar chart.

You can use an area chart as well. Note that the default area chart plots blank cells as zeros, so the edges of each series are not vertical (below left). To correct this, right click the chart and choose Select Data, click the Hidden and Empty Cells at the bottom left of the dialog, and choose Show Empty Cells As Gaps and click OK twice. The area series now don’t encroach upon each other (below right).

You can even mix up the series in a combination cycle plot, but make sure this won’t complicate the visual or confuse your readers.

Peltier Tech Charts for Excel Creating Cycle Plots with Peltier Tech Charts for Excel

While this technique isn’t too difficult, it takes some time, and after your fourth chart in a row, it becomes tedious. But never fear, because Peltier Tech Charts for Excel, my Excel charting add-in, handles this task with ease.

Select a suitable data range, such as B2:E8 below. If you select a single cell inside such a range, the program will expand the selection outwards until it hits empty rows and columns, and use that as the source data.

You can even use a discontiguous range, as shown below. You have to select all of the individual ranges.

The ranges you select also have to result in a “nice” range. A “nice” range is shown below in green; it includes the intersections of the entire row of the selection (yellow rows) with the entire column of the selection (blue columns).

The discontiguous range can be split up a number of different ways. The range below works fine, with eight separate areas.

This range also works fine, with nine separate areas.

The following range is “too” discontiguous; the limit is somewhere between 9 and 12 areas before VBA chokes on a range. But that’s okay, because this is more complicated than you should be trying to plot.

Once you select a range, click on the split button labeled “Cycle Plot”,

or on the Cycle Plot menu button which is also labeled “Cycle Plot”.

Up pops the Cycle Plot Dialog. Make sure the desired options are selected. (The program remembers the options you used last time.) Then click OK.

The program inserts a new worksheet with linked data that is appropriately arranged for the cycle plot, and creates a cycle plot using the selected options.

Among the options are Chart Type…

… whether to plot by row or by column…

… whether to format each series differently or all series the same…

… whether to show dividers between series or not…

… and whether to add means, trendlines, or both to each series.

Converting Regular Charts to Cycle Plots with Peltier Tech Charts for Excel

Not only can Peltier Tech Charts for Excel build cycle plots from scratch, but if you have a regular chart that you’ve already formatted and you want a cycle plot of the same data with the same formats, Peltier Tech Charts can convert it for you.

Here is some data and a standard column chart, which is to be converted to a cycle plot.

Select the chart (or select several charts: it will do them all), then click the Cycle Plot dropdown on the Peltier Tech ribbon, and click Convert Chart to Cycle Plot.

The program inserts a new worksheet, and builds the appropriate data range with links to the data in the original chart. It copies the chart and pastes a duplicate chart on the new workbook, and adjusts the data in this new chart so the same series (with the same formatting) now refer to the cycle plot data. Finally, the program adds the secondary series that provides the labels above the chart and the vertical gridlines. The original chart and data remain intact.

Here are a handful of original charts.

The program created the resulting cycle plots, each from its own worksheet (with its own data).

It took me longer to make these screenshots than it took my software to convert all of the charts.

Discount on Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0 – Advanced Edition normally retails for $99 US for a perpetual license. For a limited time, you can get a discount of $20. Go to the Peltier Tech Charts for Excel page, click on the Advanced button, and in the shopping cart, enter the discount code CYCLE to receive your discount.

The post Cycle Plots in Excel appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
User Defined Function to Control Chart Axes?

Can you really use a chart UDF to control axis scales? That would be pretty cool. UDFs are VBA functions that you can use in your worksheet, but they only return values to cells, right?

Well, yes, that’s what the documentation says, but there’s a fortuitous bug/feature in Excel UDFs that extend their capabilities in unexpected ways.

Link Axis Scale to Worksheet Cells

For as long as I’ve been using Excel, people have wanted to be able to link the chart axis scale parameters to worksheet cells. Seriously, how hard could it be to link the axis scale boxes in the dialog to worksheet cells?

Microsoft tells us, if there’s a feature we really really want, post it in User Voice, and if it gets enough votes, we’ll work on it. And someone already has, back in 2015, Erik Svensen posted Link the min and max values of a chart axis to cell value. And in 2015, Microsoft said “Thanks for the suggestion!” As of this writing the suggestion has received 589 votes. If you haven’t voted, please do.

So there’s no built-in capability to do this (yet). It is possible to use VBA to link a chart’s axis to the worksheet, as I wrote about in Link Excel Chart Axis Scale to Values in Cells. Works well enough, if you’re comfortable with VBA, but it’s not easy to set up and maintain, and I’ve never felt it was too reliable. Former Excel MVP Tushar Mehta has his AutoChart add-in that sets it up for you, but the technique is still a bit wonky.

Excel User Defined Functions

I’ve written a few tutorials that describe specific UDFs, but I have no general tutorials on them. But there are hundreds of UDF articles on the internet.

User defined functions allow you to write functions which are not built into Excel. In Create custom functions in Excel, Microsoft starts a tutorial with

Although Excel includes a multitude of built-in worksheet functions, chances are it doesn’t have a function for every type of calculation you perform. The designers of Excel couldn’t possibly anticipate every user’s calculation needs. Instead, Excel provides you with the ability to create custom functions, which are explained in this article.

Another good introduction is Creating a UDF (User Defined Function) in Excel by my colleague Philip Treacy.

In general, a user defined function can only return the result of its calculations to the cell it is called from. The UDF cannot change cell formatting (other than changes based on the cell’s conditional formatting) nor affect another cell, other than through a formula in that other cell. The UDF cannot insert worksheets, change sheet names, create workbooks, or affect anything else in Excel except for the value in the cell it lives in.

Except that a UDF can create and modify shapes in Excel.

UDFs Can Modify Shapes

In 2006 on the Daily Dose of Excel blog, Rob Van Gelder posted in In Cell Charting that “you can create a Shape from a user-defined function.” Rob used this trick as a way to draw lines and shapes to make small sparkline charts in an Excel worksheet. At first this seemed like a neat parlor trick, but several of us contributed ideas and lines of code, both in the original post and in the follow-up, Scaled In Cell Charting. None of the code samples are reproduced here, as the procedures are all rather lengthy.

Eventually Fabrice Rimlinger turned this concept into Sparklines for Excel®, “a set of free User Defined Functions for Microsoft Excel® to create Sparklines.” Unfortunately, the Sparklines for Excel website and forum have not had any activity since 2016; they are still live, and you can still download add-ins which as far as I know still work.

However, the concept is interesting, and charts are in fact a special kind of Excel shape.

UDFs Can Modify Charts

Back in 2007, also on Daily Dose of Excel, John “Mr Spreadsheet” Walkenbach posted Modifying Shapes (And Charts) With UDFs. John showed three UDFs: the first changed the type of a shape (e.g., rectangle to ellipse), the second changed the chart type of a chart (e.g., column to line), and the third, most interesting, example, applied new axis scale limits:

Function ChangeChartAxisScale(CName, lower, upper)
    With ActiveSheet.Shapes(CName).Chart.Axes(xlValue)
        .MinimumScale = lower
        .MaximumScale = upper
    End With
End Function

Write a worksheet function in a cell to call this chart UDF, pass in the chart name and the new lower and upper limits…

=ChangeChartAxisScale("Chart 1",-5,5)

…and the chart updates.

UDFs Can Modify Chart Axes

While traveling in Australia, I read a post by Mark Proctor on the Excel Off The Grid blog, Set chart axis min and max based on a cell value. Mark has revisited the chart UDF concepts above with a function that will update the minimum or maximum of a chart axis:

=setChartAxis(chartName, MinOrMax, ValueOrCategory, PrimaryOrSecondary, Value)

Enter the chart name, indicate whether to update the min or max, whether it’s a value (Y) or category (X) axis, a primary or secondary axis, and finally provide the new value for the min or max. The function would look something like this:

=setChartAxis("Chart 1", "Max", "Value", "Primary", 15)

which will set the maximum of the primary value axis of the chart named “Chart 1” on the active sheet to 15.

Now that’s pretty cool. While waiting in the Virgin Australia departure lounge (thanks, Liam!), I whipped up my own version of this function, and shared it with colleagues Mynda Treacy and Gašper Kamenšek, who agreed that it’s pretty cool.

Immediately I thought of ways to build on Mark’s UDF. For example, why not modify all axis scale arguments (minimum, maximum, major unit, and minor unit) in one function? You can build in some error-proofing: what if you specify a secondary axis but the chart only has primary axes, or what if the entered minimum is greater than the entered maximum? Also, as I learned in one of my Unlock Excel conference sessions, modifying “Chart 1” on the active sheet will result in strange behavior if you have another chart named “Chart 1” on another sheet, and you activate this other sheet.

Mark has another interesting chart UDF tutorial, Create dynamic chart titles with custom formatting, which presents a UDF that builds a dynamic chart title using the contents and formats of a set of specified cells.

Advanced Chart UDFs

I’ve come up with the following user defined function, which will allow you to set all axis scale arguments for a given axis, in a specified chart on a specified sheet:

=PT_ScaleChartAxis(SheetName,ChartName,X_or_Y,Primary_or_Secondary,Minimum,Maximum,MajorUnit,MinorUnit)

To hard-code some values into the formula, use this:

=PT_ScaleChartAxis("Sheet1","Chart 1","Y","Primary",0,10,2,0.5)

To link the axis scale to cell values, as in the screenshot at the beginning of this article, use this:

=PT_ScaleChartAxis("Sheet1","Chart 1","Y","Primary",H3,H4,H5,H6)

Note that the worksheet name is specified as well as the chart name. The function accepts “x”, “X”, “category”, “cat”, or 1 for the category axis, and “y”, “Y”, “value”, “val”, or 2 for the value axis. It allows “primary”, “pri”, or 1 for the primary axis, and “secondary”, “sec”, or 2 for the secondary axis. For the axis scale arguments, the function accepts a numerical value, a valid cell reference, “auto”, “autoscale”, or “default” to let Excel apply its default scaling for that argument, and “null”, “skip”, “ignore”, “blank”, or a blank to make no change to that argument.

The function also returns informative feedback to the cell:

Sheet 'Sheet1' Chart 'Chart 1' Primary Y Axis {0, 10, 2, 0.5}

If the axis is successfully adjusted, the new parameters are provided; if an error is encountered (“Worksheet ‘Sheet1’ not found”, “Chart ‘Chart 1’ not found on worksheet ‘Sheet1′”, “Cannot scale a category-type axis”, etc.) then a message is returned.

The function’s prefix “PT” indicates that I like this function so much that I’ve added it to the Advanced Edition of my commercial Excel charting add-in, Peltier Tech Charts for Excel 3.0.

If you start typing the formula into a cell, Excel’s IntelliSense finds it for you:

and Excel’s Function Arguments dialog helps you fill in the arguments:

It’s proprietary, but I’ll share the UDF below (it’s long, because of error-proofing and the descriptive output):

Function PT_ScaleChartAxis(SheetName As String, ChartName As String, X_or_Y As Variant, Primary_or_Secondary As Variant, _
    Minimum As Variant, Maximum As Variant, MajorUnit As Variant, MinorUnit As Variant) As Variant
  
  Dim wks As Worksheet, cht As chart, ax As Axis
  Dim xyAxisGroup As XlAxisGroup
  Dim rCaller As Range
  Dim dMinimum As Double, dMaximum As Double
  Dim bSetMin As Boolean, bSetMax As Boolean
  Dim sError As String, iError As Long
  Dim vTestCategory As Variant
  
  Application.Volatile True
  
  If Len(SheetName) = 0 Then
    Set rCaller = Application.Caller ' cell containing UDF
    SheetName = rCaller.Parent.Name
  End If
  
  On Error Resume Next
  Set wks = Worksheets(SheetName)
  On Error GoTo 0
  If wks Is Nothing Then
    sError = "Worksheet '" & SheetName & "' not found"
    GoTo ErrorFunction
  End If
  If wks.ChartObjects.Count = 0 Then
    sError = "No charts found on worksheet '" & SheetName & "'"
    GoTo ErrorFunction
  End If
  
  If Len(ChartName) = 0 Then
    ChartName = wks.ChartObjects(1).Name
  End If
  
  On Error Resume Next
  Set cht = wks.ChartObjects(ChartName).chart
  On Error GoTo 0
  If cht Is Nothing Then
    sError = "Chart '" & ChartName & "' not found on worksheet '" & SheetName & "'"
    GoTo ErrorFunction
  End If
  
  Select Case LCase$(X_or_Y)
    Case "x", "1", "category", "cat"
      X_or_Y = xlCategory
      '' but not for non-value axes
    Case "y", "2", "value", "val"
      X_or_Y = xlValue
  End Select
  
  Select Case LCase$(Primary_or_Secondary)
    Case "primary", "pri", "1"
      Primary_or_Secondary = xlPrimary
    Case "secondary", "sec", "2"
      Primary_or_Secondary = xlSecondary
  End Select
  
  Set ax = cht.Axes(X_or_Y, Primary_or_Secondary)
  
  If ax.Type = xlCategory Then
    On Error Resume Next
    vTestCategory = ax.MinimumScale
    iError = Err.Number
    On Error GoTo 0
    If iError <> 0 Then
      sError = "Cannot scale a category-type axis"
      GoTo ErrorFunction
    End If
  End If
  
  If IsNumeric(Minimum) Or IsDate(Minimum) Then
    dMinimum = Minimum
    bSetMin = True
  Else
    Select Case LCase$(Minimum)
      Case "auto", "autoscale", "default"
        ax.MinimumScaleIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        Minimum = "null"
        ' make no change
    End Select
  End If
  
  If IsNumeric(Maximum) Or IsDate(Maximum) Then
    dMaximum = Maximum
    bSetMax = True
  Else
    Select Case LCase$(Maximum)
      Case "auto", "autoscale", "default"
        ax.MaximumScaleIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        Maximum = "null"
        ' make no change
    End Select
  End If
  
  If bSetMin And bSetMax Then
    If dMaximum <= dMinimum Then
      sError = "Maximum must be greater than Minimum"
      GoTo ErrorFunction
    End If
  End If
  
  If bSetMin Then
    ax.MinimumScale = dMinimum
  End If
  
  If bSetMax Then
    ax.MaximumScale = dMaximum
  End If
  
  If IsNumeric(MajorUnit) Then
    If MajorUnit > 0 Then
      ax.MajorUnit = MajorUnit
    End If
  Else
    Select Case LCase$(MajorUnit)
      Case "auto", "autoscale", "default"
        ax.MajorUnitIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        MajorUnit = "null"
        ' make no change
    End Select
  End If
  
  If IsNumeric(MinorUnit) Then
    If MinorUnit > 0 Then
      ax.MinorUnit = MinorUnit
    End If
  Else
    Select Case LCase$(MinorUnit)
      Case "auto", "autoscale", "default"
        ax.MinorUnitIsAuto = True
      Case "null", "skip", "ignore", "blank"
        ' make no change
      Case ""
        MinorUnit = "null"
        ' make no change
    End Select
  End If
  
  PT_ScaleChartAxis = "Sheet '" & SheetName & "' Chart '" & ChartName & "' " _
      & Choose(Primary_or_Secondary, "Primary", "Secondary") & " " _
      & Choose(X_or_Y, "X", "Y") & " Axis " _
      & "{" & Minimum & ", " & Maximum & ", " & MajorUnit & ", " & MinorUnit & "}"
  
ExitFunction:
  Exit Function
  
ErrorFunction:
  PT_ScaleChartAxis = sError
  GoTo ExitFunction
End Function
Custom Autoscaling

People often would like their own axis autoscaling algorithms over Excel’s. For example, Excel uses 0 as the axis minimum if the minimum in the data is less than 5/6 of the maximum in the data (see How Excel Calculates Automatic Chart Axis Limits for details). You could write your own algorithms in the worksheet, based on data used in the chart, or you could use my own algorithm from Calculate Nice Axis Scales in Your Excel Worksheet, and use the results in your formula that calls the chart UDF above.

I’ve added another UDF to Peltier Tech Charts for Excel 3.0 which uses my axis scale algorithm from Calculate Nice Axis Scales in Excel VBA. It looks like this:

=PT_AutoScaleChart(SheetName,ChartName,PriX,PriY,SecX,SecY)

Specify the sheet and chart names, then for each axis, specify

  • 1, “autoscale”, “auto”, or TRUE to apply the custom autoscaling
  • 0, “ignore”, “skip”, or FALSE to make no change to the axis
  • -1, “default”, or “reset” to apply Excel’s default autoscaling

This function is also integrated into IntelliSense and the Function Arguments dialog. PriX and PriY are required, while SecX and SecY are optional. The function returns a descriptive message, indicating success or error.

The post Chart UDF to Control Axis Scale appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
My Big Fat Legend Keys

A client had a combination chart in his report, with some columns and some lines, and was complaining about the legend. It seems that when he added the lines, the column entries in the legend had a wide rectangle showing the series color, rather than a square, so the legend was taking up way too much space. The left hand chart below shows this fat-legend problem, while the right hand chart has no problem because I’ve fixed it.

Normally, the fill-type of series (column charts, area charts, bar charts) use a simple square for the legend key, the colored part of the legend entry that helps the user determine which series is which.

In contrast, line charts have a wider legend key, which allow for the display of a marker and a line.

The problematic charts are mixed types. Wider legend keys are needed to show the lines for the line chart types, and to accommodate the added width, the squares for the fill-type charts are widened.

And I thought, wouldn’t we all like to be a little thinner? So I played around a bit, and I’ve found a way to fix this.

First, I noticed that if the line charts had markers but no lines, the legend keys were only as wide as the markers.

So I played a bit more, and discovered that I could format the whole line series to have no lines, but then format the individual points to have lines, and the legend would only show the whole series format, not the individual point format.

Fix the Fat Legend Keys Manually

I’ll show you the manual protocol first, then I’ll share some VBA that will remove the drudgery of it all.

We’ll start with a combination chart with columns and one line series (top left).

The first step is to format the line chart series with markers but no lines (top right).

We select the first point, and format it to have a line, which does not appear, because it connects the first point to the point before that, which doesn’t exist (middle left).

We then select the second point, and format it also to have a line, which is now visible (middle right).

We select the third point and repeat (bottom left).

Finally, we repeat this for the rest of the points (bottom right).

That wasn’t so hard, was it?

What if the line chart has no markers? Well, it’s almost the same. Again we start with a combination chart with columns and one line series (top left).

The first step is to format the line chart series, adding markers but removing the lines (top right).

We select the first point, and format it to have a line but no marker. Neither the line nor the marker appear (middle left).

We then select the second point, and format it also to have a line but no marker (middle right).

We select the third point and repeat (bottom left). See where this is going?

Finally, we repeat this for the rest of the points (bottom right).

Fix the Fat Legend Keys Programmatically

So the protocol isn’t too hard, but you can just tell it’s going to get tedious when there are a lot of charts to process. So I’m here to streamline the process with a little VBA.

The core procedure inputs one line chart series and handles it as above. It first formats the entire series with a marker and no line. If the series originally had no marker, it temporarily uses a square; otherwise it uses whatever marker was present. Then it formats each point in turn to have a line, and if it originally had no marker, it removes the marker from that point.

Sub RemoveLegendLinesFromSeries(srs As Series)
  Dim iPt As Long, nPts As Long
  Dim bNoMarkers As Boolean
  
  nPts = srs.Points.Count
  Select Case srs.ChartType
    Case xlLine, xlLineStacked, xlLineStacked100
      bNoMarkers = True
    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100
      bNoMarkers = False
    Case Else
      GoTo ExitSub
  End Select
  
  If bNoMarkers Then
    srs.MarkerStyle = xlMarkerStyleSquare
  End If
  
  srs.Format.Line.Visible = msoFalse
  
  For iPt = 1 To nPts
    With srs.Points(iPt)
      .Format.Line.Visible = True
      If bNoMarkers Then
        .MarkerStyle = xlMarkerStyleNone
      End If
    End With
  Next
  
ExitSub:

End Sub

The next procedure inputs an entire chart, doing each line chart series and ignoring the others.

Sub RemoveLegendLinesFromChart(cht As Chart)
  Dim srs As Series
  
  For Each srs In cht.SeriesCollection
    Select Case srs.ChartType
      Case xlLine, xlLineStacked, xlLineStacked100, xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100
        RemoveLegendLinesFromSeries srs
      Case Else
        ' nope
    End Select
  Next
End Sub

We have two entry-point procedures: the first handles the active chart.

Sub RemoveLegendLinesFromActiveChart()
  RemoveLegendLinesFromChart ActiveChart
End Sub

The second entry-point procedure figures out what the user has selected, and acts accordingly. If the user has selected a series, a point, a set of data labels, or a single data label, the procedure processes just the applicable series. If the user has selected a chart (i.e., there is an active chart, but none of the previously listed elements are selected), the procedure takes care of the whole chart. If the user has selected multiple charts, then all charts are handled. In fact, since this procedure handles an active chart, the previous procedure isn’t needed after all.

Sub RemoveLegendLinesFromSelection()
  Dim sh As Shape
  
  If Not ActiveChart Is Nothing Then
    Select Case TypeName(Selection)
      Case "Series"
        RemoveLegendLinesFromSeries Selection
      Case "DataLabels", "Point"
        RemoveLegendLinesFromSeries Selection.Parent
      Case "DataLabel"
        RemoveLegendLinesFromSeries Selection.Parent.Parent
      Case Else
        RemoveLegendLinesFromActiveChart
    End Select
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    For Each sh In Selection.ShapeRange
      If sh.HasChart Then
        RemoveLegendLinesFromChart sh.Chart
      End If
    Next
  End If
End Sub

I selected the mixed charts from the screenshot in the first section above, and ran this last procedure, and in an instant, the legend keys were brought down to size.

The post Slim Down Those Fat Legend Keys appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Risk Matrix Charts

A risk matrix is a graphical way to analyze risks and benefits of a company’s potential actions. The actions might be a company’s decision about which market to enter or which other company to acquire. When I was working in corporate R&D, we used a risk matrix to decide which projects to allocate our resources on. Risk is usually placed on the horizontal axis, and is a measure of the cost of an acquisition or the estimated probability of success of a venture or project. Benefit is usually plotted on the vertical axis, and is a measure of success, such as anticipated revenues or cost savings.

The green and orange background reminds the viewer that the preferred options are in the top right of the chart. If the axes have the opposite preferred direction, for example, if the horizontal axes indicated cost of acquisition, then the grid could be produced with green in the top left corner and orange in the bottom right.

Sometimes pairs of points will be used, to show the risk-benefit analysis before and after certain mitigation steps are taken, with an arrow connecting the points to show the direction of the mitigation.

Data and Components of Risk Matrix Charts

The charts are not as complicated as they seem. The background is a stacked column chart on the primary axis using data like that shown below. The stacked columns are colored appropriately, and the gaps between them are eliminated.

You can use as large a grid as you like. We used to use a 3×3 grid, but 5×5 is also common.

The foreground is a simple scatter chart on the secondary axis with simple data and formatting.

For the paired data, a secondary axis scatter chart is also used, with arrow formatting of the connecting lines. Each pair of points has a separate data set.

I’ll show you how to construct this background and superimpose the foreground data.

This is a common request, so I’m considering risk matrix charts as an addition to Peltier Tech Charts for Excel.

Constructing the Risk Matrix Background

The first step in construction of the risk matrix is to select the column chart data (see image in above section) and create a stacked column chart (below left). Since there are more rows than columns, Excel plots by column, which means the columns aren’t stacked how we want. Fix this by clicking the Switch Row/Column button on the Chart Tools > Design tab of Excel’s ribbon (below right).

Format the stacked column series appropriately. I’ve used a set of fill colors described below the charts, with a thin white border (below left). Next, delete the legend (below right).

I made up my own green-yellow-orange color scheme, listed below.

Green 4: RGB(109,255,63)
Green 3: RGB(155,255,73)
Green 2: RGB(194,255,83)
Green 1: RGB(228,255,93)
Yellow: RGB(255,255,102)
Orange 1: RGB(255,238,77)
Orange 2: RGB(255,211,51)
Orange 3: RGB(255,184,25)
Orange 4: RGB(255,152,1)

You should also investigate the diverging map colors designed by Cynthia Brewer, found at Color Brewer.

Next I formatted both axes so the crossing axis crosses at the maximum value or category (below left). Then I formatted both axes so the line color is 50% gray. You could probably get away with a lighter shade of gray.

Then I deleted the horizontal gridlines (below left), and I hid the axis tick labels by setting the label position to None (below right).

Finally I set the gap width of the column chart series to 0% (below).

The risk matrix background is now ready to accept data.

Add Simple Risk Matrix Data

Starting with the finished background matrix above, copy the X-Y data for the secondary axis points, select the chart, and use Paste Special from the Paste dropdown on Excel’s Home tab, to add the data as a New Series, with Series in Columns, and with Categories in First Column and Series Names in First Row (below left).

This obliterates much of the chart, but that’s only temporary. Excel added the new series another set of stacked columns: right-click on the new series and choose Change Series Chart Type from the pop-up menu, and select XY Scatter. Excel automatically places the series on the secondary axis, and puts the two secondary axes on the bottom and left of the chart, since we’ve moved the primary axes to the top and right edges where secondary axes normally reside (below right).

Format both secondary axes to use 50% gray for line color (below left). Adjust the axis scales of these axes so the tick marks align nicely with the color grid: use 60,000 as the vertical axis major unit and 100% as the horizontal axis maximum (below right).

Finally apply a little formatting to the markers. I enlarged the markers to 7 pts, added a dark gray outline, and applied a blue fill (below).

You should add titles to the secondary axes and data labels to the points, as I did in the sample risk matrix charts at the beginning of this article.

Add Paired Risk Matrix Data, Connected by Arrows

Adding the more detailed paired data is much the same as adding simple data.

Starting with the finished background matrix above, copy the X-Y data for the first set of secondary axis points. Then select the risk matrix, and use Paste Special from the Paste dropdown on Excel’s Home tab, to add the data as a New Series, with Series in Columns, Categories in First Column, and Series Names in First Row (below left).

This obscures much of the chart, but we can fix that. Right-click on the new series and choose Change Series Chart Type from the pop-up menu, and select XY Scatter. Excel places the series on the secondary axis, and puts the two secondary axes on the bottom and left of the chart (below right).

Repeat the Copy-Paste Special sequence with the additional sets of risk matrix data (below left). Format both secondary axes to use 50% gray for line color (below right).

Adjust the axis scales of these axes so the tick marks align nicely with the color grid: use 60,000 as the vertical axis major unit and 100% as the horizontal axis maximum (below left).

Now format the risk matrix scatter chart series. Enlarge the markers to 7 pts, add a dark gray outline, and apply suitable color fills. Format the connecting lines to use a dashed dark gray line of suitable thickness (I used 1.5 pts), and apply an arrowhead to the end of the lines (below right).

Finally, label your axes and data points to clarify the meaning of the chart.

The post Risk Matrix Charts in Excel appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I’m in Australia for the third year in a row, the second year as part of the Unlock Excel conference. Sponsored by CAP Australia, Unlock Excel brings together six Excel MVPs to present new technologies, and review existing technologies, in front of leading accounting and finance professionals.

At this point, we have finished two-day conferences in Melbourne and Sydney, and we’re gearing up for the finale in Brisbane.

The Excel MVPs on this trip include Liam Bastick, Tim Heng, and Mynda Treacy from Australia, Gašper Kamenšek from Slovenia, Ken Puls from Canada, and myself, Jon Peltier from the United States. Liam and Tim are financial modelers at the consulting firm SumProduct LLC, which also provides live and video training. Mynda and her husband Phil run My Online Training Hub, which hosts a blog and many online courses covering Excel dashboards, Power Query, Power Pivot, and Power BI.  Gašper runs the Excel Unplugged blog and offers advanced training in Excel, Power Query, Power Pivot, and Power BI through his new Excel Olympics website. Ken is an experienced author, trainer, and blogger on Excel and Power Pivot technologies and runs the Excel Guru site. I specialize in charting and VBA programming, through training, custom programming, and my Excel charting add-ins, and you can read my blog here at Peltier Tech.

The MVPs are covering Excel topics like Pivot Tables and Pivot Charts, Formatting and Styles, Tips and Tricks for Charts and for Excel, Range Names, Data Validation, Automating Charts and Reports without VBA, and Customizing Excel. They are discussing financial modeling, budgeting and variance analysis, modeling of working capital, and KPI reports. Business Intelligence topics include deep dives into Power Query, Power Pivot, Power BI, and Dashboards.

Microsoft has sent Annie Colonna, a Program Manager on the Excel team, to highlight some new features of Excel and to share insights into how Excel will continue to develop. Annie and the MVPs have had several rousing discussions with conference attendees in daily Q&A sessions.

The post Unlock Excel – Two Down, One to Go appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Gantt charts are a special kind of bar chart used in scheduling and program management. A set of tasks or activities is listed along the left hand axis, and the bottom axis shows dates. Horizontal bars indicate when each task begins and ends, and which tasks are in progress at any given time.

The simplest kind of Excel Gantt chart involves a worksheet range, with the tasks listed in the first column, and dates (for example, week beginning or ending dates) in the top row. If a date along the top falls between the start and end dates for that task, the cell in the same row as the task is shaded a different color.

It is not too difficult to create a regular bar chart showing this same amount of detail.

And once you’ve decided to make a chart, you can add more advanced embellishments to show more details.

This tutorial will show you how to make all types of Gantt chart in Microsoft Excel.

Simple Gantt Charts in the Excel Worksheet

Let’s start with this simple Gantt chart data. Listed are tasks, beginning and ending dates, and duration. For this simple chart, we don’t even need the duration column.

We can expand the range to the right, with weekly dates in the top row of the added columns.

We can fill the cells in manually, of course, but it’s nice to set up conditional formatting so the colors update whenever the beginning or ending dates change. For each cell, the formula checks whether the date at the top of the column is greater than or equal to (on or after) the starting date and less than (before) the ending date for that row. If so, then the cell is formatted as shown.

The result is a quick little graphic, easily done right in the worksheet, so the Gantt chart timelines line up with the task rows.

It’s not fancy, but sometimes all you need is something quick.

Simple Excel Gantt Charts

To make a Gantt chart from an Excel bar chart, we can start with the following simple data: Task, Start Date, End Date, and Duration.

Since dates and duration are interrelated, usually Start is entered, and either End or Duration.

If Start and Duration are entered, End is calculated as =Start+Duration.

If Start and End are entered, Duration is calculated as =End-Start.

The small range to the right will come in handy  when fixing our date axis limits in the bar chart. The upper blue-shaded cell contains a formula that returns the minimum of the Start Dates, and the cell above this is seven days earlier. The lower blue-shaded cell has a formula that returns the maximum of the End Dates, and the cell below that is seven days later. The second column shows these dates in General number format, so we see just the serial number of the date (the number of days since 1/1/1900); we will use the yellow shaded cells as the limits of the chart’s date axis.

Before we make the chart, we should apply a special number format to the Start Dates so they fit closer along the axis. Select the dates, click Ctrl+1 to format them, and on the Number tab, choose Custom, and in the Type box, enter d (for day), type Ctrl+J to enter a line feed right in the format, then enter mmm (for three letter month abbreviation) and press Ente. This produces two-line dates with the day number on the first line and the month abbreviation on the second. We can change this back later, but it’s easier to create this format in the worksheet than to try to achieve it in the chart.

Also, clear the top left cell, so Excel plots the data the way we need it plotted.

To create the simple Gantt chart, we start by selecting the columns with Task, Start Date, and Duration, and inserting a stacked bar chart. Notice the date format which was taken from the worksheet range.

The tasks are listed in the opposite order, so we need to format the vertical axis (select it and press Ctrl+1 or simply double click on it). Check ‘Categories in Reverse Order’ and ‘Horizontal Axis Crosses at Maximum Category’ to correct this. See Why Are My Excel Bar Chart Categories Backwards? and Excel Plotted My Bar Chart Upside-Down to read all about it.

Now we need to format the date axis, so select the axis and press Ctrl+1. Change the minimum and maximum to 43184 and 43289 (from the special range we set up besides the main data range), and set the major unit to 7. Under Number, uncheck ‘Linked to Source’, and then you can reset the date formats in the data range.

Finally, a little clean up. Format the Start bars to have no fill color, and give the Duration bars an appropriate fill. I like to use 25 or 50% transparency, so the fill isn’t overpoweringly dark and so the gridlines show through. Also delete the legend.

That wasn’t too hard now, was it?

Advanced Excel Gantt Charts

Now that we know how to make a Gantt chart with a regular Excel chart, we can think of ways to enhance it. How about duration bars that show the fraction complete? How about milestone markers coded to show finished and unfinished tasks? And maybe a vertical line to indicate a date of interest?

The data range is a bit more complicated.

The user enters Start Date, % Complete, and either Duration or End Date. % Complete could also be computed from the Start Date, End Date, and the date of interest (indicated in the block labeled “Vertical”).

If the user enters Duration, the End Date formula in G3 is =C3+F3.

If the user enters End Date, the Duration formula in F3 is =G3-C3.

Duration is split into Done and Not Done components with two simple formulas.
The formula in D3 is =F3*K3
The formula in E3 is =(1-F3)*K3

The Milestones formula in J3 is =(ROW()-ROW(J$2)-0.5)/ROWS(J$3:J$11), which provides the milestone series with a vertical position to align with the bars.

The Finished milestones formula in H3 is =IF(K3=1,J3,NA())

The Unfinished milestones formula in I3 is =IF(K3<1,J3,NA())

All of these formulas are copied down to row 11.

The block to the left below the main table will help with setting the date axis scale later.
Cell C14 has the formula =MIN(C3:C11)
Cell C15 has the formula =MAX(G3:G11)
Cell C13 has the formula =C14-7
Cell C16 has the formula =C15+7
Cells D13 through D16 link to C13 through C16, but are formatted as General to show the serial number of the date.

The date listed next to ‘Vertical’ is where we will draw a vertical line indicating our interest in that date.

Before we insert our chart, let’s apply a temporary special number format to the Start Dates in C3:C11. Select the range, click Ctrl+1 to format the cells, and on the Number tab, choose Custom, and in the Type box, enter d (for day), type Ctrl+J to enter a line feed right in the format, then enter mmm (for three letter month abbreviation), and press Enter. We will change this back later, but it’s easier to create this in the worksheet than to try to achieve it in the chart.

Note also that cell B2 has been cleared, to help Excel understand how to plot the data.

The chart starts out pretty much like the simple Gantt chart above. Select the first four columns containing data for Tasks, Start Date, Done, and Not Done, and insert a stacked bar chart.

Format the vertical axis (select it and press Ctrl+1 or simply double click on it). Check ‘Categories in Reverse Order’ and ‘Horizontal Axis Crosses at Maximum Category’ to put the tasks in the order we want.

Now change the time scale on the horizontal axis. Change the minimum and maximum to 43184 and 43289 (from C13 and C16 in the special range we set up below the main data range), and set the major unit to 7. Under Number, uncheck ‘Linked to Source’, and then you can reset the date formats in the data range.

Now we can format the bars. I’ve made the Start series invisible by using no fill color; I’ve colored the Done bars blue and the Not Done bars orange, with 25% transparency on each so the gridlines appear through them.

At this point, we depart from the procedure for a simple Excel Gantt chart, by adding the Milestone data. Select and copy G2:I11 (End Date, Finished, and Unfinished), select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, and add the data as new series, with series in columns, category labels in first column, and series names in first row. The data is added as new bars stacked on the Done and Not Done bars.

Right click on any series in the chart, and choose Change Series Chart Type from the pop-up menu. Change Finished and Unfinished to XY Scatter. Excel automatically assigns them to the secondary axis group and adds a secondary vertical axis.

We need to switch the new axis: select it and press Ctrl+1, and check ‘Values in Reverse Order’.

Now we can hide the secondary vertical axis (the one we just reversed). While formatting it, under Labels, change Label Position to None.

Now let’s format those milestones. I’ve used black-bordered diamonds for both, with black fill for Finished and white fill for Unfinished.

Now let’s add the data for the vertical reference date line. Select and copy G13:H14 (which includes the “Vertical” label), select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, to add the data as a new series, with series in columns, category labels in first column, and series names in first row. The data is added as a new single point along the top of the chart.

Select this new point, and choose Error Bars from the menu you get when you click the plus icon floating beside the chart. Microsoft calls these little icons “Skittles”.

You might not even see them, but the point now has vertical and horizontal error bars. Selecting them is a bit of a challenge: you can right click on the chart, and click the Chart Element dropdown to see all of the chart elements.

Or you can click the Current Selection dropdown at the top left corner of the Chart Tools > Format tab of the ribbon.

Select the ‘Series “Vertical” X Error Bars’, and press the Delete key. Then select the ‘Series “Vertical” Y Error Bars’, and press Ctrl+1 to format them. Select Plus (the bar will point downward, but we just reversed the order of the axis, remember?), No Cap, and assign it a Fixed Value of 1. Then format the line color (I used a green lin).

Select the Vertical data point again, and using the plus skittle, check Data Labels. Format the data label to show X value, not Y value, and assign a number format of “d mmm”. The space between day and month is fine, no need to try to insert that pesky linefeed.

And now a little more clean-up. I made the error bar thicker, gave the “Vertical” data label (“1 Jun”) a white fill with 25% transparency, and formatted the Vertical to use no marker at all. I deleted the legend, because it’s no longer needed.

There are a few additional labeling embellishments you might use, shown below. I’ve hidden the task labels on the vertical axis (change Label Position to None), and added data labels to the invisible Start Date bar chart series. I am using the Category Name option instead of Value, and the Inside End Label Position. This puts the task names to the left of the visible floating bars.

Then I added data labels to the Finished and Unfinished markers. In both cases, I changed to the Value from Cells option, using the % Complete values in K3:K11.

I’ve given all of these labels a white fill background with 25% transparency.

The post Gantt Charts in Microsoft Excel appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This is the second year that CPA Australia’s Unlock Excel visits Australia and New Zealand with two-day conferences by Microsoft Excel MVPs.

Unlock Excel Venues

The 2018 Unlock Excel conference will take place in these cities:

Who Should Attend

While CPA Australia focuses on accounting and finance professionals, Unlock Excel is geared towards people who want to unlock the full potential of their data. You will enhance your Excel skills, while becoming more efficient, exploring new tools and applications, and streamlining your existing processes.

What You Will Learn

Unlock Excel will feature a range of topics including charts and dashboards, financial modelling, VBA, and Power BI. You will learn valuable time-saving techniques to take your current knowledge to the next level. You will also learn to corral your own intricate data sets, and uncover unique insights. This will add value to discussions and analyses and drive effective data-based decision-making for your business.

Microsoft MVPs

Microsoft chooses MVPs annually based on their high level of public community contributions, not just quantity, but high quality contributions by technical experts in their field. Excel MVPs discover the best ways to use Excel and other Microsoft products, and are passionate about sharing those techniques with the world.

This year’s MVPs include Ken Puls, Liam Bastick, Mynda TreacyGašper Kamenšek, Ingeborg Hawighorst (in New Zealand only), and yours truly Jon Peltier.

How do you sign up

Visit the CPA Australia site for more information and to register.

The post The Return of Unlock Excel appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Question: Extract a Category Label for a Point

I came across a question in the Excel Reddit, Is there a way to select a chart series point and have the label name of that point be copied into a cell?

Answer 1: Chart with Simple Axis

The short answer is the following function: you get .XValues property of the series, which is an array of category labels, and find the element of the array for the particular point:

Function GetCategoryLabel(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.XValues
  GetCategoryLabel = vCats(iPtNum)
End Function

You call it like this, passing in the chart, series number, and point number:

Sub TEST_GetCategoryLabel()
  Dim s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  s = GetCategoryLabel(cht, iSrs, iPt)
  s = "Category Label for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  MsgBox s
End Sub

This doesn’t stick the label into a cell, like the question asked, but displays the label with a message box. As shown below, Series 1 – Point 3 has the label “C”:

Answer 2: Chart with Multi-Tier Category Labels

It turns out that the chart was a pivot chart, based on a pivot table with several fields in the rows area. Each field contributes a tier of labels to the category axis. The screenshot below shows a Table of data, a Pivot Table based on this Table, and above both a Pivot Chart, with a two-tiered category axis.

If you use the simple approach above, the .XValues don’t distinguish among the tiers, and the labels from different tiers are put together, separated by spaces. In the chart below, Series 1 – Point 3 has the compound label “Alpha C”, according to the simple algorithm.

We need a much more detailed VBA approach. We have to parse the series formula, get the address of the range containing the category labels, then get the corresponding range, figure out how many columns of labels there are, then find the label in each column corresponding to the point. This is complicated by the empty cells in the pivot fields, so we have to keep looking upward until we find a cell with a label.

Here is the more intricate function that extracts an array of category labels:

Function GetCategoryLabels(cht As Chart, iSrsNum As Long, iPtNum As Long) As Variant
  Dim srs As Series
  Dim sFmla As String, vFmla As Variant
  Dim rCats As Range, vCats As Variant, vOutput As Variant
  Dim iRow As Long, iCol As Long, nRows As Long, nCols As Long
  
  Set srs = cht.SeriesCollection(iSrsNum)
  
  sFmla = srs.Formula
  sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
  vFmla = Split(sFmla, ",")
  
  Set rCats = Range(vFmla(LBound(vFmla) + 1))
  vCats = rCats.Value2
  
  ReDim vOutput(1 To UBound(vCats, 2))
  For iCol = 1 To UBound(vCats, 2)
    For iRow = iPtNum To 1 Step -1
      If Len(vCats(iRow, iCol)) > 0 Then
        vOutput(iCol) = vCats(iRow, iCol)
        Exit For
      End If
    Next
  Next
  
  GetCategoryLabels = vOutput
End Function

It’s called in a similar way:

Sub TEST_GetCategoryLabels()
  Dim v As Variant, i As Long, s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  v = GetCategoryLabels(cht, iSrs, iPt)
  
  s = v(LBound(v))
  For i = LBound(v) + 1 To UBound(v)
    s = s & ", " & v(i)
  Next
  
  s = "Category Labels for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  
  MsgBox s
End Sub

When we put all the category fields into the rows area of the pivot table, we get three labels for Series 1 – Point 3, “Alpha”, “A”, and “III”:

When we put the fields “Greek” and “Latin” into the rows area of the pivot table and move “Roman” to the columns area, we get two labels for Series 1 – Point 3, “Alpha” and “C”:

When we leave the field “Greek” in the rows area of the pivot table and put “Latin” and “Roman” to the columns area, we get a single label for Series 1 – Point 3, “Gamma”:

The post Extract Labels from Category Axis in an Excel Chart (VBA) appeared first on Peltier Tech Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Ctrl+Select In Excel

When selecting cells and areas in an Excel worksheet, you can select one cell or area with the mouse, then hold the Ctrl key while selecting another cell or area, and the new selection is added to the previous selection. You can select multiple areas at once using this Ctrl+Select sequence.

This is great, until you select one or more cells you didn’t mean to select, or if you just want to deselect something you’ve selected. In Excel worksheets, you can’t use the Ctrl key to unselect something that you’ve selected previously. This is a pain, because you have to start again with your multiple region selection, and hope you don’t mess it up again.

This has been the behavior since Excel 1950 for Univac was introduced.

Ctrl+Select Everywhere Else

In every other application I can think of, when you are selecting objects, you can use Ctrl+Select to add to the collection of selected objects. And if you Ctrl+click on a selected object, it is deselected. This works on objects in PowerPoint, shapes embedded in Word, files and folders displayed in Windows Explorer.

People have grumbled about the inability to deselect a cell in Excel for a long time, and it even has received an entry in Excel UserVoice: Unselecting cells when using Ctrl to select multiple cells.

The New Ctrl+Select Behavior in Excel

Well, the Excel Team was listening, and not long ago they announced that it is possible to Deselect a selection in Excel. According to that announcement, it’s still only available to Insiders, who risk all to get the latest builds with the latest and greatest new features and the occasional “Gotcha!” Read about Office Insiders and What is Office Insider? on the Microsoft Office web site.

I knew that this suggestion had been made in UserVoice, and I might even have heard that Microsoft was working on it. So one day, when I was tooling away as usual, I incorrectly selected some cells, and thought to myself, “When will they ever let me Ctrl+Select to unselect that stupid cell over there?” And I Ctrl+Selected that stupid cell over there, and it was UNSELECTED! Naturally I didn’t really believe it, so I Ctrl+Selected and Ctrl+Deselected that cell about twelve times.

It’s such a seemingly small change, but it removes a frequent and annoying source of frustration.

How Ctrl+Select and Ctrl+Deselect Work

Suppose I have a range of data like that below left, and for some reason I want to select the header row and the rows with even X values. I start by selecting the header row and the row with X = 0.

Now I hold Ctrl and select the row with X = 2 (below left). That’s how Ctrl+Select works.

Then I hold Ctrl and select the row with X = 4, except I’m clumsy and also select the row with X = 5 (below right).

Ordinarily I would just curse at my poor mouse-eye coordination, and go back to the first selection above. But now I can curse, then hold Ctrl and select the row with X = 5. Or rather, DESELECT the row with X = 5 (below left); the resulting selection is shown below right. That’s how Ctrl+Deselect works.

Now I can Ctrl+Select the row with X = 6 (below left), and do what I want with the selected range, which apparently was shading the cells light gold (below right).

Want More Examples?

So I was playing around to suss out the behavior more fully, and I present a few more examples. They all start with a simple rectangular range selected, C2:G6.

Below left, I’m holding Ctrl while selecting part of the previously selected range, C4:E6. The resulting selection is comprised of the two rectangular areas C2:G3 and F4:G6.

So I learned if the cell you click at the beginning of a Ctrl+Select operation is selected, you will deselect any cells in the range you are currently selecting. I selected B3:F7, starting within the previously selected range at cell F3 (shown below left), so all cells in B3:F7 end up not selected (below right).

If the cell you click at the beginning of a Ctrl+Select operation is not selected, you will select all cells in the range you are currently selecting, and no cells become deselected. I selected B3:F7, starting outside the previously selected range at cell B3 (shown below left), so all cells in B3:F7 end up selected, overlapping the original selection of C2:G6 (below right).

If I then Ctrl+Select D4:E5, which is completely enclosed by the previous selection, that small square region is excised from the selection.

The new selection is easier to see if I then shade the selected cells below.

Finally, it’s impossible to unselect all cells on the active sheet. Well, you can, if you select an object on the sheet, such as a shape, a picture, or a chart. But an active worksheet has an active cell.

Below left, C2:G6 was selected, and I am Ctrl+Selecting the same range, from the bottom left corner to the top right. The new active cell is C6, the first cell of my last Ctrl+Select.

Pretty cool, isn’t it?

A Plug for Excel UserVoice

This is the third UserVoice success story I’ve written about, joining Plot Blank Cells and #N/A in Excel Charts and User Voice Fixes Pivot Table Default Settings. There have been several other UserVoice suggestions implemented by the Excel team, as well as numerous being planned and under review.

UserVoice is a great way to make suggestions about issues or new features in Excel. Don’t just start by making a suggestion, though. Search for your idea, and if it’s already there, vote for it and add a comment. Ideas are grouped by platform (Windows, Mac, Online, etc.) and by topic (Charting, Formatting, Pivot Tables, etc.). Among other criteria, when evaluating user feedback, the team looks for ideas with lots of votes.

The post Ctrl+Select in Excel Gets Smart appeared first on Peltier Tech Blog.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview