0

## How to Calculate Daily Average from Hourly Data in Excel

This article will serve this purpose if you are looking for ways to calculate a daily average from hourly data in Excel. So, let’s get into the main article to ...

0

## How to Use Conditional Formatting with AND Function in Excel

If you are looking for ways to apply Conditional Formatting with the AND function in Excel, then this article will serve this purpose. Sometimes you may need ...

0

## How to Swap Non-Adjacent Cells in Excel (3 Easy Ways)

If you are looking for ways to swap non-adjacent cells in Excel, this article is for you. Swapping values can be time-saving for correcting any mistakes of ...

0

## Excel COUNTIF Function with Multiple Criteria & Date Range

If you are looking for ways to use the COUNTIF function with multiple criteria date range in Excel, then this article will be helpful for you. Sometimes you ...

0

## How to Spell Number in Dirhams in Excel (2 Handy Ways)

If you are looking for ways to spell a number in Dirhams in Excel, then you are in the right place. So, let’s start with the main article to know the 2 ...

0

## How to Highlight Text in Text Box in Excel (3 Handy Ways)

If you are looking for ways to highlight text in the Excel text box, then you are in the right place. So, let’s get started with the main article to know 3 ...

0

## How to Disable Automatic Update of Links in Excel (3 Easy Ways)

If you are looking for ways to disable the automatic update of links in Excel, then this article will serve this purpose. Sometimes the source articles are ...

0

## How to Find Upper and Lower Limits of Confidence Interval in Excel

If you are looking for ways to find the upper and lower limits of a confidence interval in Excel, then this article will help you with 5 different ways. The ...

0

## How to Skip to Next Cell If a Cell Is Blank in Excel (5 Easy Ways)

If you are looking for ways to skip to the next cell if a cell is blank in Excel, then this article will serve this purpose. So, let’s start with the main ...

0

## How to Create Calculated Field in Pivot Table Data Model

If you are looking for ways to create a calculated field in the Pivot Table data model, then this article will serve this purpose. So, let’s start with our ...

0

## How to Compare Two Pivot Tables in Excel (3 Suitable Examples)

If you are looking for ways to compare two pivot tables in Excel, this article is for you. So, let’s start with the main article to apprehend the examples ...

0

## How to Create Stacked Area Chart with Negative Values in Excel

If you are looking for ways to create a stacked area chart with negative values in Excel, then this article will serve this purpose. Generally, it is difficult ...

0

## Excel VBA: Zoom to Fit Screen (4 Suitable Examples)

If you are looking for ways to zoom in to fit a screen using VBA in Excel, then you will find this article helpful. By using the VBA codes of this article, you ...

0

## Conditional Formatting Icon Sets Based on Text in Excel

If you are looking for ways to use Conditional Formatting icon sets based on text in excel, then you are in the right place. By applying the icon sets we can ...

0

## How to Make Table of Contents Using VBA in Excel (2 Examples)

If you are looking for ways to make a table of contents in Excel VBA quickly, then this article is for you. So, let’s dive into the main article to know more ...

##### Browsing All Comments By: Tanjima Hossain
1. Reply Tanjima Hossain Sep 4, 2022 at 4:09 PM

Hi SANSHI,
You can use the direct method to calculate the PPF interest easily.
For calculating the PPF interest, we will be using the following dataset. Here, we have the total Balance, Deposits from April to March, and an Interest Rate of 5%. • For the monthly interest rates use the following formula
=IF(C4=”Before 5th”,(\$A\$2+B4)*(D4/12),(\$A\$2)*(D4/12)) • For the first month of getting the balances, apply the following formula in cell F4.
=\$A\$2+B4 • To get the rest of the balances type the following formula
=\$A\$2+SUM(\$B\$4:B5)
Drag down the Fill Handle tool. The final output will look like the following figure. 2. Reply Tanjima Hossain Sep 4, 2022 at 1:32 PM

Hi MRBRAT2U,
Thanks for reaching us. You can use the following code to execute your desired operation.
According to your requirement, I have created a source worksheet wsx containing a list of projects with their costs up to 9999 rows. For the results, we have enlisted some project names in the Sheet2 column and after running the code we will extract the cost values in the Cost column. • Type the following code in your created module.

