Forum Discussion

Del_Dev888's avatar
Del_Dev888
Copper Contributor
Jun 28, 2024

Group by Multiple Columns in a View in an Online SharePoint List

Hi guys! I looked through the functions in a SharePoint List and I heard that we could group data by some columns. In addition, we could also group data by 2 columns in a view in SharePoint List.

 

Let's say this is an example table in an online SharePoint List:

Date of JourneyFarNearStatusLow Medium High
8 June 2024Very FarVery NearActiveMedium LowAverageHigh

 

As far as I know, a SharePoint List also allows users to group data by 2 columns in a view, which is illustrated below:

 

Date of Journey                          Far              Near            Low                 Medium         High

Date of Journey:: 6/8/2024

June 8

             Status: Active            Very Far       Very Near     Medium Low    Average         High

 

I'm wondering whether an online SharePoint List allows users to group data by multiple levels (more than 2 levels) in a view.

 

Let's say this is an example of an expanded version of the above table in an online SharePoint List:

Date of JourneyFarNearStatusRemaining HoursProgressAlertLevel of SpeedQualityLowMediumHigh
8 June 2024 Far NearActive8 HoursSlowImmediateFastBad LowAverageLow

 

I want to group data by multiple columns, let's say by 4 columns in a view, which the intended outcome in the SharePoint List would look like this:

 

Date of Journey             Far            Near   Remaining Hours      Alert    Level of Speed      Low    Medium      High

Date of Journey:: 6/8/2024   Far           Near          

June 8

       Status: Active                                    8 Hours 

       Progress: Slow                                                            Immediate     Fast

       Quality: Bad                                                                                                               Low     Average      Low

 

 

 

The below screenshot is taken from a youtube tutorial:

I would like to achieve the similar appearance from the screenshot above, but to group by 2 more columns after the "Status : Closed (1)". Therefore, 2 more columns would appear below "Status : Closed (1)", but to be applied in my context mentioned above this screenshot.

 

I have seen the documentation that if I want to group data with more than 2 columns, I would need to use the "Filter" function together with the function of grouping by 2 columns to achieve that, but I'm not sure how to go about it. This documentation is found in this link: Support - Office.com and illustrated in the screenshot below:

The statement in the above screenshot is found on the last few lines of the documentation page from the link "Support - Office.com" given above.

 

Therefore, I need help to group data by 4 columns in a view in the SharePoint List and the intended outcome would look like the one above. Any methods and techniques are appreciated, thanks in advance!

SharePoint Online
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jun 29, 2024

    Del_Dev888 

    To achieve grouping by multiple columns (more than two levels) in an Online SharePoint List, you can use a combination of custom views and metadata columns. Unfortunately, SharePoint Online's native list view settings only allow grouping by up to two columns. However, you can use a workaround to simulate additional levels of grouping using custom columns and calculated columns.

    Here's how you can approach this:

    Step-by-Step Guide

    1. Create Custom Columns

    First, ensure you have all the necessary columns created in your SharePoint list. For your example, you need columns like:

    • Date of Journey
    • Far
    • Near
    • Status
    • Remaining Hours
    • Progress
    • Alert
    • Level of Speed
    • Quality
    • Low
    • Medium
    • High

    2. Create Calculated Columns for Grouping

    Create additional calculated columns that concatenate values for multi-level grouping. This will help you achieve a nested grouping effect.

    1. Navigate to List Settings:
      • Go to your SharePoint list.
      • Click on the gear icon (Settings) and select List settings.
    2. Create Calculated Columns:
      • Scroll down to the Columns section and click on Create column.
      • Name the column (e.g., Group1) and set the type to Calculated (calculation based on other columns).
      • In the Formula box, enter the formula to concatenate the values for the first level of grouping. For example:

    =[Date of Journey] & " - " & [Far]

      • Click OK.
      • Repeat the process for additional grouping levels (e.g., Group2, Group3), adjusting the formula to include more columns. For example:

    =[Date of Journey] & " - " & [Far] & " - " & [Near]

    3. Create a Custom View with Grouping

    1. Create or Edit a View:
      • Go to the SharePoint list.
      • Click on All items or the name of the current view, and then click Create new view or Edit current view.
    2. Configure Group By Settings:
      • In the view settings, scroll down to the Group By section.
      • Select the first calculated column (e.g., Group1) for the first level of grouping.
      • Select the second calculated column (e.g., Group2) for the second level of grouping.
    3. Apply Filters:
      • To simulate additional grouping levels beyond the two provided by the Group By feature, you can use filters in combination with calculated columns.
      • Set up filters to narrow down the view based on specific criteria, creating the effect of further nested groupings.
    4. Save the View:
      • Give your view a name (e.g., "Multi-Level Grouping") and click OK to save it.

    Example

    Let's say you want to group by Date of Journey, Far, Near, Status, and Remaining Hours:

    1. Calculated Columns:
      • Group1: [Date of Journey] & " - " & [Far]
      • Group2: [Date of Journey] & " - " & [Far] & " - " & [Near]
      • Group3: [Date of Journey] & " - " & [Far] & " - " & [Near] & " - " & [Status]
      • Group4: [Date of Journey] & " - " & [Far] & " - " & [Near] & " - " & [Status] & " - " & [Remaining Hours]
    2. View Configuration:
      • First group by: Group1
      • Then group by: Group2
      • Apply filters to further break down data, simulating additional grouping levels.
      • Group By:
      • Filters:

    By using calculated columns, you concatenate the values of multiple fields to create custom groupings that go beyond the native two-level grouping limitation. This approach helps you organize data into more complex hierarchical structures, giving the appearance of multi-level grouping in SharePoint views.

    If you need to adjust the view further or automate this process, consider using Power Automate or custom SharePoint Framework (SPFx) solutions for more advanced customization.

    NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in AI. The text and the steps are the result of AI. Maybe it will help you further in your project, if not please just ignore it.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.