Arin Islam

Hi EDD,
Thanks for your comment. Hiding the Formula bar and Ribbon are typically done on a per-workbook basis and aren’t usually carried over to other workbooks you open. However, when you hide Formula Bar and Ribbon from any specific worksheet of your workbook, they’ll be removed from all the other worksheets of that workbook.
You can follow a manual process to align the range in the center of the screen. To center align all the elements on the screen, select the whole range and press Ctrl + X to cut the total range. Here, for the Home page, we will cut cell range B1:H12.

Then, press Ctrl + V to paste it in the center of your screen.

Similarly, you have to cut and paste all the elements to the center and align them on the screen manually.

We hope this will solve your problem. Please let us know if you face any further problems.
Regards,
Arin Islam,
ExcelDemy

Dear Eugenia,
Thank you for your comment.
To ensure that the progress bar accurately reflects the progress of your macro, you can use the following modified code.
• Insert this code into your module.

Private Sub cmd_Click()
	Progress_Bar
End Sub
Sub Progress_Bar()
	Dim Counter1, Row_no, Col_no As Integer
	Counter1 = 1
	Row_no = 1
	Col_no = 1
	For Counter1 = 1 To 1000
    	DoEvents
    	Cells(Row_no, Col_no) = 5
    	Application.StatusBar = "Loading data... " & Round((Counter1 / 1000) * 100, "0") & "%"
    	Row_no = Row_no + 1
    	If (Row_no = 5) Then
        	Col_no = Col_no + 1
        	Row_no = 1
    	End If
	Next Counter1
	Application.StatusBar = "Done " & Round((Counter1 / 1000) * 100, "0") & "%"
End Sub

Here, the Progress_Bar subroutine is called by the cmd_Click event. We used the (Counter1 / 1000) * 100 formula to calculate the progress of the macro execution as a percentage and set it as the value of StatusBas.

• Then, create a button in your worksheet >> right-click on it >> select Assign Macro.

• Assign the Progress_Bar named macro to that button.

Now, if you click on the button, It will show progress in the status bar.
We hope this will solve your problem. Please let us know if you face any further problems.
Regards,
Arin Islam,
ExcelDemy

Hi FADYA,
Thanks for your appreciation.
When we auto-populate Word documents from Excel it automatically generates data in individual documents for each data.
However, you can go to the View tab and then click on Web Layout from the Views group to see all the values in a single layout.

Hi KARL,
Thanks for your comment.
To always return 8 digits with a fill of 0 on the left side of the data you can use VBA code. Follow the steps given below to do that.
Steps:
• Firstly, go to the Developer tab >> click on Visual Basic.

• Then, it will open Microsoft Visual Basic for Applications.
• Now, open Insert >> select Module.

• Next, a Module will open then type the following code in the opened Module.

Sub Alphanumeric()
For Each cell In Selection
    If Len(cell.Value) < 8 And Len(cell.Value) <> "" Then
        cell.Value = WorksheetFunction.Rept("0", 8 - Len(cell.Value)) & cell.Value
    End If
Next
End Sub

• Finally, Save the code and go back to the worksheet.

• Then, select the cell or cell range to apply the VBA.
• Here, we selected the range B5:B7.
• Next, open the Developer tab >> select Macros.

• After that, select Alphanumeric and click on Run.

• Thus, Excel will always return 8 Alphanumerics with a fill of 0 on the left side of the data string.

We hope this will solve your problem. Please let us know if you face any further problems.

Regards,
Arin Islam,
ExcelDemy

Hello MRRRR,
Thank you for your comment. If you follow the first method, we have shown you will get the week number from a date according to the calendar.
• Here, to find the week number of 4th September 2021 we used the following formula and got 1 as the week number.

=WEEKNUM(B3,1)-WEEKNUM(DATE(YEAR(B3),MONTH(B3),1),1)+1

• On the other hand, using the same formula we got 2 as the week number for 5th September 2021.

Hope you have found your solution. If you face any further problems, please share your Excel file with us at [email protected].
Regards
Arin Islam,
Exceldemy.