``````Sub vlookupvba()
Dim FinalRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
FinalRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Debug.Print ActiveWorkbook.Name
Debug.Print ActiveSheet.Name
Range("B2:B" & FinalRow).FormulaR1C1 = "=VLOOKUP(RC[-1],wsx!R2C1:R9999C2,2,FALSE)"
End Sub`````` • Press F5.
Afterward, you will have the cost values extracted in the Cost column. 3. Reply Tanjima Hossain Sep 4, 2022 at 11:10 AM

Hi Jeff Blackwell,
Thanks for reaching us. You can use the following code with some adjustments to split a worksheet based on column into some worksheets of the same workbook.
Here, we will split the following worksheet based on the Month column and so the worksheet will be split into 3 different sheets – January, February, March. • Use the following code. Adjust the starting row number in nRow = 2, the column indicating letter in (the base column on which you will split the worksheet) objWorksheet.Range(“C” & nRow).

``````Sub SplitSheetIntoMultipleWorkbooksBasedOnColumn()
Dim objWorksheet As Excel.Worksheet
Dim nLastRow, nRow, nNextRow As Integer
Dim strColumnValue As String
Dim objDictionary As Object
Dim varColumnValues As Variant
Dim varColumnValue As Variant
Dim xWS As Excel.Worksheet
Set objWorksheet = ActiveSheet
nLastRow = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row
Set objDictionary = CreateObject("Scripting.Dictionary")
For nRow = 2 To nLastRow
strColumnValue = objWorksheet.Range("C" & nRow).Value
If objDictionary.Exists(strColumnValue) = False Then
End If
Next
varColumnValues = objDictionary.Keys
For i = LBound(varColumnValues) To UBound(varColumnValues)
varColumnValue = varColumnValues(i)
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
objWorksheet.Rows(1).EntireRow.Copy
xWS.Name = varColumnValue
xWS.Activate
xWS.Range("A1").Select
xWS.Paste
For nRow = 2 To nLastRow
If CStr(objWorksheet.Range("C" & nRow).Value) = CStr(varColumnValue) Then
objWorksheet.Rows(nRow).EntireRow.Copy
nNextRow = xWS.Range("A" & objWorksheet.Rows.Count).End(xlUp).Row + 1
xWS.Range("A" & nNextRow).Select
xWS.Paste
xWS.Columns("A:D").AutoFit
End If
Next
Next
End Sub`````` • Press F5.
Then, you will have 3 sheets- January, February, March. 4. Reply Tanjima Hossain Sep 4, 2022 at 9:20 AM

Thanks for your compliment.

5. Reply Tanjima Hossain Sep 1, 2022 at 9:46 AM

Hi Laurene,
Thanks for staying with us. If the net income cash flows reduced, or 0 or negative, whatever it is. The value of the argument finance rate doesn’t depend on it. You must give the rate as input which is paid by you for cash flows. When you select the payment and incomes at specified intervals as the Values argument, the finance rate as the rate paid by you for your income, and finally the reinvestment rate, the MIRR function will calculate the rate by automatically adjusting the values.

6. Reply Tanjima Hossain Aug 30, 2022 at 3:31 PM

Hi Raymond,
Thanks for your question. I think you can do your task easily by following the code below.
• Right-click on the sheet name containing your dataset and then select the View Code option. • Type the following code in the opened window and make sure to adjust the number of Target.Column = 5 according to the column number of the emails.

``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
New_Email = Application.WorksheetFunction.Substitute(Target.Value, "gmail", "outlook")
Target.Value = New_Email
End If
End Sub`````` • Type a random email with @gmail.com • Press ENTER.
In this way, the email will be automatically changed from gmail to outlook. 7. Reply Tanjima Hossain Aug 30, 2022 at 12:01 PM

Hello Jorge.F,
Thank you so much for your suggestion. I think you might want the following code incorporating your suggested lines. Moreover, I tried to select the range through an input box which may help you select the range.

