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.

The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders for a file name. It returns the exact path to the file, file name, and the file size.

A user defined function is a custom function that you can build yourself if none of Excel's built-in functions works for you.

**Array formula in cell range B6:D7:**

=ListFiles(C3,B3)

To enter an array formula, select cell range B6:D7. Type the formula and 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.

There are two arguments in ListFiles(*filename*, *path*)

filename |
Required. The filename you are looking for. |

path |
Required. The full path to the folder you want the UDF to begin searching in. The function will also search in all subfolders of this folder. |

There are actually two user-defined functions in order to search all subfolders and a variable that needs to be located at the very top of your workbook's code module.

Public temp() As String

Here is the first UDF, it makes sure that the result doesn't contain any error values.

Function ListFiles(FileName As String, FolderPath As String) Dim k As Long, i As Long ReDim temp(2, 0) If Right(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\" End If Recursive FileName, FolderPath k = Range(Application.Caller.Address).Rows.Count If k < UBound(temp, 2) Then Else For i = UBound(temp, 2) To k ReDim Preserve temp(UBound(temp, 1), i) temp(0, i) = "" temp(1, i) = "" temp(2, i) = "" Next i End If ListFiles = Application.Transpose(temp) ReDim temp(0) End Function

The second UDF is recursive meaning that a new instance of this UDF is created for each subfolder.

Function Recursive(FileName As String, FolderPath As String) Dim Value As String, Folders() As String Dim Folder As Variant, a As Long ReDim Folders(0) If Right(FolderPath, 2) = "\\" Then Exit Function Value = Dir(FolderPath, &H1F) Do Until Value = "" If Value = "." Or Value = ".." Then Else If GetAttr(FolderPath & Value) = 16 Then Folders(UBound(Folders)) = Value ReDim Preserve Folders(UBound(Folders) + 1) Else If Value = FileName Then temp(0, UBound(temp, 2)) = FolderPath temp(1, UBound(temp, 2)) = Value temp(2, UBound(temp, 2)) = FileLen(FolderPath & Value) ReDim Preserve temp(UBound(temp, 1), UBound(temp, 2) + 1) End If End If End If Value = Dir Loop For Each Folder In Folders Recursive FileName, FolderPath & Folder & "\" Next Folder End Function

Follow these steps to insert the code into your workbook.

- Copy macro.
- Go to VB Editor (Alt+F11).

- Click on "Insert" on the top menu.
- Click "Module" to insert a module into the workbook.
- Paste macro to the code module.
- Exit VB Editor.
- Save the workbook as a macro-enabled workbook (*.xlsm).

If you don't the macro will be gone the next time you open the workbook.

The NOMINAL function calculates the nominal annual interest rate based on the effective rate and the number of compounding periods per year.

Formula in cell B7:

=NORMINAL(B3,B5)

NOMINAL(*effect_rate*, *npery*)

effect_rate |
Required. The effective interest rate. |

npery |
Required. The number of compounding periods per year. |

The NOMINAL function returns:

- #VALUE! if an argument is not a numerical value.
- #NUM! if
*effect_rate*is smaller than 0 (zero) or if*npery*is smaller than 1.

The npery argument is automatically converted to a whole number by removing the decimals.

The NOMINAL function is related to the EFFECT function.

]]>The MUNIT function calculates the identity matrix for a given dimension.

Array formula in cell B2:

=MUNIT(3)

The formula above returns an identity matrix with dimension 3, meaning the matrix is 3x3 and has ones on the main diagonal and zeros elsewhere.

MUNIT(*dimension*)

dimension |
Required. An integer that determines the dimension of the returning unit matrix. |

The MUNIT function returns an array, you need to enter the function as an array formula.

*To enter an array formula, type the formula in a cell 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.*

The dimension argument must be larger than 0 (zero).

The MUNTI function returns #VALUE if the dimension argument is smaller than or equal to 0 (zero).

]]>The RAND function calculates a random real number greater than or equal to 0 and less than 1.

The function is volatile meaning a new random real number is returned every time the worksheet is calculated.

Formula in cell B3:

=RAND()

RAND()

The RAND function has no arguments.

]]>The MULTINOMIAL function calculates the ratio of the factorial of a sum of values to the product of factorials.

Formula in cell D3:

=MULTINOMIAL(B3:B5)

The value is calculated like this:

=FACT(2+3+4)/(FACT(2)*FACT(3)*FACT(4))

See mathematical formula below in comments.

MULTINOMIAL(*number1*, *[number2]*, ...)

number1 |
Required. The number for which you want to calculate the multinomial. |

[number2] |
Optional. Up to 254 additional numbers. |

MULTINOMIAL returns

- #VALUE! if argument is nonumeric.
- #NUM! if argument is less than 0 (zero).

The MULTINOMIAL function has the following mathematical formula:

]]>The MINIFS function calculates the smallest value based on a given set of criteria.

Formula in cell E3:

=MINIFS(C3:C10,B3:B10,"A")

The formula in cell E3 checks if values in column B are equal to "A". If TRUE, corresponding values in column C are evaluated and the minimum value is then returned to cell E3.

MINIFS(*min_range*, *criteria_range1*, *criteria1*, [*criteria_range2*, *criteria2*], ...)

min_range |
Required. A cell reference pointing to the numbers. |

criteria_range1 |
Required. Cells to evaluate based on the criteria. |

criteria1 |
Required. Criteria in the form of a number, expression, or text. |

[criteria_range2] |
Optional. Up to 126 additional arguments. |

[criteria2] |
Optional. Up to 126 additional arguments. |

The MINIFS function returns #VALUE error if the cell range size of the min_range, criteria_range and criteria is **not** the same.

]]>