Hello RALF,
Thanks for your comment. I suppose you are mentioning the first method. Here, we have shown you how to sort a single column. We have sorted only the column which contains the Age and the other column values (Name, Date of Birth) remained the same. That’s why after sorting the age changed for Amy Bryne.
If you have any other suggestions or face any problems, please share them with us in the comment section.
Regards,
Arin Islam
Exceldemy.

Hello, MELVIN MORALES!
Thanks for your comment. You can click here to autoscroll and you’ll find the download links for both pdf and xlsx formats.
https://www.exceldemy.com/microsoft-excel-formulas-functions-cheat-sheet/#Download_Excel_Formulas_Cheat_Sheet_PDF
Regards
Arin Islam,
Exceldemy.

Hello HBING,
To solve your issue follow the steps given below.
• Firstly, go to the Page Layout tab >> click on Print Titles.

• After that, type 5:5 as Print Area and $2:$2 as Rows to repeat at top.
• Then, click on OK.

• Now, write the following code in your module.
Sub Print_Each_Data()
Dim iRng As Range
Dim iWorkRng As Range
Dim ixWs As Worksheet
On Error Resume Next
xTitleId = "Microsoft Excel"
Set iWorkRng = Application.Selection
Set iWorkRng = Application.InputBox("Range", xTitleId, iWorkRng.Address, Type:=8)
Set ixWs = iWorkRng.Parent
For Each iRng In iWorkRng
ixWs.PageSetup.PrintArea = iRng.EntireRow.Address
ixWs.PrintPreview
Next
End Sub

• Next, click on Macros from the Developer tab.
• Select the macro named Print_Each_Data.
• Lastly, click on Run.

• Now, a box will open.
• Then, select the range which you want to print. Here, we selected cell range B2:E3.
• Finally, click on OK.

• Thus, you can print each student’s data automatically in the same format.

If you face any further problems, please share your Excel file with us in the comment section.
Regards
Arin Islam,
Exceldemy.

Hello Lodewijk,
I suppose you want to know more about the Power Query Editor and how it can add sheets to get unique values.
Power Query Editor is very useful in the case of data preparation.
To use this editor, you have to use the Get Data feature from the Data tab. You can get data from different kinds of files such as Excel workbooks, PDFs, Text, etc. Then, you can transform those datasets using the operator available. Here, we removed rows from the tables. You can also combine different tables from those files by the Append or Merge operator. The Append operator is used to create a new query having all the rows from the datasets and the Merge operator is used to have a query with all the columns. You have to use the Append operator to get the unique values. Finally, you can load the query in the existing worksheet or a new worksheet.
If you face any further problems, please share your Excel file with us in the comment section.
Regards
Arin Islam,
Exceldemy.

Hi Mohammed Shahid,
You can pull text from multiple sheets and ignore blank or space by using the TRIM function. This function will help you to remove space automatically.
To solve your problem, use the following formula in Cell B2 of Sheet 3 in your Excel worksheet.
=TRIM(Sheet1!B2&" "&Sheet2!B2)

Hope this will solve your problem. Feel free to comment if you have further inquiries.
Regards,
Arin Islam.

Hi Sue,
Hope you are doing well.
You can approximately match addresses on 2 sheets and find a job associated with that address.
Here, we created a dataset in Sheet1 according to your description.

Again, this is Sheet2 containing the exact Address and Job record.

Now, to approximately match addresses on 2 sheets and find a job associated with that address use the formula given below in Sheet1 Cell C5.
=VLOOKUP(B5,Sheet2!$B$5:$C$11,1,TRUE)

Thanks.

Regards,
Arin Islam

Hi J,
Hope you are doing well.
You can get the actual numerical difference between two cells by using subtraction (-) in the formula. In case of, percentage value use the formula given below.
=IF(AND(0<D5,D5<$D$10),(D5/$D$10)*100&"% Less Than 10",(-D5/$D$10)*100&"% Greater Than 10")

If the condition is True then the formula will return the result as follows.

On the other hand, if the condition is False then the formula will return the result as follows.

Here, the AND function will return True if both the conditions are true else it will return False.
Thanks.

Regards,
Arin Islam