User Posts: Afrina Nafisa
0

## How to Link PowerPoint Chart to Excel (Step by Step)

In this article, we will link a PowerPoint chart to Excel using the simple copy-and-paste method. But this time, you need to paste the chart using paste ...

0

## How to Use Non Linear Interpolation in Excel (

In this Excel article, we will learn how to calculate non linear interpolation Excel. While working on a dataset, we need to deal with those data points that ...

0

## Excel Color Index Numbers

In this article, we will get Excel color index numbers using Excel VBA. Here, we will learn how to get color index numbers in Excel and how to change the ...

0

## Excel VBA Calculate Cell

In this article, we will learn how to use Excel VBA to calculate cell. We will calculate cell, workbook, worksheet, range, and formula using Excel VBA macro. ...

0

This article will teach us how to create a sole trader bookkeeping Excel. Here, we will create a template and add all the information to complete the template. ...

0

## Compare Two Excel Sheets and Highlight Differences Macro

In this article, we will learn how to compare two Excel sheets and highlight differences using a macro. So, let's start. Sometimes we are working on two ...

0

## [Solved!] Excel Feathered Theme Missing

A theme is defined by font, color, and effects. The theme enhances the readability of the dataset. Excel, by default, contains different types of themes. But ...

0

## How to Handle Excel VBA On Error GoTo 0

While working on a dataset, sometimes you need to sum up numbers or do other operations. This is quite easy while you are working on a small dataset, but if ...

0

## How to Add Excel Feathered Theme

In this article, we will learn how to add Excel feathered theme. While working in Excel, you will find different types of themes that can improve the ...

0

## Excel VBA to Change Pivot Table Source (2 Easy Ways)

Suppose you need to work with a single criterion in a huge dataset. In that case, we use the pivot table to narrow down other criteria, but what if you already ...

0

## Excel VBA Horizontal Alignment

In this article, we will learn how to use Excel VBA horizontal alignment. Excel VBA is the visual basic analysis which is a programming language. You can ...

0

## How to Use Excel VBA Sub to Return Value

In this article, we will learn how to execute Excel VBA sub to return value using the ByRef keyword in different ways.VBA is a programming language that stands ...

0

## How to Fit Long Text in One Cell in Excel

Nowadays, Excel is quite a common tool to use in corporate life and day-to-day life as well. As a beginner, many people face some common problems in Excel. One ...

0

## How to Use Excel VBA to Wait Until Refresh Complete

Excel VBA programming is editable programming executed when a macro is recorded in Excel. Nowadays people are more into automation and VBA simplifies the ...

0

## How to Create Timeline Slicer with Date Range in Excel

Nowadays using Excel is quite common in day-to-day life. Specially, we need to work on large datasets in corporate life. But there is a bit of a hassle when ...

Browsing All Comments By: Afrina Nafisa
1. Reply Afrina Nafisa Sep 18, 2023 at 6:09 PM

Hello JESSPEAR,

Hope you are doing well. In Discount price Column we used formula with relative cell reference that’s why xlPasteFormulas command copying the formula of that column with same cell reference. In Column E you will get the result with relative cell reference.

However, you can also use the OFFSET function to copy the formula with relative reference. ``````Sub Copy_Formula_with_Relative_Cell_References()
Worksheets("Sheet1").Activate
Range("D4:D13").Copy
Range("E4").PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End Sub
``````
2. Reply Afrina Nafisa Sep 7, 2023 at 4:42 PM

Hello FARHAN,

Hope you are doing well. I can see the formula in this article to calculate commission is a bit tricky. However, you can use the below procedure to calculate commission. I believe this formula is easier than the previous one.

`=IF(K5< 12%,0,IF(K5 <= 20%, I5 *0.005,I5 *0.015)) `

Here is the final output after applying the formula to calculate the commission. 3. Reply Afrina Nafisa Aug 23, 2023 at 5:20 PM

Hello SHAWN,

Hope you are doing well. Well, you can try the code below to get multiple cells value in pivot table. Code

``````Sub FilterPivotTable()
Dim filtvalues As Variant
Dim i As Integer, j As Integer
Dim pvt As PivotField
Dim pitm As PivotItem
' Set the filter range with values to filter on
filtvalues = Range("I5:I7")
' Set the pivot field object
Set pvt = ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Product")
' Modify "SalesPivotTable" and "Product" according to your pivot table and field names
' Clear existing filters
pvt.ClearAllFilters
' Apply filter to pivot table
For i = 1 To pvt.PivotItems.Count
Set pitm = pvt.PivotItems(i)
pitm.Visible = False
For j = 1 To UBound(filtvalues, 1) - LBound(filtvalues, 1) + 1
If pitm.Name = filtvalues(j, 1) Then
pitm.Visible = True
Exit For
End If
Next j
Next i
End Sub``````

Initially, the pivot table is similar to the below image. Here is the final output. 4. Reply Afrina Nafisa Aug 17, 2023 at 12:45 PM

Hello Gianluca,

Hope you are doing well. Thank you for your query. Well, I can see two columns here and you need to get the running maximum of the values using a dynamic array. Therefore, you can use the MAX function to get the running maximum from the dataset. You may follow the below image to use the MAX function as an array function. If you use the total column in the formula as below then the formula will work like an array formula. You can add or change any value within the range and the running maximum value will change according to your dataset.

[wpsm_box type="green" float="none" textalign="center"]
`=MAX(\$B:\$B,\$C:\$C)`
[/wpsm_box] Here, if add another column then the formula adds the values in the range and changes the output as below. 5. Reply Afrina Nafisa Aug 6, 2023 at 11:43 AM

