About author
## Designation

Team Leader at ExcelDemy in SOFTEKO.
## Lives in

Dhaka, Bangladesh.
## Education

B.Sc. in Civil Engineering, BUET.
## Expertise

C, C++, ETABS, AutoCAD, SAP, SketchUp, Microsoft PowerPoint, Microsoft Excel, Microsoft Word, MATLAB
## Experience

## Summary

## Research & Publication

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards, Power Query, and Excel VBA.

- Technical Content Writing
- Team Management
- Voluntary Works:
- Eccentric: Civil Engineering Festival 2018, Organized by the Department of Civil Engineering, BUET.
- 2nd EW: B National Enviro+ challenge 2017 Organized by Environment Watch: BUET

- Currently working as Team Leader of ExcelDemy.
- Started technical content writing of Excel & VBA for ExcelDemy in March 2023.

- “A Study of The Relationship between Undrained Shear Strength and Preconsolidation Pressure of Normally Consolidated Clays” under Dr. Mohammed Kabirul Islam, Professor, Civil Engineering Department, BUET.

The texts or symbols we see on our computer screens are stored in different forms on the back end. The numerical values are stored with some special characters ...

Mahfuza Anika Era
Jul 22, 2024

Download Practice Workbook Delete Sheet.xlsm Example 1 - Use the Home Tab Click the sheet you want to delete. Keep it as the active sheet. ...

Mahfuza Anika Era
Aug 14, 2024

Consider the following dataset. It contains 60 rows and 11 columns. The top row is frozen. How to Freeze Rows in Excel 1. Freeze ...

Mahfuza Anika Era
Aug 15, 2024

PivotTable - Basic Things A PivotTable is a powerful data analysis tool in Microsoft Excel. It allows users to quickly summarize, organize, and gain insights ...

Mahfuza Anika Era
Jul 28, 2024

Returning all rows that match criteria in Excel means showing the rows in a dataset that meet specific conditions. For example, this is a dataset showing ...

Mahfuza Anika Era
Apr 19, 2024

How to Launch VBA Code Editor The most commonly used method to open the VBA editor is Developer Tab >> Visual Basic or to press ALT+F11. Go to ...

Mahfuza Anika Era
Jul 4, 2024

Here's a dataset of sales data for a grocery shop. In the Date & Time of Sale column, users have to enter the date and time manually. This is not ...

Mahfuza Anika Era
Jun 14, 2024

How to Launch a VBA Editor in Excel To launch the VBA Editor, click on the Developer Tab from Excel Ribbon and select the Visual Basic option. If your Excel ...

Mahfuza Anika Era
Jul 3, 2024

This is an overview: The Stock to Sales Ratio The stock to sales ratio is calculated by dividing the value of the average inventory held during a ...

Mahfuza Anika Era
Aug 13, 2024

Example 1 - Use the Excel ISBLANK Function in a Range to Know If a Cell Is Blank We have a list of values in B5:B9. Use the formula in cell C5. Write the ...

Mahfuza Anika Era
Jul 16, 2024

How to Launch a VBA Editor in Excel Click on the Developer Tab from Excel Ribbon. In case you don’t have the Developer Tab in Excel Ribbon, follow ...

Mahfuza Anika Era
Jun 10, 2024

Consider the following dataset of a manufacturing company. We'll use it to demonstrate how to create a material reconciliation template. How ...

Mahfuza Anika Era
Apr 23, 2024

This is an overview. Method 1- Using Excel 2016-365 Versions to Create a Pareto Chart with the Cumulative Percentage in Excel The dataset ...

Mahfuza Anika Era
Aug 13, 2024

Linear Programming is very useful in terms of resource optimization. In this article, we will demonstrate how to find an optimal solution in linear ...

Mahfuza Anika Era
Jul 29, 2024

Browsing All Comments By: Mahfuza Anika Era

Hello

TERRY,Thank you for your comment. Yes, this is possible to do with selected cells only. To do this, you can use the third method, which is using a

VBAcode.First, you have to select the cells. Then go to the

Developertab >>Visual Basic.Then, run the

VBAcode that is provided in method3.A dialog box will appear, showing the selected cell range. Now, save the file by following the given instructions in method

3.Open the file with

NotepadorWordpad, and you will see, the selected cells are only saved with double quotes.Regards

Mahfuza Anika EraExcelDemy

Hello

GUILLERMO ALCALA,Thank you for your comment. In the initial example,

