The PHI function calculates a number of the density function for a standard normal distribution.

Formula in cell C3:

=PHI(B3)

PHI(*x*)

x |
Required. x is the number for which you want to calculate the density of the standard normal distribution. |

The PHI function returns:

- #NUM# error value if the argument is an invalid numeric value.
- #VALUE! error value if the argument is an invalid data type, like a non numeric value.

The following chart shows the density values for a normal distribution where x is between -4 and 4.

]]>The PDURATION function calculates how many periods required by an investment to reach a given amount based on a percentage rate.

Formula in cell C7:

=PDURATION(C3,C4,C5)

PDURATION(*Rate, Pv, Fv*)

Rate |
Required. Rate is the interest rate per period. |

Pv |
Required. Pv is the present amount of the investment. |

Fv |
Required. Fv is the desired future amount of the investment. |

The PDURATION function returns

- #VALUE error if invalid data types are used.
- #NUM error if argument values are invalid.

Positive values are required as arguments.

The following formula calculates the exact same thing as the PDURATION function.

Formula in cell C5:

=(LOG(C5)-LOG(C4))/LOG(1+C3)

This formula explains in depth how the PDURATION function works.

=(LOG(*Fv*)-LOG(*Pv*))/LOG(1+*Rate*)

]]>The CONCATENATE function lets you add text strings into one single text string. The function has been replaced by the CONCAT function in Excel 2016, although it still exists for compatibility with earlier Excel versions.

The CONCATENATE function may be removed in a future Excel version, I recommend you use the CONCAT function or the more advanced TEXTJOIN function instead.

Formula in cell D3:

=CONCATENATE(B3,B4,B5,B6)

You cannot use a cell range as an argument, only cell references to single cells are allowed.

Press and hold the CTRL key to quickly select multiple cells while entering arguments in the CONCATENATE function.

CONCATENATE(*text1*, *[text2]*)

text1 |
Required. The argument can be a text value, number, or cell reference. |

[text2] |
Optional. You can have up to 255 additional text strings to join. |

The new TEXTJOIN function is a lot more versatile and easier to use, however, it is only available in Excel 365.

There is a workaround available for earlier Excel versions:

Quickly concatenate values into one cell [No VBA]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

Use the following formula to convert a column number to a column letter:

=LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703}))

The formula is entered in cell D3 shown in the image above, the column number is in cell B3.

You can follow along if you start the "Evaluate Formula" tool. You will find it on tab "Formula" on the ribbon.

Click "Evaluate" button to move to the next calculation step.

The ADDRESS function returns a cell reference depending on what you use in the first (row) and second (column) argument.

The third argument lets you choose the type of cell reference the ADDRESS function returns. 4 is a relative cell reference.

ADDRESS(1, B3, 4)

becomes

ADDRESS(1, 1, 4)

and returns A1.

The column letters start with A and ends with XFD. A is column 1, AA is column 27, the first column reference that contains two letters.

AAA is the first column containing 3 letters and the corresponding column number is 703.

The MATCH function returns the position in the array of the largest value that is smaller than the lookup value (B3).

MATCH(B3, {1; 27; 703})

becomes

MATCH(1, {1; 27; 703})

and returns 1. The cell reference must have a single column letter.

LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703}))

becomes

LEFT("A1", 1)

and returns A in cell D3.

The following formula converts a column letter to the corresponding column number.

Formula in cell C3:

=COLUMN(INDIRECT(B3&"1"))

If you don't want to use the INDIRECT function because it is volatile and may cause your worksheet to slow down considerably if used extensively, use this array formula.

=MATCH(B3&"1",ADDRESS(1,COLUMN($1:$1),4),0)

If you rather use a regular formula, try this:

=MATCH(B3&"1",INDEX(ADDRESS(1,INDEX(COLUMN($1:$1),),4),),0)

The ampersand character & concatenates the value in cell B3 with 1.

The INDIRECT function converts the text string to a cell reference.

INDIRECT(B3&"1")

becomes

INDIRECT("A"&"1")

becomes

INDIRECT("A1")

and returns A1.

COLUMN(INDIRECT(B3&"1"))

becomes

COLUMN(A1)

and returns 1 in cell C3.

User defined function in cell C3:

=ColumnLetter(B3)

Function ColumnLetter(col As Integer) As String ColumnLetter = Split(Cells(1, col).Address, "$")(1) End Function

