User Posts: Eshrak Kader
0
How to Construct Cost Inflation Index Calculator in Excel
0

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

0
How to Display Named Range Contents in Excel (4 Quick Ways)
0

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

0
How to Make Chattel Mortgage Calculator in Excel (with Alternative)
0

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

0
How to Use ISBLANK Function to Check If Cell Is Blank in Excel
0

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

0
How to Calculate Weighted Average Interest Rate in Excel (3 Ways)
0

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

0
[Fixed!] FORECAST Function Not Accurate in Excel (5 Solutions)
0

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

0
How to Pull Data from a Date Range in Excel (7 Handy Ways)
0

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

0
How to Create a 3D Bar Chart in Excel (3 Suitable Examples)
0

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

0
How to Use EOMONTH Function in Excel (10 Ideal Examples)
0

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

0
How to Generate Volatility Surface in Excel (with Detailed Steps)
0

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

0
How to Use Scaling Option in Excel (4 Quick Ways)
0

Oftentimes, our Excel worksheets contain numerous columns which can throw a curveball when trying to print the spreadsheet on a single page. Fear not! Because ...

0
How to Build a Sample Sales Pipeline in Excel (with Easy Steps)
0

Certainly, having a clear overview of the leads and sales in a sales pipeline can improve sales and conversion rates. However, this can lead to paying large ...

0
How to Use Navigation Keys in Excel (6 Examples with Shortcuts)
0

Undoubtedly, we’ve all used Microsoft Excel at some point in our lives. Now, to improve our productivity and time management when working in Excel, we’ll have ...

0
How to Perform Outer Join in Excel (2 Easy Ways)
0

Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means that you can merge datasets and perform tedious calculations in the blink of an ...

0
How to Disable Alerts in Excel VBA (With Easy Steps)
0

Certainly, VBA Macros in Microsoft Excel, help to solve various problems and automate repetitive tasks. Though alerts are intended to warn users of their ...

Browsing All Comments By: Eshrak Kader
  1. 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, complete steps 1 through 5 from Method 1 >> now, follow the steps shown in the live demonstration.
    steps to expand table in power query

    Step 2. Next, click on Close & Load drop-down >> select Close & Load to option.
    Using Close & Load to option

    Step 3. Lastly, choose the Table or PivotTable option according to your preference >> load this data into a new worksheet.
    Loading table to new worksheet

    Hopefully, this solves your problem. Have a good day.

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

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

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

  5. 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 dirty way 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.

  6. Hello Jamil Khan,
    Thank you for your question. Actually, this is how the RANK function 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.xlsx

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

  8. 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: 1
    a. Firstly, navigate to the Developer tab >> click the Visual Basic button. This opens the Visual Basic Editor in a new window.
    b. Next, go to the Insert tab >> select Module.
    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: 2
    a. Secondly, close the Visual Basic Editor >> in the top Ribbon, click the Macros button >> Now, select the Rename_Multiple_Sheets Macro and press Run.
    b. This opens up a few input boxes. The inputs are described in the step below therefore just follow these steps.

    Step: 3
    a. The first Input Box will ask you to enter the name of the sheets that you want to change. Since you want to rename your worksheets to Sheet1, Sheet2, etc. you can type in ALL.
    b. The second Input Box will ask you whether you change the sheet names in a sequential way or in a random way. In this case, you enter 1.
    c. If you go for a sequential way, the third Input 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 Prefix before the numbers, for instance, “Sheet”.
    e. Then, give the Starting Number, in this case, 1.
    f. Lastly, provide the Increment for the numbers, for example, you can choose 1.
    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.

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

  10. 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 Function as shown in Method 1. Now, we want to determine the age of a person whose ID Number starts with 00 (which refers to the Year 2000) but that person was born after the Year 2000.

    Step: 1
    • Firstly, let’s consider Mary with the ID Number to be ‘0005255800012.
    • As a note, Excel removes any leading zeros from numbers so we have inserted an apostrophe comma to store the ID Number as text.

    Step: 2
    • Secondly, let’s assume Mary was born in the Year 2003.
    • Now, on the Date of Birth column 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 Date and Age columns.
    • The value of Age should be 19 years.

    Similarly, we have also included a second example for Julian with the ID Number ‘0108295800012 but he was born in the Year 2006.

    Please feel free to provide any further feedback.

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

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

ExcelDemy
Logo