It's a common practice to sort data in Excel by a particular column header name, especially when working with large datasets. Excel has a built-in sorting ...

Excel VBA enables users to automate processes and increase productivity. Working with huge datasets and needing to modify cells in close proximity to the ...

Are you curious to establish a correlation between the variables in your data? Look no further! In this article, we'll explore how to find the correlation ...

InputBox in Excel VBA is a helpful tool for receiving user input. Sometimes it is necessary to limit the input to a specific data type, like numbers. In this ...

The numerous functionalities of Excel VBA can automate various processes and simplify your workflow. One such feature is the MsgBox function, which shows the ...

Undoubtedly, Microsoft Excel is a ubiquitous tool for analyzing data and solving complex problems. Now, wouldnâ€™t it be great if we could make a calculator for ...

Admittedly, Named Ranges are a quirky and often misunderstood feature of Excel that most people think is pointless. In reality, the issue is that few people ...

Certainly, Microsoft Excel is a handy and popular tool for analyzing large sets of data. Now, if you are looking to calculate your Chattel Mortgage, then this ...

Certainly, Microsoft Excel is a popular tool for arranging and manipulating data. Now, what if you need to check for blank cells? With this question in mind, ...

Unquestionably, Microsoft Excel excels at crunching numbers! So, this means we can perform tedious calculations like computing the weighted averages in the ...

Undoubtedly, Excelâ€™s forecasting functions are very useful since they can predict future output based on past results. However, like everything else in this ...

Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means that you can perform tedious calculations, like Filter Date Ranges, in the blink ...

Undoubtedly, Excelâ€™s powerful graphing feature can add visual depth and clarity to even the most mundane of datasets, while doing most of the heavy lifting for ...

Certainly, while working in Excel, we often have dates in the dataset and luckily, Excel has a palette of functions for dealing with dates in the dataset. In ...

Undoubtedly, Excelâ€™s ability to crunch numbers and plot three-dimensional graphs can help us better understand and represent the volatility of stocks. With ...

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

Hello Philip Earland,

Thank you for your query. You can find the answer to your question in the Excel file linked to this message. The steps are described below.

Bank Reconciliation.xlsx

Consider the transactions from â€śMetro Bankâ€ť to â€śLloyds Bankâ€ť. We can see the closing balances are not the same and need to be reconciled.

Select the transaction dates in the

B10:B19cells >> click theConditional Formattingdrop-down >> go toNew Rule.Select

Use a formula to determine which cells to formatoption >> enter the formula given below >> select fill color, here weâ€™ve chosen the color “Orange, Accent 2, Lighter 80%”.`=NETWORKDAYS($B10,$B10,$B$22:$B$29)=0`

Here, the highlighted transactions were processed by â€śMetro Bankâ€ť just before the weekends or holidays, so they were not recorded in the â€śLloyd Bankâ€ť statement.

Next, move to the â€śMismatchâ€ť worksheet and apply the

MATCHfunction to find the discrepancies between the two statements. Here the#N/Aerrors are the mismatches.Then, use the

Filteroption in theDatatab to show only the mismatches.Enter the mismatched account names and their amounts >> apply the expression below to reconcile the differences.

`=I5+I7+I8-I10-I11`

Hope this helps. Have a good day.

Regards,

Exceldemy

Hello Jeff,

Thank you for your query. You can find the answer to your question in the Excel file linked to this message.

Sort Alphanumeric.xlsx

Follow the steps shown below to sort alphanumeric data.

Enter the formula into the

E5cell to convert the alphanumeric data to numeric data >> drag theFill Handletool to copy the formula to the cells below.`=VALUE(CONCATENATE(COLUMN(INDIRECT(LEFT(D6,2)&1)),RIGHT(D6,LEN(D6)-1)))`

Now, select the

E5:E13range >> clickSort & Filter>> selectSort Smallest to Largest.A warning box appears,