- Copy above custom function
- Go to VBA Editor (Alt+F11)
- Click "Insert" on the top menu
- Click "Module" to insert a module to your workbook
- Paste code into the code window
- Exit VBA Editor and return to Excel (Alt+Q)

To be able to use the user defined function next time you open your workbook you need to save the workbook as a macro-enabled workbook.

- Click "File" on the menu, or if you have an earlier version of Excel, click the office button.
- Click "Save As"
- Click file extension drop-down list

- Change the file extension to "Excel Macro-Enabled Workbook (*.xlsm)".

User defined function in cell C3:

=ColumnNumber(B3)

Function ColumnNumber(col As String) As Long ColumnNumber = Columns(col).Column End Function

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based on a cross-reference schedule.

The schedule has dates in row 2, however, the cell is formatted to show only the day number.

Names are in column A and there are no duplicate names. An X indicates that a date is selected for the given person, multiple x's in a sequence is a date range.

The array formula in cell B3 returns the correct number names needed to return all date ranges:

=IFNA(INDEX(Sheet1!$A$3:$A$7, MATCH(FALSE, COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2), 0)), "")

Array formula in cell C3:

=SMALL(IF((Sheet1!$B$3:$NB$7<>Sheet1!$C$3:$NC$7)*($B3=Sheet1!$A$3:$A$7), Sheet1!$B$2:$NB$2+1, ""), COUNTIF($B$3:B3, B3)*2-1)

Array formula in cell D3:

=SMALL(IF((Sheet1!$B$3:$NB$7<>Sheet1!$C$3:$NC$7)*($B3=Sheet1!$A$3:$A$7), Sheet1!$B$2:$NB$2, ""), COUNTIF($B$3:B3, B3)*2)

- Sheet1!$A$3:$A$7 is the cell reference to the names in the schedule.
- Sheet1!$B$3:$AA$7 and Sheet1!$C$3:$AB$7 are a cell reference to the X's in the schedule.

Two are needed for the formula to count the number of date ranges.

Note! The cell refs have the same size, however, the latter is offset by one column.

To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

Use "Evaluate Formula" on tab "Formula" on the ribbon to go through the steps.

COUNTIF($B$2:B2,Sheet1!$A$3:$A$7)

becomes

COUNTIF("Name", {"Graham Chapman"; "John Cleese"; "Eric Idle"; "Terry Gilliam"; "Michael Palin"})

and returns {0;0;0;0;0}. None of the names have been displayed in cells above, remember that I am right now showing the calculation steps in cell B3.

As soon as the first name has been displayed the same number of times as it has date ranges the formula continues to the second name.

We are now going to count how many date ranges there is in the schedule per row.

To count date ranges the formula needs to compare a cell with the next. To be able to compare all values in one calculation I use two cell ranges. The last cell range has the same size as the first, however, it is offset one column to the right.

(Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1

becomes

{0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0;0, 1, 1, 0, 0, 0, ... , 0}

Part of the array is displayed in cell range B8:X12 in picture below.

The amazing MMULT function allows you to sum numbers in an array per row or column. The earlier calculation step created an array that shows when a cell has a different value compared to the next cell.

That makes the array show when a date range starts and ends, to be able to count date ranges we must divide the sum of each row with 2.

(MMULT((Sheet1!$B$3:$AA$7 <> Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2)

becomes

({6; 6; 2; 6; 4}/2)

and returns {3; 3; 1; 3; 2}.

The array tells us that the first name has three date ranges, the second name has three date ranges and so on.

The image above shows that the first name has three date ranges, the second has 3, the third has 1, the fourth has 3, and the last name has 2 date ranges.

The calculation is correct.

The number of names displayed must match the corresponding number of date ranges.

To do that I compare the arrays, if they are equal the logical expression returns TRUE. If not equal it returns FALSE.

COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2)

{0;0;0;0;0}={3; 3; 1; 3; 2}

and returns {FALSE; FALSE; FALSE; FALSE; FALSE}.

The MATCH function allows you to identify the position of a specific value in an array or cell range, if the third argument in the MATCH function is 0 (zero) meaning EXACT match.

MATCH(FALSE, COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2), 0)

MATCH(FALSE, {FALSE; FALSE; FALSE; FALSE; FALSE}, 0)

and returns 1.

The INDEX function gets the first value in cell range Sheet1!$A$3:$A$7.

INDEX(Sheet1!$A$3:$A$7, MATCH(FALSE, COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2), 0))

becomes

becomes

