Zahid Shuvo

Hi there, Nathalie!

Thanks for sharing your confusion here.
Since your problem seems unique. We have three possible cases of solutions that you can use.

Solution 01: You can use the Custom Sort feature to order the graph according to your choice. And it should update it by itself.

Solution 02: Add a new sunburst graph according to the sorted data if it does not work.

Solution 03: you can use sort data based on multiple columns, and you can specify secondary and tertiary sorting criteria. Here’s how:

1. Select the range of data you want to sort.
2. Go to the “Data” tab.
3. Click on the “Sort” button.
4. In the Sort dialog box, you can add multiple levels of sorting by clicking on the “Add Level” button.
5. For each level, choose the column to sort by, the sort order (ascending or descending), and then click “OK.”

Excel will first sort the data based on the primary column you specified. If there are any ties in the primary column, it will then sort those tied rows based on the criteria you specified for the secondary column, and so on for additional levels.
Then, add a new sunburst graph based on the newly sorted data.

Hopefully, one of the solutions from here should resolve your issue.

Regards
ExcelDemy Team

Hey there!

We understand your problem that you shared here. Based on the scenario you have provided, there can be two possible cases of your problem and solution.
Case -1
If you want to convert the minute span into the tenths fraction of time as you mentioned. For example,
0-3 = .0
4-9 = .1
10-15 = .2
16-21 = .3
…….
58-60 = 1.0

You can simply use the formula to convert it into tenth fraction. (Yes, you are right, we will use multiple IF function to achieve it)

=IF(AND(A1>=0, A1<=3), 0, IF(AND(A1>=4, A1<=9), 0.1, IF(AND(A1>=10, A1<=15), 0.2, IF(AND(A1>=16, A1<=21), 0.3, IF(AND(A1>=22, A1<=27), 0.4, IF(AND(A1>=28, A1<=33), 0.5, IF(AND(A1>=34, A1<=39), 0.6, IF(AND(A1>=40, A1<=45), 0.7, IF(AND(A1>=46, A1<=51), 0.8, IF(AND(A1>=52, A1<=57), 0.9, IF(AND(A1>=58, A1<=60), 1, "Out of Range"))))))))))))

Replace A1 with the cell reference where your time value is located.
This formula uses nested IF statements to check the given conditions and return the corresponding result based on the specified ranges. If the value is outside the specified ranges, it will return “Out of Range.” Adjust the cell reference and the ranges as needed for your specific use case.

Case -2
In this case, if you want to convert the time 7:25:00 (hh:mm:ss) into 7.4. You can use the formula to convert it. Say, the value 7:25:00 is stored in cell A1.

=IF(AND(MINUTE(A1)>=0,MINUTE(A1)<=3),HOUR(A1)+0,IF(AND(MINUTE(A1)>=4,MINUTE(A1)<=9),HOUR(A1)+0.1,IF(AND(MINUTE(A1)>=10,MINUTE(A1)<=15),HOUR(A1)+0.2,IF(AND(MINUTE(A1)>=16,MINUTE(A1)<=21),HOUR(A1)+0.3,IF(AND(MINUTE(A1)>=22,MINUTE(A1)<=27),HOUR(A1)+0.4,IF(AND(MINUTE(A1)>=28,MINUTE(A1)<=33),HOUR(A1)+0.5,IF(AND(MINUTE(A1)>=34,MINUTE(A1)<=39),HOUR(A1)+0.6,IF(AND(MINUTE(A1)>=40,MINUTE(A1)<=45),HOUR(A1)+0.7,IF(AND(MINUTE(A1)>=46,MINUTE(A1)<=51),HOUR(A1)+0.8,IF(AND(MINUTE(A1)>=52,MINUTE(A1)<=57),HOUR(A1)+0.9,IF(AND(MINUTE(A1)>=58,MINUTE(A1)<=60),HOUR(A1)+1,"Out of Range")))))))))))

It will show 7.4 instead of 7:25:00.

You can use this formula for your dataset. To do so, replace A1 with the cell reference where your time value is located.
Thanks for choosing us. And we will be happy to solve your problem and answer your query regarding Excel.
Regards
ExcelDemy Team

Hey There, Mr. Mark.
Thanks for dropping your query here. You can follow the steps to copy the pivot table to the new book.

• Select the entire pivot table.
• Press “Ctrl+C” to copy the pivot table to the clipboard.
• Go to the sheet in the new Workbook.
• Press “Ctrl+V” to paste the pivot table.
• Now, adjust the column width. And you will have the pivot table in the new book.

Regards
ExcelDemy Team

Hi there, Mr. Smith
Thanks for sharing your problem here. We have a solution for you on this issue. You can use the code below. This will basically highlight the cell (where the Manager has made any change) with yellow background and red font, and also a comment showing the date and time when the change is done.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WRng As Range
Dim rg As Range
Dim commentText As String
commentText = “Time of change: ” & Format(Now, “hh:mm:ss AM/PM”)
Set WRng = Intersect(Application.ActiveSheet.Range(“a1:ff983”), Target)
If Not WRng Is Nothing Then
Application.EnableEvents = False
For Each rg In WRng
If Not VBA.IsEmpty(rg.Value) Then
rg.AddComment commentText
rg.Interior.Color = RGB(255, 255, 0) ‘ Yellow background
rg.Font.Color = RGB(255, 0, 0) ‘ Red font color
End If
Next
Application.EnableEvents = True
End If
End Sub

You can follow the steps to run the code:
● Press ALT + F11 to access the VBA editor.
● In the Project Explorer, find and select the desired worksheet.
● Copy and paste the code above.
● Save the File.
● Go back to the Excel worksheet.
● Perform actions that trigger the event (e.g., change cell values).

If you still have any problem with this code. Please can you be more specific about your needs in this case thus we can provide you with a much more relevant solution?

Thanks again for your patience.
Regards
ExcelDemy Team

Hey there, Carissa!
Thanks for sharing your problem here. To solve the issue, you just need a small modification in the VBA code.
Follow the steps in the 3rd method mentioned in the article and make a change into the VBA code as follows.

And then run the code. It would work the way you want.
Regards
ExcelDemy Team