Expand the selectionoption is checked by default >> click theSortbutton.Thatâ€™s it the data will be sorted.

Hope you find this useful. Have a good day.

Regards,

ExcelDemy.

Hello Nimesh,

Thank you for your query. You can find the answer to your question in the Excel file linked to this reply.

Counting Significant Figures.xlsx

Here is a screenshot of the results in the Excel file.

Hope you this useful. Have a good day.

Regards,

ExcelDemy

Hello Saccharine,

Thank you for your query. You can find the answer to your question in the Excel file attached to this message.

How to Find Values of Multiple Letters.xlsx

Here is a sample image of the Excel file.

Hope this helps. Have a good day.

Regards,

Exceldemy

Hello Avinash,

Thank you for your query. We have attached an Excel file with the answer to your question. Make sure to download the file.

Late Payment Interest Calculator.xlsxHere is a snapshot from the Excel file.

Hope this helps. Have a good day.

Regards,

ExcelDemy

Hello TomĂˇs Limeme,

Thank you for your feedback. The goal of a bank reconciliation is to identify and adjust any difference between the closing balances of our cashbook and bank statement over a specific period. To do this, we add or subtract any unrecorded transaction from our unadjusted closing balance.

In short, adding and subtracting ensures the matching of the bank statement and cashbook balances. This is important for proper financial reporting and to avoid mistakes or fraud.

Let’s review some of the transactions that are added, followed by the transactions that are subtracted.

Examples of transactions that are added include:

1. Deposits in transit: Funds transferred to the bank account but not yet entered into the accounting system.

2. Bank errors: Mistakes made by the bank, such as incorrectly recorded deposits or credits.

3. Earned interest: Interest on an account that hasn’t been entered into the accounting system.

On the other hand, subtracted transactions are:

1. Outstanding checks: Checks that have been written but have not yet been cashed by the bank.

2. Bank fees: Charges made by banks that are not accounted for in the accounting system.

3. Not Sufficient Fund checks: Checks that the bank returned due to insufficient money in the account of the issuer.

Hope this helps, have a good day.

Regards,

ExcelDemy

Hello AFIF,

Thank you for your question. Yes, we can perform the vehicle life cycle cost analysis for a duration of 25 years; just change the period of ownership to 25 and Excel should automatically show the updated results.

We have also included the Excel file with this message for you to download.

Vehicle-Life-Cycle-Cost-Analysis.xlsxRegards,

ExcelDemy

Thank you for your inquiry. Regrettably, Microsoft Word does not have the capability to perform a mail merge with Excel charts. Although there are third-party add-ins that claim to provide this functionality, we cannot vouch for their security, and therefore, we do not recommend their use.

Perhaps, you may post this query on Microsoft Community, where experts can direct you to a trusted alternative.

Regards,

ExcelDemy

Hello Bruce,

Thank you for your feedback and for pointing out the error. In this case, instead of

Couponsit should beCoupon Paymentwhich in this case has been considered$30.0as shown in the updated picture below.Hopefully, this clears out any confusion and we are sorry for this error.

Regards,

ExcelDemy

Hello KC,

Thanks for the feedback. It appears that the

INDIRECTfunction is unable to return the sum of two columns from multiple worksheets, even though it works fine for a single column. Rather, we can use theXLOOKUPandSUMfunctions to get the results.You can download the Excel file included in this reply.

SUMIF Across Multiple Sheets.xlsx

Consider the

Sales Performance dataset for Los Angeles, likewise, we have theSales Performance dataset for New York.The screenshot below shows the aggregate sales for each salesman using the

SUMandXLOOKUPfunctions.`=SUM(XLOOKUP(B5,'Sales Data LA'!$B$4:$B$9,'Sales Data LA'!$C$4:$E$9), XLOOKUP(B5,'Sales Data NY'!$B$4:$B$9,'Sales Data NY'!$C$4:$E$9))`

Regards,

ExcelDemy

Hello CHRISP,