INDEX({"Graham Chapman"; "John Cleese"; "Eric Idle"; "Terry Gilliam"; "Michael Palin"}, 1)

and returns "Graham Chapman" in cell B3.

This article explains how to substitute part of a formula across all cells in a worksheet. It is easier than you think, no VBA programming or formulas are needed.

The picture above demonstrates a simple example, the formula in cell B3 gets values below 5 from sheet 2 cell range B3:B12.

**Array formula in cell B3**

=SMALL(IF(Sheet2!$B$3:$B$12<5, Sheet2!$B$3:$B$12, ""), ROWS($A$1:A1))

I will now show you how to replace Sheet2 with Sheet3 in formulas, in all cells in Sheet1. Simply press CTRL and H to open the Find and Replace dialog box.

Click the "Options" button to see all settings.

Here you have the option to

- Search the entire workbook or just the active worksheet. I want to search the active worksheet so I change nothing.
- Match entire cell contents. Deselect the check box, I want to match specific strings in formulas.

Click in field "Find what:" and type Sheet2. Now click in field "Replace with:" and type Sheet3, then click on "Replace All" button.

This will find all instances of Sheet2 in all cells and replace them with Sheet3.

Click the "OK" button and then the "Close" button.

The array formula in cell B3 (Sheet1) changes to:

=SMALL(IF(Sheet3!$B$3:$B$12<5, Sheet3!$B$3:$B$12, ""), ROWS($A$1:A1))

Recommended article

Search all workbooks in a folder

Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

Simply select the cell range, press CTRL + H to open the Find and Replace dialog box.

The "Find and Replace" action will now be applied to cell range B3:B4.

**Array formula in cell B3 (Sheet1)**

=SMALL(IF(Sheet2!$B$3:$B$12<5, Sheet2!$B$3:$B$12, ""), ROWS($A$1:A1))

To replace only the first instance of a specific search string in the formula simply include more characters so it makes the search string unique.

Example, you want to replace Sheet2 with Sheet3 but only the first instance found in the formula.

Press CTRL + H to open the Find and Replace dialog box.

Don't forget to add the included characters in the "Replace with: " field as well.

String IF(Sheet2 is found in only one location in each cell, this will replace only the first instance of Sheet2.

=SMALL(IF(Sheet3!$B$3:$B$12<5, Sheet2!$B$3:$B$12, ""), ROWS($A$1:A1))

]]>I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by one, and gets data from each sheet copied to a master workbook.

This allows you to quickly merge data across multiple workbooks saving you a lot of time and effort. To be able to consolidate data you need to make sure that data in each sheet begins in cell A1.

The macro selects the current region based on cell A1 in each sheet, then copies the cell range to the master sheet in a new workbook.

For this to work the cells must be contiguous meaning there can't be blank rows or columns in the dataset.

The macro can, however, easily be modified to get a range based on the last non-empty cell in column A.

Sub CopWKBooksInFolder() Dim WS As Worksheet Dim myfolder As String Dim Str As String Dim a As Single Dim sht As Worksheet Set WS = Sheets.Add With Application.FileDialog(msoFileDialogFolderPicker) .Show myfolder = .SelectedItems(1) & "\" End With Value = Dir(myfolder) Do Until Value = "" If Value = "." Or Value = ".." Then Else If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then On Error Resume Next Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz" If Err.Number > 0 Then Else On Error GoTo 0 For Each sht In ActiveWorkbook.Worksheets If sht.Range("A1") <> "" Then Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row + 1 sht.Range("A1").CurrentRegion.Copy Destination:=WS.Range("A" & Lrow) End If Next sht End If Workbooks(Value).Close False On Error GoTo 0 End If End If Value = Dir Loop Cells.EntireColumn.AutoFit End Sub

- Copy above macro
- Go to VBA Editor (Alt+F11)
- Click "Insert" on the top menu
- Click "Module" to insert a module to your workbook
- Paste code into the code window
- Exit VBA Editor and return to Excel (Alt+Q)

To be able to use the macro next time you open your workbook you need to save the workbook as a macro-enabled workbook.

- Click "File" on the menu, or if you have an earlier version of Excel, click the office button.
- Click "Save As"
- Click file extension drop-down list

- Change the file extension to "Excel Macro-Enabled Workbook (*.xlsm)".

- Open the Macro dialog box (Alt + F11)

- Select
*CopWKBooksInFolder*. - Click "Run" button.
- A folder dialog box appears.