``````Sub Insert_Blank_Rows()
Dim rng As Range
Dim CountRow As Integer
Dim i As Integer
Dim n As Integer
Application.CutCopyMode = False
ExcelTitleId = "Exceldemy"
Set rng = Application.Selection
Set rng = Application.InputBox("Select your range", ExcelTitleId, rng.Address, Type:=8)
n = Int(InputBox("Enter the Value of n: "))
k = Int(InputBox("Enter the Number of Blank Rows: "))
CountRow = rng.EntireRow.Count
Selection(1).Activate
For i = 1 To Int(CountRow / n)
For j = 0 To k - 1
ActiveCell.Offset(n + j, 0).EntireRow.Insert
Next j
ActiveCell.Offset(n + k, 0).Select
Next i

End Sub``````
8. Reply Tanjima Hossain Aug 30, 2022 at 9:51 AM

Hello Julie,
You can try out the following code. I think it will work for you. Just make sure to change the number in Target.Column = 3 according to your column number of data validation.

``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
Else
If newVal = "" Then
Else
Target.Value = oldVal & ", " & newVal

End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub`````` 9. Reply Tanjima Hossain Aug 29, 2022 at 3:22 PM

Thanks for your question.
According to your requirement, I have created a random dataset containing 1M records within 1M rows of a dataset in Excel. Using a VBA code, I will split it into 10 different worksheets each containing 100,000 rows. • Type the following VBA code. Here, instead of using the InputBox method, we are directly declaring the total range and the split number in the code which will expediate running the code.

``````Sub SplitExcelSheet_into_MultipleSheets()
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
Set xWs = Range("A1:A1000000").Parent
Set xRow = Range("A1:A1000000").Rows(1)
Application.ScreenUpdating = False
For i = 1 To 1000000 Step 100000
resizeCount = 100000
If (Range("A1:A1000000").Rows.Count - xRow.Row + 1) < 100000 Then resizeCount = Range("A1:A1000000").Rows.Count - xRow.Row + 1
xRow.Resize(resizeCount).Copy
Application.ActiveSheet.Range("A1").PasteSpecial
Set xRow = xRow.Offset(100000)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub`````` • Press F5.
In this way, we have created 10 different sheets each with 100000 records. 10. Reply Tanjima Hossain Aug 29, 2022 at 2:14 PM

Hi Alex,
Here, I have shown the procedure of creating a waterfall chart for positive increments only. However, you can follow the article of this link- https://www.exceldemy.com/excel-waterfall-chart-with-negative-values/ – to create a chart with negative increment values.

11. Reply Tanjima Hossain Aug 22, 2022 at 2:08 PM

Hi Tushar Chauhan,
kindly let me know which code is causing this problem.

12. Reply Tanjima Hossain Aug 22, 2022 at 12:33 PM

Hello Tushar Chauhan,
Thanks for reaching us. According to your stated scenario, I have created the following dataset for some imaginary employees. Using their weekly presence and targeted presence we will calculate their weekly percentages here. In the process of creating PivotTable, make sure to check the Add this data to the Data Model option. • Drag down the SalesPerson and Email Id fields to the Rows area and Weekly Presence and Targeted Presence fields to the Values area. • To add a new measure for calculating percentages right-click on the table name Range and select the Add Measure option. • In the Measure dialog box, enter Percentage as Measure Name and use the following formula in the Formula box
=[Sum of Weekly Presence]/[Sum of Targeted Presence] • Choose Number as Category and then select the Percentage option.
• Press OK. • Drag down the newly created Percentage measure to the Values area. Afterward, you will get the Percentage column in your PivotTable. Now, if you change any data of your main dataset then the values of the PivotTable will be updated also.
For instance, we have changed the values of the Weekly Presence column in the main dataset. • Now go to the sheet with PivotTable and then go to the PivotTable Analyze tab >> Refresh group >> Refresh option. After that, the percentages will be updated also. To send these percentages to individual employees automatically using VBA script you can follow this article https://www.exceldemy.com/send-bulk-email-from-outlook-using-excel/
After going through this article, you will notice different ways of doing this task.

13. Reply Tanjima Hossain Aug 21, 2022 at 4:50 PM