Thank you for your question, we have included a screenshot of the results and you can find the Excel file linked below.

Calculating Simple interest.xlsx

Regards,

ExcelDemy

Hello RICHARD MUTALE BWALYA,

Thank you for your question. Changing a fixed-rate loan to a variable rate depends on the terms and conditions of the loan agreement, in addition to the applicable laws and regulations in the jurisdiction where the loan was made.

So, we believe it is best that you contact a financial advisor who can suggest the proper course of action based on your agreement.

Regards,

ExcelDemy

Hello KLC,

Thank you for your feedback. Unfortunately, weâ€™re having trouble accessing the pictures in the link, so you can attach your Excel workbook and send it to us at [email protected].

Regards,

ExcelDemy

Hello SAM,

We have attached an Excel file with this reply which you can download from the link below.

Print-Sheets-Using-Excel-Button.xlsm

Here, weâ€™ve modified the code such that it automatically lists the names of the worksheets when opened. You can select multiple worksheets and press the print button to print them.

Regards,

ExcelDemy

Hello JOE,

Thank you for your feedback. Did you try the solutions suggested in the article and did they work before 6 February 2023?

If the solutions worked before, then you may need to repair your Microsoft Office application.

Hopefully, this resolves the problem.

Regards,

ExcelDemy

Hello ROSEMARIE LINFOOT,

We have attached an Excel workbook with the necessary instructions to this reply. You can download the file using the link below.

Loan-Amortization-Schedule-with-Variable-Interest-Rate-And-OCR.xlsxIf you have further queries please attach a sample workbook and contact us at

[email protected].Regards,

ExcelDemy

Hello DAN,

Firstly, we would like to apologize for the trouble. As you pointed out, the use of the GEOMEAN function does not return the correct answer. In fact, we can apply the PRODUCT function as shown below.

`=PRODUCT(G5:G7)-1`

Here, the

G5:G7range refers to the values in theâ€ś1+Returnâ€ťcolumn.Regards,

ExcelDemy

Hello SHABBIR,

You can follow a step-by-step guide or use the free template for your simple accounting system by following this article on How to do Bookkeeping for Small Businesses.

Regards,

ExcelDemy

Hello DONNA ATKINS,

Thank you for your feedback, the answer to your question is provided in the steps below, so follow along.

Step 1.First, completesteps 1 through 5 from Method 1>> now, follow the steps shown in the live demonstration.Step 2.Next, click onClose & Loaddrop-down >> selectClose & Load tooption.Step 3.Lastly, choose theTable or PivotTableoption according to your preference >> load this data into a new worksheet.Hopefully, this solves your problem. Have a good day.

Hello KAREN W,

First of all, we would like to apologize for the trouble. As you pointed out, there were in fact some issues with the cell referencing in the Regression mathod, luckily they have been updated.

The Exceldemy team is grateful to you for sharing your thoughts and feedback. Hopefully, now you can obtain the desired result.

Hello JB,

Thank you for your feedback. Admittedly, like everything else, Excel has its downsides too and sometimes the solution to a problem can be quite surprising, but whatever works! Right?

That said, weâ€™re delighted that youâ€™ve shared your experience with us, hopefully, other people find this useful. Have a good day.

Hello Behzad,

Thank you for your question. Weâ€™re sorry to hear that youâ€™re facing difficulties with the formula. In fact, the ExcelDemy team has tested the Excel file following your comment and the formula appears to be working correctly.

That said, it would be helpful if you could send us a screenshot of the issue that youâ€™re experiencing.

Hello S,

Thank you for your question. Firstly, I would like to apologize for the misunderstanding. Admittedly, the method in question is in fact a

quick and dirtyway to manually insert page numbers if your document contains only a handful of pages.That said, the widely accepted process of inserting page numbers in Excel is described in

Method 4. Hopefully, this helps.Hello Jamil Khan,

Thank you for your question. Actually, this is how the