- Navigate to a folder you want to search.
- Click OK button.
- The macro starts opening workbooks, one by one, copying values to a master worksheet.

The picture above demonstrates a master worksheet, even the headers are copied to the worksheet.

The following macro copies all data from the first opened workbook and worksheet, worksheets after that ignores the header row.

There is only one header row in the picture above.

Sub CopWKBooksInFolder() Dim WS As Worksheet Dim myfolder As String Dim Str As String Dim a As Single Dim sht As Worksheet Set WS = Sheets.Add With Application.FileDialog(msoFileDialogFolderPicker) .Show myfolder = .SelectedItems(1) & "\" End With chk = 0 Value = Dir(myfolder) Do Until Value = "" If Value = "." Or Value = ".." Then Else If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then On Error Resume Next Workbooks.Open Filename:=myfolder & Value, Password:="zzzzzzzzzzzz" If Err.Number > 0 Then Else On Error GoTo 0 For Each sht In ActiveWorkbook.Worksheets If sht.Range("A1") <> "" Then Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row + 1 If chk = 0 Then sht.Range("A1").CurrentRegion.Copy Destination:=WS.Range("A" & Lrow) chk = 1 Else Set crng = sht.Range("A1").CurrentRegion Set crng = crng.Offset(1, 0) Set crng = crng.Resize(crng.Rows.Count - 1) crng.Copy Destination:=WS.Range("A" & Lrow) End If End If Next sht End If Workbooks(Value).Close False On Error GoTo 0 End If End If Value = Dir Loop Cells.EntireColumn.AutoFit End Sub

This macro ignores header rows except the first one, as well. It also allows you to copy data from worksheets whose names contain a specific text string.

The the macro allows you to select a cell range containing search paths to folders you want to search.

Sub CopWKBooksInFolder() Dim WS As Worksheet Dim myfolder As String Dim Str As String Dim a As Single Dim sht As Worksheet Str = Application.InputBox(prompt:="Search only sheet names containing this string:", Title:="Search worksheet whose name contain this string:", Type:=2) On Error Resume Next Set Rng = Application.InputBox(prompt:="Select a cell range containing paths to folders" _ , Title:="Select a cell range", Default:=ActiveCell.Address, Type:=8) On Error GoTo 0 Set WS = Sheets.Add For Each cell In Rng If Dir(cell.Value, vbDirectory) <> "" Then chk = 0 Value = Dir(cell.Value) Do Until Value = "" If Value = "." Or Value = ".." Then Else If Right(Value, 3) = "xls" Or Right(Value, 4) = "xlsx" Or Right(Value, 4) = "xlsm" Then On Error Resume Next Workbooks.Open Filename:=cell.Value & Value, Password:="zzzzzzzzzzzz" If Err.Number > 0 Then Else On Error GoTo 0 For Each sht In ActiveWorkbook.Worksheets If InStr(sht.Name, Str) <> 0 Then If sht.Range("A1") <> "" Then Lrow = WS.Range("A" & Rows.Count).End(xlUp).Row + 1 If chk = 0 Then sht.Range("A1").CurrentRegion.Copy Destination:=WS.Range("A" & Lrow) chk = 1 Else Set crng = sht.Range("A1").CurrentRegion Set crng = crng.Offset(1, 0) Set crng = crng.Resize(crng.Rows.Count - 1) crng.Copy Destination:=WS.Range("A" & Lrow) End If End If End If Next sht End If Workbooks(Value).Close False On Error GoTo 0 End If End If Value = Dir Loop End If Next cell Cells.EntireColumn.AutoFit End Sub]]>

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell range B3:C6 and E3:F6.

If a record exists in both tables only one record is returned by the formula. If a record exists multiple times in one table only one record is returned by the formula.

Example, John 42 exists in both tables, however, the formula returns only one instance of John 42.

Laura 26 exists multiple times but only in the first table, the formula returns only one record of Laura 26.

**Array formula in cell B9:**

=IFNA(INDEX($B$3:$C$6, MATCH(0, COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6), 0),COLUMNS($A$1:A1)), INDEX($E$3:$F$6,MATCH(0, COUNTIFS($B$8:B8, $E$3:$E$6, $C$8:C8, $F$3:$F$6), 0), COLUMNS($A$1:A1)))

To enter an array formula, type the formula in cell B9 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

Unique distinct records are all records except duplicates merged into one distinct value.

In other words, duplicate records are removed.