Hi Kala,
Thanks for your question. According to your comment, you want to work with a table that’s why I have created the following table. You can use the following formula
=IF(ROW()=””,””,TODAY())
Here, ROW() will return the corresponding row number for a row; like for Row 5, it will give the value 5, for Row 6 you will have 6. Then, you can insert the names in the Name column. 14. Reply Tanjima Hossain Aug 21, 2022 at 4:03 PM

Hi Jeff V,
Thanks for reaching us. You have informed us here that the aforementioned code is not giving your expected out. But in my case, I am getting the correct outputs by extracting data from different workbooks into one. I think yours will also work fine if you notice the following matters.
• Firstly, copy the exact path name where your desired files are saved.  • Put down the correct sheet name of your saved workbooks in the following indicated areas. After modifying all of these factors, run your final code.
Sub ExtractDataToDifferentSheets()
On Error GoTo HandleError
Application.ScreenUpdating = False
Dim objectFlieSys As Object
Dim objectGetFolder As Object
Dim file As Object
Set objectFlieSys = CreateObject(“Scripting.FileSystemObject”)
Set objectGetFolder = objectFlieSys.GetFolder(“D:\Monthly Sales”)
Dim counter As Integer
counter = 1
For Each file In objectGetFolder.Files
Dim sourceFiles As Workbook
Set sourceFiles = Workbooks.Open(file.Path, True, True)
Dim rowsNumber As Integer
rowsNumber = sourceFiles.Worksheets(“Sheet1”).UsedRange.rows.Count
Dim colsNumber As Integer
colsNumber = sourceFiles.Worksheets(“Sheet1”).UsedRange.Columns.Count
Dim rows, cols As Integer
For rows = 1 To rowsNumber
For cols = 1 To colsNumber
Application.Workbooks(1).ActiveSheet.Cells(rows, cols) = _
sourceFiles.Worksheets(“Sheet1”).Cells(rows, cols)
Next cols
Next rows
rows = 0
Dim worksheetName As String
worksheetName = Replace(sourceFiles.Name, “.xlsx”, “”)
sourceFiles.Close False
Set sourceFiles = Nothing
With ActiveWorkbook
.ActiveSheet.Name = worksheetName
counter = counter + 1
If counter > .Worksheets.Count Then
End If
.Worksheets(counter).Activate
End With
Next
HandleError:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Finally, you will get the following sheets in a single workbook. 15. Reply Tanjima Hossain Aug 21, 2022 at 1:17 PM

Hi Andy,
Thanks for your query. Unfortunately, using the UNIQUE function you cannot do your desired job directly. So, I have come up with an easy alternative way.
Here, I have created the following dataset using your example. Using the PivotTable feature of Excel, I will convert the following three columns into a single column with unique values only. • Press ALT+D and then P immediately to open up the PivotTable and PivotChart Wizard. • In Step 1 of this wizard click on the options; Multiple consolidation ranges, PivotTable.
• Click on Next. • In Step 2a of this wizard click on the Create a single page field for me option.
• Click on Next. • Now, select the range of the words including a blank column prior to this range in the Range box.
• Select Add to enter the formula of the Range box to the All ranges box. Afterward, the formula will be entered into the All ranges box, and finally, click on Next. • In Step 3 of this wizard click on the New worksheet option.
• Click on Finish. • Now, drag down the Value to the Rows area.
Finally, all of the unique words will be listed in a single column. 16. Reply Tanjima Hossain Aug 21, 2022 at 12:01 PM

Hello Jen,
Thanks for your question. I think there is no direct way to fulfill your requirement using the UNIQUE function. But you can use a combination of some functions to get the unique values after comparing different columns.
So, I have created the following dataset where I have some names of fruits in the two columns, List 1 and List 2. Here, we have some same fruits names in these two columns, and using the formula we will extract the unique values of these columns in the columns; Result 1, and Result 2. For extracting the unique values of List 1, we will use the following formula in Result 1.
=IF(ISNA(VLOOKUP(B3,\$C\$3:\$C\$9,1,FALSE)),B3,””) After comparing the unique values of List 2 with the values of List 1 we will use the following formula in the Result 2 column.
=IF(ISNA(VLOOKUP(C3,\$B\$3:\$B\$9,1,FALSE)),C3,””) 17. Reply Tanjima Hossain Aug 16, 2022 at 1:21 PM