RANKfunction works, that is to say, it ranks the duplicate values in ascending or descending order according to the given argument. Now, to have the same ranks for identical values you can follow Method 1 or download the Excel file that the ExcelDemy team has created.Download the Excel File below.

Ranking Duplicates.xlsxHello Phong,

Thank you for your suggestion and for taking the trouble to provide a great solution to Kate’s problem. We, the ExcelDemy team really appreciate your effort and as a result, weâ€™ve updated our article to include the solution that youâ€™ve provided.

Hello P.KUIPERS,

Thank you for your question. The ExcelDemy team has created an Excel file with the solution to your question that you may download from the link below.

Rename_Sheets.xlsm

You can download the practice files from the link below

Rename_Sheets_Do_Yourself.xlsm

Otherwise, you can just follow the steps below.

In order to rename the sheets in a sequential way, you can use another VBA Macro. So, letâ€™s see the step below.

Step: 1a. Firstly, navigate to the

Developertab >> click the Visual Basic button. This opens theVisual Basic Editorin a new window.b. Next, go to the

Inserttab >> selectModule.For your ease of reference, you can copy the code from here and paste it into the window.

Sub Rename_Multiple_Sheets()

Alphabets = Array(“A”, “B”, “C”, “D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”)

Days = Array(“Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”)

Dim Weekdays(5) As String

For i = 0 To 4

Weekdays(i) = Days(i)

Next i

Months = Array(“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)

Old_Names = InputBox(“Enter the Names of the Worksheets to Change (Separate them be Commas).” + vbNewLine + “OR” + vbNewLine + “Enter ALL to change all the worksheets.”)

If Old_Names = “ALL” Then

Dim Old_Sheets() As String

ReDim Old_Sheets(Sheets.Count – 1)

For i = 0 To Sheets.Count – 1

Old_Sheets(i) = Sheets(i + 1).Name

Next i

Else

Old_Sheets = Split(Old_Names, “,”)

End If

Dim Used_Names() As String

ReDim Used_Names(0)

Dim Sign As Integer

Sequential_Or_Random = Int(InputBox(“Enter 1 to Change the Worksheet Names in a Sequential Way: ” + vbNewLine + “OR” + vbNewLine + “Enter 2 to Change the Worksheet Names in a Random Way: “))

If Sequential_Or_Random = 1 Then

Series = Int(InputBox(“Enter 1 to Change the Names to a Series of Numbers: ” + vbNewLine + “Enter 2 to Change the Names to a Series of ALphabets: ” + vbNewLine + “Enter 3 to Change the Names to a Series of Days: ” + vbNewLine + “Enter 4 to Change the Names to a Series of Weekdays: ” + vbNewLine + “Enter 5 to Change the Names to a Series of Months: “))

If Series = 1 Then

Prefix = InputBox(“Enter the Prefix before the Numbers: “)

First_Number = Int(InputBox(“Enter the First Number: “))

Increment = Int(InputBox(“Enter the Increment: “))

For i = 0 To UBound(Old_Sheets)

Sheets(Old_Sheets(i)).Name = Prefix + Str(First_Number + Increment * (i))

Next i

ElseIf Series = 2 Then

Prefix = InputBox(“Enter the Prefix before the Letters: “)

First_Letter = InputBox(“Enter the First Letter: : “)

Increment = Int(InputBox(“Enter the Increment: “))

Dim Case_Identifier As String

For i = 0 To UBound(Alphabets)

If Alphabets(i) = First_Letter Then

First_Letter_Number = i

Case_Identifier = “U”

Exit For

ElseIf LCase(Alphabets(i)) = First_Letter Then

First_Letter_Number = i

Case_Identifier = “L”

Exit For

End If

Next i

For i = 0 To UBound(Old_Sheets)

Sign = 0

For j = 0 To UBound(Used_Names)

If Alphabets((First_Letter_Number + (Increment * i)) Mod 26) = Used_Names(j) Then

Sign = Sign + 1

End If

Next j