Use the "Evaluate Formula" tool to examine the calculation steps in greater detail.

Go to tab "Formula" on the ribbon, click "Evaluate Formula" button to start the tool.

Then click the "Evaluate" button to see the next step in the calculation, this will make it easier to understand how the formula works.

The COUNTIFS function allows you to count how many times a record exists in a table.

The previous values in cell B9 are the values in B8 and C8.

The first argument $B$8:B8 in the COUNTIFS function has both absolute and relative cell references, this allows the formula to automatically expand when you copy it and paste to cells below.

"Name" and "Age" is not found in cell range B3:C6 so the COUNTIFS function returns 0 (zero) for each record.

COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6)

becomes

=COUNTIFS("Name", {"John";"Laura";"Martin";"Laura"}, "Age", {42;26;45;26})

and returns {0;0;0;0}. There are four records and the function returns an array containing 4 values (zeros).

The MATCH function allows you to identify which record to return next.

MATCH(0, COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6), 0)

becomes

MATCH(0, {0;0;0;0}, 0)

and returns 1. The first instance of 0 (zero) is found in position 1 in the array.

The INDEX function lets get a specific value using a row and column number.

The MATCH function calculates the row number we need to get the correct value, however, the COLUMNS function keeps track of which value in the record to get.

The COLUMNS function calculates the number of columns in a cell reference, the cell reference used here $A$1:A1 is also expanding when the formula is copied to other cells.

COLUMNS($A$1:A1) returns 1.

INDEX($B$3:$C$6, MATCH(0, COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6), 0),COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$6, 1, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$C$6, 1, 1) and returns John in cell B9.

Step 1 to 3 explains how the formula extracts unique distinct records from the first table.

The first part of the formula returns a #N/A error when there are no records left in table 1 to extract.

The IFNA function points the calculation to part2 when the error occurs.

=IFNA(*part1*, *part2*)

The second part of the formula does the exact same thing as the first part except that the cell references this time points to the second table.

The picture above shows that the two last records are extracted from the second table.

The picture above shows a line chart with month and year labels between tick marks instead of date values below each tick mark.

This tutorial demonstrates in great detail how to position month and year values between chart tick marks. You will need an additional series and a secondary axis to accomplish this.

To insert a line chart simply select the values you want to use, I am using the values shown in the image to the right.

Then click on the "Insert" tab on the ribbon, click the "Line" chart button and a line chart instantly appears.

The chart shown below is the default chart Excel creates when you insert a line chart.

The line chart x-axis shows dates with seven days interval to the next tick mark beginning with 11/22/2017.

Double click with left mouse button on the dates right below the chart x-axis to open the **Format Axis Task Pane**.

Here you can easily change which date the x-axis begins with and ends with, it also allows you to specify the interval.

Change **Minimum Bounds** to 11/1/2017.

Change **Major Units** from 7 to 1 and also change Days to Months.

This will change how the x-axis will display the dates, it will begin with 11/1/2017 and the next tick mark is going to have the first date in the next month.

This, however, is not what we are looking for.

We want month and year **between** tick marks, not the entire date **below** tick marks.

The chart above shows the first date in each month right below tick marks.

To position dates between tick marks we need an additional series, I am going to use the values displayed to the right.

My dates in the first dataset start in November 2017 and end in February 2018, this series also has month and year values in the same date range as my first data set.

The dates look like this 11/1/2017, 12/1/2017, 1/1/2018 and 2/1/2018.

To format the dates simply select the dates, press CTRL + 1 to open the **Format Cells** dialog box.

Here click on "Custom" and type: mmm-yy

Click OK button.

The dates show only month and year now, see picture to the right.

The next column contains 0's (zeros), these values are not needed.

They are not shown in the chart and I will show you how to hide them.

Now, right click on the chart and select "Select Data...".

The "Select Data Source" dialog box appears.

Click the "Add" button, demonstrated in the image above.

Select the cell range containing the 0's (zeros) and click the OK button. Click the next OK button, as well.

The chart above shows the second series in orange.

Double click with left mouse button on the first series (blue line in above chart).

The **Format Data Series** Task Pane appears, select "Secondary Axis".

This will move the first series to a secondary axis allowing you to choose the axis you want to be displayed.

This made the chart look like it broke or something, however, don't panic.

We just need to customize the chart so the right axis and values are displayed.

Right click on the chart, click on "Select Data...".

Select Series2, click the "Edit" button.