Jim Brown,Robert Smith, andHenry Jamesscored65inScience. So if you rank them according to the score ofScience, you will get repeated ranks. But, as you can see, these 3 students got different scores inPsychology. So, we have ranked them according to theEcolumn (Psychology). Hence, the rank is not repeated, and they got different ranks according toPsychologyscore.Regards

Mahfuza Anika EraExceldemy

Hello

RUTH MORAN,Thank you for your comment. Unfortunately, it is not possible to print an Excel document starting on the

thirdpage as number1. You can leave the first page and start the page number from the second page. But the number on thesecondpage will show “2“.Regards

Mahfuza Anika EraExceldemy

Hi

P.Chan,The scanner cannot scan or scan wrong information as the text the code is generating is wrong in your case. I have tried the code and it is working properly.

As for why the code isn’t running properly in your system is a bit complicated to trace. For starters, try troubleshooting and retry the code again.

Regards

NiloyExcelDemy

Hello

BRAM,If you are using the VBA option, the colour of the table header should be automatically changed to the previous colour right after selecting another cell. Please check if you have written the code correctly. Following is the gif for your understanding.

If your problem is not yet solved, please join our

ExcelDemy Forumand post this problem with your Excel file attached to it.Regards,

Mahfuza Anika EraExceldemy

Dear

NAM,Thank you for engaging with our article and I appreciate your insightful observation. Allow me to address your query and offer clarification on the terminologies utilized in the formulas.

In the article, the term “Coupon Rate” refers to the fixed coupon rate applied to the principal amount during the last fixed period before the bond transitions to a floating rate. Conversely, “Market Rate” refers to the variable interest rate set by the market for the floating periods.

I acknowledge your concern regarding the terms, and I wish to explain that the

Cash Flowcomputation entails multiplying thePrincipalby theLast Fixed Coupon Ratefor the initial fixed period. Subsequently, for the floating periods, theCash Flowis calculated by multiplying thePrincipalby the respectiveMarket Ratesfor each year.In the

Discounted Cash Flowcalculation, the appropriate discount rates used are theMarket Ratesfor every corresponding year. This aligns with the procedure of discounting future cash flows to their present value using applicable interest rates.I hope this clarification addresses your concerns. If you have any further questions, please feel free to let me know.

Kind Regards,

Sumaiya MirzaExcelDemy

Hello

Roland Sprague,The MMULT functionof Excel represents the matrix multiplication we perform in mathematics. We can use this function if there happens to be two ranges in a sheet representing two matrices and we want to multiply them.You can find out more about matrix multiplication in

this sectionof the article. It is helpful for computing in linear algebra, transformation of coordinate systems, population modeling, etc.Regards

NiloyExcelDemy

Dear

BREANNA,Thank you for your positive feedback and I appreciate the opportunity to assist you further.

To accommodate random additional payments, you can follow these steps below:

First, insert a new column next to your dataset in column I and label it as “Additional Payment.” Next, in each row under the “Additional Payment” column, input the amount of any extra payment made for that particular month. Every month, these extra payments can be random.

Modify the formulas in “Payment Amount” columns C, E, and G to include the additional payments. For example, update the formulas to incorporate the extra payment like this:

`=IF((D13-$E$4-$D$9-$I13)<=0,($E$4+(D13-$E$4)),($E$4+$D$9+$I13))`

Extend these adjustments to the formulas in columns

D,F, andHas well ensuring the formula incorporates the additional payments. Lastly, drag theFill HandletoAutoFillthe formulas in each column for subsequent months.Hopefully, this solution has met your requirements. If you have any further questions, please feel free to ask.

Kind regards,

Sumaiya MirzaExcelDemy

Hello

BRIAN,Thanks for your comment. You can apply two criteria by using

the SUMIF functiontwice in the formula. The formula is:`=SUMPRODUCT((SUMIF(B5:B15,B5:B15,C5:C15)<10000)*(SUMIF(B5:B15,B5:B15,C5:C15)>0)/COUNTIF(B5:B15,B5:B15))`

This will count the number of products whose total sum value is greater than

0and less than10000.Regards

Mahfuza Anika EraExcelDemy

Hello

ANGEL,Thank you for your query. Counting the sheet of the new big table (sheet1) can be ignored. Suppose, you have added a new table named “April” and you want to include it in the big table. Unfortunately, the mother table cannot be updated automatically. What you can do is to create another query after inserting a new sheet and table.

After adding a new sheet and table, follow the same process to pull data from different sheets using power query. But the problem is the previous query will be included in your new query. To remove that:

1.Click on the dropdown icon.

2.Uncheck

Query1>> PressOK.The new query is now removed.

3. Click on the following icon >>

Expand>>OK.The tables are expanded now. You can remove the marked column as it is not necessary.

4.Now, close and load the table in a new worksheet. So, the previous mother sheet (sheet 1) will not be included now.

You have to do this process every time you add a new sheet and table.

Regards

Mahfuza Anika EraExcelDemy

Dear

T,Thank you very much for your response. It means a lot to us.

We have changed several parts of the article according to your comment. Hope you will find the article helpful now.

Thanks

Wasim AkramExcelDemy

Hi

Zsolt!You are most welcome. It seems to me that you are trying to write a decimal number using a comma (,) instead of a decimal point(.). If you write the latitudes and longitudes as 42.328674 and -72.664658 instead of 42,328674 and -72,664658 the function should not return any error and you should get your expected result.

Regards,

NafisExcelDemy

Hello

Andrea!You could follow these alternative solutions below to solve your problem:

Remove any blank cells or non-date entries in the date column. Filter out non-date values and ensure that all cells in the date column contain valid date data.

Click on the drop-down arrow in the date filter, and check the filtering options. Make sure the desired date range or specific dates are selected. Adjust the filter criteria accordingly.

Refresh the pivot table to ensure it reflects the most recent changes in your data. Right-click on the pivot table and select Refresh.

Create a new pivot table and see if the date filter works. If it does, the original pivot table may be corrupted. Copy your settings to the new pivot table or recreate it.

Ensure that you are using a compatible Excel version for the features you are trying to use. Update Excel to the latest version if possible.

If your problem is not yet solved, please join our ExcelDemy Forum and post this problem with your Excel file attached to it.

Regards,

NafisExcelDemy

Hi

BARRY AYLETT-WARNER,At the beginning of Method 1, it was mentioned that “we have named the range of the ‘Fruits’ column with Fruits”, but the process wasn’t shown. Did you use the Named Range feature for your dataset? If not, first use the Named Range and then follow the steps as mentioned. Hope you find this helpful.

Regards

Rafiul HasanExcelDemy

Hi

Gabolete Fane,Glad this was helpful to you. “Balance stock” can technically become “opening stock”.

The balance stock refers to the inventory at the end of the period while the opening stock is the inventory at the beginning of a period. So, if your next period starts right after your current period, the balance stock becomes the opening stock. It depends on the time periods you are considering.

Regards

NiloyExcelDemy

Hi

Alonso,The solutions focus on working with the objects that are creating the issue. If your Excel file is frozen and you can’t perform any task, it is better to force quit the file using the Task Manager. Excel tends to autosave the files in those instances. If it doesn’t autosave, you may need to resort to third-party data recovery tools.

In case the problem occurs every time you open the file, try repairing the file or opening it in safe mode. If all of that fails and you need to extract the data, try opening the file in other spreadsheet applications like Google Sheets. That should retrieve most of the data and avoid adding the object.

Regards

NiloyExcelDemy

Hello YOGESH UTEKAR,

Thank you for your comment. To show the data for specific columns (For example: A,C,D,I,P) instead of the entire row you can use the following code.

I have highlighted the portions where I have made changes.

Best Regards,

Mahfuza Anika Era (ExcelDemy Team)

Hi TRICIA,

Thank you for your comment. To add the sales of JAN & FEB you can use this formula:

`=SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L9)*(B5:B14=L7))+SUMPRODUCT(D5:I14,(C5:C14=C10)*(D4:I4=L10)*(B5:B14=L7))`

Regards,

Mahfuza Anika Era

Exceldemy Team

Hi JAMZ,

Thank you for your comment. There is an error in the second portion of your formula. As your week starts on Monday, you must apply the WEEKNUM function’s

return typeas2. Here is the corrected formula:`=WEEKNUM(B1,2)-WEEKNUM(DATE(YEAR(B1),MONTH(B1),1),2)+1`

Regards

Mahfuza Anika Era

Exceldemy

Hello

MARIANOLI,Thank you so much for your comment. You can fix this problem by changing the date format in your computer’s date and time settings. Change the date format to

dd/MM/yy. Hopefully, you will get the dd/mm/yy format for the whole month.Regards

Mahfuza Anika Era

ExcelDemyDear NURIT,

Thank you so much for your comment. I have changed the date format to United States format in the code. You have to change the VBA code in the subroutine named