If Sign = 0 Then

If Case_Identifier = “U” Then

Sheets(Old_Sheets(i)).Name = Prefix + Alphabets((First_Letter_Number + (Increment * i)) Mod 26)

Else

Sheets(Old_Sheets(i)).Name = Prefix + LCase(Alphabets((First_Letter_Number + (Increment * i)) Mod 26))

End If

Else

If Case_Identifier = “U” Then

Sheets(Old_Sheets(i)).Name = Prefix + Alphabets((First_Letter_Number + (Increment * i)) Mod 26) + ” (” + Str(Sign) + “)”

Else

Sheets(Old_Sheets(i)).Name = Prefix + LCase(Alphabets((First_Letter_Number + (Increment * i)) Mod 26)) + ” (” + Str(Sign) + “)”

End If

End If

ReDim Preserve Used_Names(i)

Used_Names(i) = Alphabets((First_Letter_Number + (Increment * i)) Mod 26)

Next i

ElseIf Series = 3 Then

First_Day = LCase(InputBox(“Enter the First Day: : “))

Increment = Int(InputBox(“Enter the Increment: “))

For i = 0 To UBound(Days)

If LCase(Days(i)) = First_Day Then

First_Day_Number = i

Exit For

End If

Next i

For i = 0 To UBound(Old_Sheets)

Sign = 0

For j = 0 To UBound(Used_Names)

If Days((First_Day_Number + (Increment * i)) Mod 7) = Used_Names(j) Then

Sign = Sign + 1

End If

Next j

If Sign = 0 Then

Sheets(Old_Sheets(i)).Name = Days((First_Day_Number + (Increment * i)) Mod 7)

Else

Sheets(Old_Sheets(i)).Name = Days((First_Day_Number + (Increment * i)) Mod 7) + ” (” + Str(Sign) + “)”

End If

ReDim Preserve Used_Names(i)

Used_Names(i) = Days((First_Day_Number + (Increment * i)) Mod 7)

Next i

ElseIf Series = 4 Then

First_Weekday = LCase(InputBox(“Enter the First Day: : “))

Increment = Int(InputBox(“Enter the Increment: “))

For i = 0 To UBound(Weekdays)

If LCase(Weekdays(i)) = First_Weekday Then

First_Weekday_Number = i

Exit For

End If

Next i

For i = 0 To UBound(Old_Sheets)

Sign = 0

For j = 0 To UBound(Used_Names)

If Weekdays((First_Weekday_Number + (Increment * i)) Mod 5) = Used_Names(j) Then

Sign = Sign + 1

End If

Next j

If Sign = 0 Then

Sheets(Old_Sheets(i)).Name = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5)

Else

Sheets(Old_Sheets(i)).Name = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5) + ” (” + Str(Sign) + “)”

End If

ReDim Preserve Used_Names(i)

Used_Names(i) = Weekdays((First_Weekday_Number + (Increment * i)) Mod 5)

Next i

ElseIf Series = 5 Then

First_Month = LCase(InputBox(“Enter the First Month: “))

Increment = Int(InputBox(“Enter the Increment: “))

For i = 0 To UBound(Months)

If LCase(Months(i)) = First_Month Then

First_Month_Number = i

Exit For

End If

Next i

For i = 0 To UBound(Old_Sheets)

Sign = 0

For j = 0 To UBound(Used_Names)

If Months((First_Month_Number + (Increment * i)) Mod 12) = Used_Names(j) Then

Sign = Sign + 1

End If

Next j

If Sign = 0 Then

Sheets(Old_Sheets(i)).Name = Months((First_Month_Number + (Increment * i)) Mod 12)

Else

Sheets(Old_Sheets(i)).Name = Months((First_Month_Number + (Increment * i)) Mod 12) + ” (” + Str(Sign) + “)”

End If

ReDim Preserve Used_Names(i)

Used_Names(i) = Months((First_Month_Number + (Increment * i)) Mod 12)