Select the dates formatted as month and year (mmm-yy). Click OK button.

Click the next OK button.

The month and year values are now in the correct positions, however, the blue series is missing data points.

Now add a secondary horizontal axis, first select the chart.

Click "Design" tab on the ribbon, and then "Add Chart Element". Click on "Axes" and then "Secondary Horizontal.

You are almost there, we need to hide the orange series and a few axes.

To hide the orange series double-click on the orange series to open the** Format Data Series** task pane.

Select "No line" shown in the picture to the right.

If you have markers, hide those as well.

The orange series is now hidden, however, the axes are still visible.

You can now delete the left vertical axis, simply select the left vertical axis and then press Delete on your keyboard.

It is now time to move the right vertical axis to the left side of the chart area, double click on the top horizontal axis to open the task pane.

It may seem counter-intuitive to select the top horizontal category when you want to move the right vertical axis, however, the two axes are connected, meaning you can choose where you want the axis relative to the other axis.

Select "At date" below "Vertical axis crosses" and type the earliest date in your data series.

In this case 11/1/2017.

The chart now looks like this.

One last thing to do, hide the top horizontal axis.

Double click on the top horizontal axis to open the task pane.

Click on the black arrow next to "Labels" to expand settings.

Click on drop-down list next to "Label Position" and select "None", see picture to the right.

The chart is now complete but the first value is not in the beginning of November 2017? It is 22/11/2017.

We need to use the same start date and end date on both the hidden horizontal axis and the visible one.

Double click on the bottom horizontal axis to open the Format Axis task pane.

Use the following start date: 11/1/2017 and end date: 2/28/2018

See picture to the right.

Apply the same minimum and maximum bound to the hidden top horizontal axis.

Go to "Format" tab on the ribbon, if the tab is not visible select the chart again.

Select the other horizontal axis, see picture to the right.

Make sure the minimum and maximum bound values shown in the Format Axis task pane are the same.

In this case, the start date is 11/1/2017 and end date is 2/28/2018.

The picture below shows what the chart looks like when both horizontal axes are aligned with the same start and end date.

I have added axes lines and changed major grid lines, see this post if you are interested in how:

Components of an Excel chart

How to center month and year between chart tick marks.xlsx

Use the same technique to separate months and years, see picture above.

Simply use another column containing dates formatted as years.

Download the following file to see how this chart is constructed.

How to center month and year between chart tick marks_test.xlsx

]]>If you try to copy multiple cell ranges on a worksheet that don't have the same number of rows or columns you get the following error message.

This action won't work on multiple selections, see picture above. *What? The cell ranges above have the exact same size?*

Yes, however, they also need to be on the same columns or rows.

The image to the right shows selections that share the same column letters, in this example column B and C.

If the selected cell ranges share the same column letters your selected cell ranges may have a different number of rows.

*What do you mean?* The following picture shows multiple selections that share the same column letters but each selection has a different number of rows.

The picture above shows three selections, the first selection contains one row, the second contains two rows and the third selection has 3 rows.

The same thing applies to cell ranges that share the same row numbers.

To sum it up, remember that the selections must share the same column letters or row numbers to be able to copy non contiguous cell ranges.

*I need to copy multiple noncontiguous cell ranges that don't share the same column letters or row numbers? *The following macro allows you to do that.

The picture above demonstrates a macro that allows you to copy non contiguous cell ranges. In this example, the selections are copied to destination cell F3.

The macro will show you a dialog box that allows you to select a destination cell. Each selection is then copied to the destination cell or the first empty cell below.

Sub CopySelections() Set cellranges = Application.Selection Set ThisRng = Application.InputBox("Select a destination cell", "Where to paste slections?", Type:=8) For Each cellrange In cellranges.Areas cellrange.Copy ThisRng.Offset(i) i = i + cellrange.Rows.CountLarge Next cellrange End Sub

- Open the Visual Basic Editor (Alt + F11).
- Click "Insert" on the top menu bar.
- Click "Module" to insert a code module to your workbook. Module1 automatically appears in the VB Project window.
- Paste the code into the code module.

- Save your workbook as a macro-enabled workbook, the file extension looks like this: *.xlsm
- Exit Visual Basic Editor.

- You simply hold CTRL key while selecting the cell ranges.
- Then press Alt+F8 to see a list of macros.

- Select CopySelections.
- Click "Run" button
- A dialog box appears asking you for the destination cell.

That is it, the selections are copied to your destination cell.