Hello Dan,
Thank you so much for your appreciation. Hope you will be benefitted more by staying with our Exceldemy site.

18. Reply Tanjima Hossain Aug 16, 2022 at 1:18 PM

Hi David, I think maybe you have forgotten to change the name of the worksheet from another to practice while working with the practice worksheet. So, you can try out the following code to work with the practice sheet.
Sub selectrange1()
Dim LR As Long
Dim x1 As Range, y1 As Range
With ThisWorkbook.Worksheets(“practice”)
LR = Cells(Rows.Count, “B”).End(xlUp).Row
Application.ScreenUpdating = False
For Each x1 In .Range(“B1:B” & LR)
If x1.Text = “Apple” Then
If y1 Is Nothing Then
Set y1 = .Range(“C” & x1.Row).Resize(, 2)
Else
Set y1 = Union(y1, .Range(“C” & x1.Row).Resize(, 2))
End If
End If
Next x1
Application.ScreenUpdating = True
End With
If Not y1 Is Nothing Then y1.Select
End Sub

19. Reply Tanjima Hossain Aug 16, 2022 at 11:59 AM

Hello Chandrakant, regarding your problem I have created the following two datasets where in both sheets I have your predefined text 762-V-231. After comparing the range of texts from Sheet1 with Sheet2 I will have the matched texts besides the Existing column.
In Sheet1 I have a range of texts and after the comparison, I will have matched texts in the Similar Text column. The comparison will be done with Sheet2 To do this comparison you can use the following code

Sub find_text()
Dim source_txt As Range, find_txt As Range
For Each source_txt In Sheets(“Sheet1”).Range(“A2:A6”)
For Each find_txt In Sheets(“Sheet2”).Range(“A2:A6”)
If InStr(1, source_txt, find_txt, vbTextCompare) > 0 Then
source_txt.Offset(0, 1) = find_txt
Exit For
End If
Next
Next
Set source_txt = Nothing
Set find_txt = Nothing
End Sub

After pressing F5, you will have the following result 20. Reply Tanjima Hossain Aug 16, 2022 at 10:29 AM

Hello EXCELLEARNER, Thank you so much for your compliment. Hope you will stay with our site Exceldemy always.

21. Reply Tanjima Hossain Aug 16, 2022 at 10:09 AM

Hello Gabrielle, after going through your problem, I have understood it like this that you want to highlight those dates which are greater than 30 days (or January month) or 60 days (or February month), or 90 days (or March month) and the corresponding specific cells are blank for these dates. If the specific cells are not blank, then the coloring should be avoided. After understanding this scenario, I have created the following dataset like your sample to illustrate the process. • Firstly, select the dates, and then go to the Home tab >> Conditional Formatting dropdown >> New Rule option. • In the opening dialog box, choose the indicated option and then type the following formula in the box
=AND(B2=””,OR(A2>DATEVALUE(“1/30/2022”),A2>DATEVALUE(“2/28/2022”),A2>DATEVALUE(“3/31/2022”)))
• Click on Format • In the Format Cells dialog box go to the Fill tab >> select your desired color >> press OK. Then, the following result will appear. 22. Reply Tanjima Hossain Jul 24, 2022 at 2:40 PM

Hello Richard,
Thanks for your question. The maximum number of sheets in a workbook is 255. So, if you have values for more than 255 rows in a sheet on the basis of which you will create multiple sheets then you may face a problem.

23. Reply Tanjima Hossain Jul 20, 2022 at 10:47 AM

Hello Muizz Shaikh,
Thanks for your question. The maximum number of entries should be within the limit of the maximum row numbers of Excel which is 1,048,576. So, you can merge the files as long as the entries of the combined file support this limit.

24. Reply Tanjima Hossain Jul 19, 2022 at 1:08 PM

Hi Jose A. Valiente, thanks for your valuable suggestion. Actually, the third method is quite different from the first 2 methods as this method determines the P value for the correlation of the two sets of values using a correlation factor. 