Next i

End If

ElseIf Sequential_Or_Random = 2 Then

New_Names = InputBox(“Enter the New Names (Separate them by Commas): “)

New_Sheets = Split(New_Names, “,”)

For i = 0 To UBound(Old_Sheets)

Sign = 0

For j = 0 To UBound(Used_Names)

If New_Sheets(i) = Used_Names(j) Then

Sign = Sign + 1

End If

Next j

If Sign = 0 Then

Sheets(Old_Sheets(i)).Name = New_Sheets(i)

Used_Names(Count) = New_Sheets(i)

Count = Count + 1

Else

Sheets(Old_Sheets(i)).Name = New_Sheets(i) + ” (” + Str(Sign) + “)”

End If

ReDim Preserve Used_Names(i + 1)

Used_Names(i + 1) = New_Sheets(i)

Next i

End If

End Sub

Step: 2a. Secondly, close the

Visual Basic Editor>> in the topRibbon, click the Macros button >> Now, select theRename_Multiple_Sheets Macroand pressRun.b. This opens up a few input boxes. The inputs are described in the step below therefore just follow these steps.

Step: 3a. The

firstInput Box will ask you to enter the name of the sheets that you want to change. Since you want to rename your worksheets toSheet1, Sheet2,etc. you can type inALL.b. The

secondInput Box will ask you whether you change the sheet names in a sequential way or in a random way. In this case, you enter1.c. If you go for a sequential way, the

thirdInput Box will ask for a series of values from the options below. Now, enter 1 for a series of Numbers (1, 2, 3, etc.)d. Next, enter a

Prefixbefore the numbers, for instance,â€śSheetâ€ť.e. Then, give the

Starting Number, in this case,1.f. Lastly, provide the

Incrementfor the numbers, for example, you can choose1.Voila! All your sheets are numbered serially as

Sheet 1, Sheet 2,etc.If you wish you can learn more about this VBA Code in this article.

Hello Julie Parker,

Thank you for your question. I have looked into this matter, and so far, I havenâ€™t been able to find a solution to your particular query. In the meantime, I have prepared an excel template with a table of contents that you may download from the link below. That said, I will let you know when I find a solution. I hope this was helpful.

Table of Contents.xlsx

Hello RAY,

Thank you for your question. The Exceldemy team has created an Excel file with the solution to your question. Please provide your email address here, we will send it to you in no time.

Otherwise, you can just follow the steps below.

Suppose we want to use the

MID Functionas shown inMethod 1. Now, we want to determine the age of a person whoseID Numberstarts with00(which refers to theYear 2000) but that person was born after theYear 2000.Step: 1

â€˘ Firstly, letâ€™s consider

Marywith theID Numberto be‘0005255800012.â€˘ As a note, Excel removes any leading zeros from numbers so we have inserted an apostrophe comma to store the

ID Numberas text.Step: 2

â€˘ Secondly, letâ€™s assume

Marywas born in theYear 2003.â€˘ Now, on the

Date of Birthcolumn insert the formula given below.`=MID(C14,5,2)&"/"&MID(C14,3,2)&"/"&"0"&MID(C14,1,2)+3`

â€˘ You should see the result as

25/05/03.Step: 3

â€˘ Next, AutoFill the

Current DateandAgecolumns.â€˘ The value of

Ageshould be19years.Similarly, we have also included a second example for

Julianwith theID Number‘0108295800012but he was born in theYear 2006.Please feel free to provide any further feedback.

Hello KATE,

Thank you for your question. You can reduce a fraction to its lowest term by specifying the format style of the TEXT function to: =INT(C5) & ” ft ” &TEXT(MOD(C5,1)*12, â€ś000/00â€ť.) & “in”

Hello FELICIA FOO,

Thank you for your question. You can find the average of a group by right-clicking on the Row Labels (Sum of Sales) and selecting the Value Field Settings option. Next, in the Summarize value field by list, youâ€™ll find Average.