##### User Posts: Tanjima Hossain

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

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 ...

- 1
- 2
- 3
- …
- 11
- Next Page »

Hi SANSHI,

You can use the

direct methodto calculate thePPFinterest easily.For calculating the

PPFinterest, we will be using the following dataset. Here, we have thetotal Balance,DepositsfromApriltoMarch, and anInterest Rateof5%.â€˘ 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)â€˘

Dragdown theFill Handletool.The final output will look like the following figure.

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

wsxcontaining a list of projects with their costs up to9999rows.For the results, we have enlisted some project names in the

Sheet2column and after running the code we will extract the cost values in theCostcolumn.â€˘ Type the following code in your created module.

â€˘ Press

F5.Afterward, you will have the cost values extracted in the

Costcolumn.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

Monthcolumn and so the worksheet will be split into3different 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).â€˘ Press

F5.Then, you will have

3sheets-January,February,March.Hello Milad,

Thanks for your compliment.

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 ratedoesnâ€™t depend on it. You must give the rate as input which is paid by you for cash flows. When you select thepaymentandincomesat specified intervals as theValuesargument, thefinance rateas the rate paid by you for your income, and finally thereinvestment rate,the MIRR functionwill calculate the rate by automatically adjusting the values.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 Codeoption.â€˘ Type the following code in the opened window and make sure to adjust the number of

Target.Column = 5according to the column number of theemails.â€˘ After saving the code, return to your worksheet.

â€˘ Type a random email with

@gmail.comâ€˘ Press

ENTER.In this way, the email will be automatically changed from

gmailtooutlook.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.

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 = 3according to yourcolumn numberof data validation.Hi Milad,

Thanks for your question.

According to your requirement, I have created a random dataset containing

1Mrecords within1Mrows of a dataset in Excel. Using aVBAcode, I will split it into10different worksheets each containing100,000rows.â€˘ Type the following

VBAcode. Here, instead of using theInputBox method, we are directly declaring thetotal rangeand thesplit numberin the code which will expediate running the code.â€˘ Press

F5.In this way, we have created

10different sheets each with100000records.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.

Hi Tushar Chauhan,

kindly let me know which code is causing this problem.

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 presenceandtargeted presencewe will calculate their weekly percentages here.In the process of creating PivotTable, make sure to check the

Add this data to the Data Modeloption.â€˘ Drag down the

SalesPersonandEmail Idfields to theRowsarea andWeekly PresenceandTargeted Presencefields to theValuesarea.â€˘ To add a new measure for calculating percentages

right-clickon the table nameRangeand select theAdd Measureoption.â€˘ In the

Measuredialog box, enterPercentageasMeasure Nameand use the following formula in theFormulabox=[Sum of Weekly Presence]/[Sum of Targeted Presence]â€˘ Choose

NumberasCategoryand then select thePercentageoption.â€˘ Press

OK.â€˘ Drag down the newly created

Percentagemeasure to theValuesarea.Afterward, you will get the

Percentagecolumn in yourPivotTable.Now, if you change any data of your main dataset then the values of the

PivotTablewill be updated also.For instance, we have changed the values of the

Weekly Presencecolumn in the main dataset.â€˘ Now go to the sheet with

PivotTableand then go to thePivotTable Analyzetab >>Refreshgroup >>Refreshoption.After that, the percentages will be updated also.

To send these percentages to individual employees automatically using

VBA scriptyou 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.

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 forRow 5, it will give the value5, forRow 6you will have6.Then, you can insert the names in the

Namecolumn.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

.Sheets.Add After:=.Worksheets(.Worksheets.Count)

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.

Hi Andy,

Thanks for your query. Unfortunately, using

the UNIQUE functionyou 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 featureof Excel, I will convert the following three columns into a single column with unique values only.â€˘ Press

ALT+Dand thenPimmediately to open up thePivotTable and PivotChart Wizard.â€˘ In

Step 1of this wizard click on the options;Multiple consolidation ranges,PivotTable.â€˘ Click on

Next.â€˘ In

Step 2aof this wizard click on theCreate a single page field for meoption.â€˘ Click on

Next.â€˘ Now, select the range of the words including a blank column prior to this range in the

Rangebox.â€˘ Select

Addto enter the formula of theRangebox to theAll rangesbox.Afterward, the formula will be entered into the

All rangesbox, and finally, click onNext.â€˘ In

Step 3of this wizard click on theNew worksheetoption.â€˘ Click on

Finish.â€˘ Now, drag down the Value to the

Rowsarea.Finally, all of the unique words will be listed in a single column.

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 1andList 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, andResult 2.For extracting the unique values of

List 1, we will use the following formula inResult 1.=IF(ISNA(VLOOKUP(B3,$C$3:$C$9,1,FALSE)),B3,””)After comparing the unique values of

List 2with the values ofList 1we will use the following formula in theResult 2column.=IF(ISNA(VLOOKUP(C3,$B$3:$B$9,1,FALSE)),C3,””)Hello Dan,

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

Hi David, I think maybe you have forgotten to change the name of the worksheet from

anotherto practice while working with thepracticeworksheet. So, you can try out the following code to work with thepracticesheet.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

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

Sheet1withSheet2I will have the matched texts besides the Existing column.In

Sheet1I 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

Sheet2To 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 resultHello EXCELLEARNER, Thank you so much for your compliment. Hope you will stay with our site Exceldemy always.

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

Hometab >>Conditional Formattingdropdown >>New Ruleoption.â€˘ 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 Cellsdialog box go to theFilltab >> select your desired color >> pressOK.Then, the following result will appear.

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.

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.

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.