Afrina Nafisa

About author

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis, VBA, etc.

Designation

Web Content Developer at ExcelDemy in SOFTEKO.

Lives in

Dhaka, Bangladesh.

Education

B.Sc. in Industrial and Production Engineering, Ahsanullah University of Science and Technology

Expertise

Data analysis, HTML, Supply Chain Management, Industrial Design, Solidworks, Microsoft Excel

Experience

  • Web Content Developer in SOFTEKO
  • Transport Manager (Intern) in Decathlon Bangladesh
    • Manage Supply chain of purchase orders
    • calculated the lead time of different lanes and managed that time limit in every week’s order.
    • Made shipment calendar
    • Managed Eid shipment

Achievements, Awards & Co-curricular Activities

  • Runner-up of Pran MPE business idea competition. (Supply chain) organized by Ahsanullah University of Science and Technology.
  • Ex-manager, IPE cricket team (Batch 15), a tournament organized by AUST sports club.

Latest Posts From Afrina Nafisa

0
How to Hide Cells in Excel – 3 Methods

This is an overview. Download Practice Workbook Hide Cells.xlsx Method 1 - Using the Keyboard Shortcut to Hide Cells Across Columns ...

0
How to VLOOKUP a Range in Excel – 7 Examples

  Download Practice Workbook Download the worksheet. Performing VLOOKUP in Range.xlsx Example 1 - Applying the VLOOKUP Function to get ...

0
How to Split in Excel (Text, Numbers, Cells & Columns)

The overview shows the summary of splitting first and last names, separating numbers from text, and splitting cells vertically across rows. Here, the C column ...

0
How to Link PowerPoint Chart to Excel: 2 Methods

Method 1 - Create the Chart in Excel Select the table range C5:D13 and go to Insert Tab >> Chart to create a chart. An Excel Chart is added ...

0
The Feathered Theme is Missing in Excel – Solution

This dataset represents sales and profit of different companies over the year. The Office theme was used to create the dataset. Step 1 - Download ...

0
How to Add the Excel Feathered Theme

In Excel, you will find different types of themes that can improve the readability of the dataset. Usually, a feathered theme is not added to Excel by default. ...

0
Using Excel VBA to Change the Pivot Table Source – 2 Methods

  The dataset contains Product ID, Product, category, sales of the products, Date, and Outlets of a grocery shop.   Method 1 - Using ...

0
How to Fit Long Text in One Cell in Excel – 6 Methods

Download Practice Workbook Download the worksheet here. Fit Long Text in One Cell.xlsx How to Fit Long Text in One Cell in Excel : 6 Simple ...

0
How to Use Excel VBA to Wait Until Refresh Is Complete

  Dataset Overview Consider the Profit and Loss (P&L) sheet for electronics companies. This sheet contains essential information such as Company ...

0
How to Create Timeline Slicer with Date Range in Excel: 4 Methods

Method 1 - Insert a Pivot Table Select the data range B4:E13 to build the pivot table Select Insert >> PivotTable from the Toolbox as below. ...

0
Excel If Cell Begins with Certain Text Then Return Value

Consider the following dataset of products and their IDs. We can search for a specific product using a predetermined value or a given value in a cell. ...

0
How to Use CHOOSE Function in Excel?

Below is an overview of our sample dataset. Example 1 - Using CHOOSE Function to Get Data from Selected Range The CHOOSE function will return the ...

Browsing All Comments By: Afrina Nafisa
  1. Hello ROB,
    Hope you are doing well. The behavior you describe is how a scanner or browser shows the URL while scanning. The QR codes contain the full URL within them, and the scanner opens the full URL.
    However, some scanners display the base domain of the URL for user-friendliness and simplification. Unfortunately, there is no direct solution but you can use online URL decoder to see the encoded URL before opening or try different scanners to get the full URL while scanning the QR code.

    I hope this will help you to solve your problem. Please let me know in the comment section if there are any other queries.

    Regards
    Afrina Nafisa
    Exceldemy

  2. Hello RENAE HINES,

    Hope you are doing well. Thank you for your query. You can create a dynamic checklist using Excel’s built-in feature such as Data Validation.

    • Initially, select cell E5 and go to Data >> Data Tools >> Data Validation to get the particular color.

    • Then, Data Validation dialog box will pop up. Select List from the drop-down menu of Validation criteria and Source as E14:E15.
    • Lastly, Click OK to complete the Data validation process.
    • Here, the process of selecting color is dynamic as below.

    • Finally, enter the below formula in cell E6 to complete the dynamic checklist of getting task colorwise.

    =FILTER($C$5:$C$16,EXACT($B$5:$B$16,E5))

     

  3. Hello Rick Howard, Thank you for your query. Therefore, method 3 is updated in the article. Now you may find any champion team or any runner Up team of any random League using The VLOOKUP function and the MATCH function.

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

    Calculating commission

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

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

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

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

  10. Hello JACOB FLOYD,
    Thanks for your valuable contribution. Now this method is added to this article. Please let us know if you have any queries regarding this article.

  11. Hello, Kevin King. I am glad that you liked this article and this article helped you when you needed the most. Hopefully, we will help you more with our other articles. Please leave a message if there is any other query.

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

  13. 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")
       Set nwk = Workbooks.Add
    For Each wsht In ThisWorkbook.Worksheets
    If IsInArray(wsht.Name, sheetNames) Then
    wsht.Copy After:=nwk.Worksheets(1)
           End If
       Next wsht
       Application.DisplayAlerts = False
       nwk.Worksheets(1).Delete
       Application.DisplayAlerts = True
       For Each wsht In nwk.Worksheets
           wsht.UsedRange.Value = wsht.UsedRange.Value
       Next wsht
       nwk.SaveAs "C:\Users\User1\Downloads"
       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.

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

    Also, follow the code that is already used in this article to convert “8233 CR 8233 CR” into one barcode

     

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

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

 

 

ExcelDemy
Logo