Private Sub Create_Calender(). Following is the code for your required date format.Here is the screenshot of the new code. I have marked the changes for your better understanding.

Regards

Mahfuza Anika Era

ExcelDemy

Dear Ahmad,

Thanks for your query.

In the VLOOKUP function #N/A error occurs when you type the wrong lookup value or worksheet name. So, check if you have given the lookup value and worksheet name properly.

Again, VLOOKUP can only look up values to the right of the lookup value. Be careful about this.

If you need further help, please mention details about your problem.

Regards

Mahfuza Anika Era

ExcelDemy

Dear Salad,

Thanks for your question. Here is the VBA code that will give you your mentioned output.

Following is the output after using the code.

Regards

Mahfuza Anika Era

ExcelDemy

Dear MARK,

Thank you for your query.

Here is the dataset I will use to show the solution to your problem.

After

creating a PivotTable, I havecopied the PivotTable5 times. So, there are 5 PivotTables in my worksheet now.Now, we have to

create a drop-down menufrom the list of PivotTables.Next, copy this VBA code into your VBA code editor. You have to change three things in this code. These are: the cell address of where you placed the drop-down menu, the filter values, and the field name that you want to filter.

To get the output, select the PivotTable from the drop-down which you want to filter, and then Run the code by pressing the F5 key.

For your convenience, I have given the Excel file:

Filtering PivotTable with drop-down menu.xlsmRegards

Mahfuza Anika Era

ExcelDemy

Dear Stuart,

I am glad that you find this article informative. Thank you for your query. The VBA code which I have inserted in step 4 is in

Sheet1under Microsoft Excel Objects Section.Mahfuza Anika Era

ExcelDemy

Dear Yosh,

Thank you for your query. Yes, you can determine the sum of YTD number. Firstly create a table like the following for each Month’s Sales of “Jimmy” for “Laptop”.

Copy this formula in cell C21.

`=SUMIFS(INDEX($D$5:$I$16,,MATCH(B21,$D$4:$I$4,0)),$B$5:$B$16,$B$20,$C$5:$C$16,C$20)`

You will get the sales for each month.

Next, to calculate the “YTD Grandtotal” copy this formula in cell C27.

`=SUM(C21:C26)`

I hope this method will solve your problem. Thank you!

Mahfuza Anika Era

ExcelDemy

Dear Yosh,

I assume, this question is same as the previous one. You can follow the steps I have given in the previous reply. If you still have any confusion, please leave a comment describing your problem. Thank you!

Mahfuza Anika Era

ExcelDemy

Dear L,

Thank you for your comment. This formula calculates a date value. Here is a breakdown of what each part of the formula does:

=$B$4-(WEEKDAY(C$3,1)-($AM$7-1))-IF((WEEKDAY($B$4,1)-($AM$7-1))<=0,7,0)+1$B$4: This is the reference to cellB4which contains the date value you enter inCustomize DatesTable.WEEKDAY(C$3,1): This function calculates the weekday of the date in cellC3. The argument 1 specifies that the weekday numbering should start on Monday (1) instead of Sunday (default value of 0).$AM$7: This is a reference to the cell containing a number that specifies the Start Month. (e.g. 1 for Monday, 7 for Sunday).IF((WEEKDAY($B$4,1)-($AM$7-1))<=0,7,0): This function checks whether the weekday of the date in cellB4is earlier in the week than the specified start day of the week. If it is, the function returns 7 (the number of days in a week) to adjust the date calculation later. If not, the function returns 0.B4-(WEEKDAY(C3,1)-($AM$7-1))-IF((WEEKDAY($B$4,1)-($AM$7-1))<=0,7,0)+1: This formula subtracts the weekday of the date in cellC3from the date in cellB4, then adds the start day of the week minus 1, and finally subtracts the result of the IF function. This calculates the first day of the week that contains the date in cellB4. The final +1 adds one day to get the actual start date of the week.If you want 4 weeks in one sheet, just add 3 more weeks similarly in the existing sheet. Hope this will help you.

Regards

Mahfuza Anika Era

ExcelDemy

Dear Max,

Thank you for your query. Changing the currency is not the reason behind the incorrect output of the

module 2code. Actually, the code is not working for the last3digits of the whole number part. Here is the modified code ofmodule 1that may help you. This will give the correct result hopefully.Copy the code in your module and Run the code.

I hope you have got your problem solved. Thank you.

Regards

Mahfuza Anika Era

ExcelDemy