Hello M,
Thank you for your query. I hope you are doing well. The quickest way to convert CSV files to XLSX files is by using programming languages like Excel VBA. You may check Method 4 ( Applying VBA Code to Convert Multiple CSV Files to XLSX without Opening) to convert multiple files in subfolders.

6. Reply Afrina Nafisa Jul 30, 2023 at 12:44 PM

Hello OLWETU,

I hope you are doing well. Well, thank you for your query. Adding asterisks to graphs is a significant way to show the difference between two or more groups. You can get the mean value of the treatments using the ANOVA method. Then plot the mean values in the graph. This part is relatively easy and already shown in the article. Now, if you want to add the asterisks to the chart to define the largest mean then select the chart and check the Data levels as below. Therefore, write down a” in a cell and double-click the value of the highest mean value. Once you click on the data levels you will see the options of selecting data levels. Finally, select Choose Cell  to get the asterisks as “a”. Now, apply the MIN function to get the minimum value of the mean values and inter asterisks as “b” using the same process. 7. Reply Afrina Nafisa Jul 16, 2023 at 12:22 PM

Hello JACOB FLOYD,

8. Reply Afrina Nafisa Jun 6, 2023 at 10:28 AM

9. Reply Afrina Nafisa Jun 4, 2023 at 3:36 PM

Hello BLAKE HUGUENIN. Thank you for your query. I believe the best way to combine multiple spreadsheets and pivot tables is using Power Query. Please follow the below steps:
Initially, select Data >> Get Data >> From File >> From Excel Workbook Toolbar. Now, Get the Import File dialog box and select the required file. Then navigator tab will be visible and select the worksheet to copy the sheet in another workbook. Complete the process by clicking on Load and Close. Now repeat this process to add sheets as much as you want. Using Power Query is better because if you change the value in any of the workbooks then the value will be changed in master workbook.

Now, if you want to create a dashboard that will be dynamic and change the values of the data if the primary data is changed then use formula =(Sheetname!Range) in the dashboard. For instance =(File4!C5:C13). Please find Add Multiple Worksheets in the master workbook

10. Reply Afrina Nafisa Jun 4, 2023 at 3:15 PM

Hello, NARASIMHAN S

Please find the below code to get only specific sheets by name.

``````Sub CopySpecificSheetsAsValues()
Dim nwk As Workbook
Dim wsht As Worksheet
Dim sheetNames() As Variant
Dim sheetName As Variant
sheetNames = Array("File 1", "File 3", "File 4")
For Each wsht In ThisWorkbook.Worksheets
If IsInArray(wsht.Name, sheetNames) Then
wsht.Copy After:=nwk.Worksheets(1)
End If
Next wsht
nwk.Worksheets(1).Delete
For Each wsht In nwk.Worksheets
wsht.UsedRange.Value = wsht.UsedRange.Value
Next wsht
nwk.Close SaveChanges:=False
End Sub

Function IsInArray _
(valueToFind As Variant, arr As Variant) As Boolean

Dim element As Variant
On Error GoTo ErrorHandler
For Each element In arr
If element = valueToFind Then
IsInArray = True
Exit Function
End If
Next element
ErrorHandler:
IsInArray = False
End Function
``````

Note:  Change the Path Id and the sheet name according to your convenience. 11. Reply Afrina Nafisa Jun 4, 2023 at 3:07 PM

Hello, CALEB HOGUE. Thank you for your query. I understand you find difficulties using 9 and 10  as Tab and Enter. Please follow the below code to get 9 and 10 as Tab and Enter respectively.

Sub ActivateFirstBlankCell()
Dim cell As Range

``````Sub ActivateFirstBlankCell()
Dim cell As Range

' Loop through each cell in the range A1:A5
For Each cell In Range("A1:A5")
' Check if the cell is blank
If cell.Value = "" Then
' Activate the blank cell
cell.Activate
Exit Sub ' Exit the loop once the first blank cell is found
End If
Next cell
End Sub`````` 12. Reply Afrina Nafisa May 31, 2023 at 11:22 AM

Hello ERIC. Thank you so much for your query. Please follow the formula below to get the days between the two dates, including the Launching and Closing dates.

`=IF(NETWORKDAYS.INTL(C5, D5, 1) > 0, NETWORKDAYS.INTL(C5, D5, 1) + 1, IF(WEEKDAY(C5, 1) < WEEKDAY(D5, 1), NETWORKDAYS.INTL(C5, D5, 1) + 2, NETWORKDAYS.INTL(C5, D5, 1) + 1))`

Here, we used NETWORKDAYS.INTL function along with the IF function. Now, this formula can check if the launching date or the closing date is a weekend and adjust the result accordingly. For a better understanding, you can check Number-of-Days-Between-Two-Dates-Calculator-1 as well.

13. Reply Afrina Nafisa May 29, 2023 at 12:35 PM

Hello, JACK LONETTO. Thank you for your query. After calculating the loan installment using Amortization Schedule with your given criteria, it shows the loan will be dismissed after the 6th installment if you go with a 20% down payment at the beginning of the year. If you want to complete this process in 10 years then the criteria will be-

Amount Financed \$5,280,000
Interest Rate 4%
Term 10Years
At the beginning of Year2 2% Down payment
At the beginning of Year3 2% Down payment on the Remaining Balance
At the beginning of Year4 2% Down payment on the Remaining Balance
At the beginning of Year5 2% Down payment on the Remaining Balance
At the beginning of Year6 2% Down payment on the Remaining Balance
At the beginning of Year7 2% Down payment on the Remaining Balance
At the beginning of Year8 3% Down payment on the Remaining Balance
At the beginning of Year9 3% Down payment on the Remaining Balance
Remaining balance at the beginning of year10 For a better understanding, you can check Amortization-Schedule-with-Irregular-Payments as well. Advanced Excel Exercises with Solutions PDF  