0

## How to Change Comma Style in Excel (4 Quick Ways)

When working with financial data in Excel, the comma style is necessary, since adding commas between the digits makes it easier to understand the data, ...

0

## How to Find Average If Cell Contains Text in Excel (4 Useful Ways)

In our daily lives, calculating the average of cells containing specific text is a typical chore, and this is where Microsoft Excel shines. In the following ...

0

## How to Find Average of Specific Cells in Excel (3 Handy Ways)

Calculating the average of specific cells is a common task in our day-to-day lives and this is where Microsoft Excel excels. In this article, we’ll show 3 ways ...

0

## How to Use VLOOKUP with Multiple Criteria in Different Columns

Whilst working in Microsoft Excel, we’ve all faced a scenario where we need to look up a value from a dataset based on a few criteria. Now, even with a small ...

0

## How to Remove a Form Control in Excel (3 Quick Ways)

Excel’s Form Controls feature adds extra functionality and allows the user to interact with the worksheet. However, you might need to remove them from your ...

0

## How to Create a Yearly Calendar in Excel (with Easy Steps)

An Excel calendar could be pretty helpful if you prefer to plan ahead and have a busy schedule. Remembering this, in this article, we’ll go through the steps ...

0

## How to Stop Excel from Changing Numbers to Scientific Notation

Large numbers are automatically placed into scientific notation by default in Microsoft Excel. In fact, Excel can handle 11 digits before it converts the ...

0

## How to Delete Hidden Link in Excel (5 Easy Ways)

While working with Excel, you may want to remove unwanted links from your spreadsheet. Now, in this article, we’ll show 5 effective ways how to delete hidden ...

0

## How to Flip Table in Excel (2 Quick Ways)

Flipping a data table in Excel seems like a trivial task, however, Excel offers no such functionality or any direct ways to invert data. Keeping this in mind, ...

0

## How to Set Intervals on Excel Charts (2 Suitable Examples)

Excel has a powerful graphing feature that can add visual depth and clarity to even the most mundane of datasets. Though Excel does most of the heavy lifting, ...

0

## How to Convert Seconds to Hours Minutes Seconds in Excel

In many situations, you may need to convert seconds to hours, minutes, and seconds. When you have a powerful tool like Microsoft Excel, you can perform such a ...

0

## How to Create a Weekly Timesheet in Excel (with Easy Steps)

Are you an HR professional, looking to make a weekly timesheet in Excel? Then, I have some good news for you! Because the following article demonstrates a ...

0

## If a Value Lies Between Two Numbers Then Return Expected Output in Excel

Excel is a popular and useful tool for analyzing large sets of data. Now, wouldn’t it be great if we could add decision-making ability to our spreadsheet? ...

0

## If Cell Contains Date Then Return Value in Excel (5 Examples)

Whilst working with Excel, you may need to check if a cell contains dates and return a value accordingly. Now, wouldn’t it be great to have Excel functions to ...

0

## How to Merge All Sheets into One in Excel (6 Quick Ways)

Microsoft Excel is a ubiquitous tool for organizing and analyzing data. Oftentimes, we may need to combine large datasets spanning multiple sheets. Keeping ...

##### Browsing All Comments By: Eshrak Kader
1. Reply 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.

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

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

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

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