Forum Discussion

dmoniquesmith's avatar
dmoniquesmith
Copper Contributor
Mar 31, 2024

Spreadsheet Design

Hello. I have 12 sets of large datasets with more than 400,000 rows in each. Each dataset also has 7 columns. I want to upload the data into a visualization software to compile one graph (bar chart) from all of this data.

 

Because the files are so large I am creating another workbook that summarizes the data that I need. Each file represents a different month. Those months will eventually be the column labels, and the data from the months will be the bar information. I'm trying to figure out a good way to design the spreadsheet. This should be so simple, I think, but I'm having a hard time with it.

 

 

 

Excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 01, 2024

    dmoniquesmith 

    Designing a spreadsheet to summarize large datasets and create a bar chart from the compiled data can be straightforward with the right approach. Here's a suggested design for your spreadsheet:

    1. Data Organization:
      • Create a new workbook to summarize the data.
      • In the first sheet, label the columns as follows:
        • Column A: Row Labels (e.g., categories or groups for the bar chart)
        • Column B onwards: Columns for each month (e.g., January, February, etc.)
      • In each subsequent sheet, import or link the large datasets for each month. Ensure that each dataset is organized with the same structure (7 columns and consistent data format).
    2. Data Consolidation:
      • In the summary sheet, use formulas or Power Query (Get & Transform Data) to consolidate the data from all the monthly datasets into the appropriate columns.
      • For example, if each dataset has the same categories in Column A and data for each category in Columns B to G, you can use formulas like VLOOKUP or INDEX/MATCH to pull the data for each month into the summary sheet.
    3. Bar Chart Creation:
      • Once the data is consolidated in the summary sheet, you can create a bar chart based on this data.
      • Select the range of data (excluding the row labels) and insert a bar chart (Insert > Charts > Bar Chart).
      • Excel will automatically generate a bar chart based on the selected data range.
      • You can customize the chart further by adding axis labels, titles, and adjusting the formatting as needed.
    4. Dynamic Range (Optional):
      • To make your summary sheet more dynamic and accommodate future data additions, consider using Excel Tables or named ranges for your data.
      • This allows the chart to automatically update as new data is added to the summary sheet.
    5. Optimization for Performance:
      • Since you're working with large datasets, be mindful of Excel's performance. Avoid unnecessary calculations and complex formulas that may slow down the workbook.
      • Consider optimizing your formulas and using features like data validation and conditional formatting judiciously.

    By following these steps, you can design a spreadsheet that efficiently summarizes your large datasets and creates a bar chart from the compiled data. Feel free to adjust the design based on your specific requirements and preferences. The text and steps were created with the help of AI.

     

    If none of these steps help you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc.

    In this link you will find some more information about it:

    Welcome to your Excel discussion space!

     

    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.

    • dmoniquesmith's avatar
      dmoniquesmith
      Copper Contributor
      Apr 01, 2024
      I didn't realize I'd gotten an answer. Thank you so much. I already completed this project but this will help me so much moving forward.
    • dmoniquesmith's avatar
      dmoniquesmith
      Copper Contributor
      Apr 01, 2024

      If I may, I want to ask one more question that puzzled me. If I was using data from several spreadsheets that summarized data within the same year, and the date column was broken down by transactions for each day (each spreadsheet would have the transaction for one month only) but I wanted to create a separate spreadsheet in which I would summarize the data by year to bring into visualization software. Would you create a column named "Year" although every entry would be the same year? I ask this question because as I was doing that, it seemed ridiculous. I figured there must be a better way.

       

       

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Apr 02, 2024

        dmoniquesmith 

        If you're summarizing data from multiple spreadsheets where each spreadsheet contains data for a different month within the same year, and you want to create a separate spreadsheet summarizing the data by year for visualization purposes, there are a few approaches you could consider:

        1. Use a "Year" Column: Although it may seem redundant to have a "Year" column where every entry is the same year, it can still be a useful way to organize your data, especially if you're planning to use this summarized data in visualization software. Having a "Year" column allows you to maintain consistency in your dataset structure and makes it clear to others who may view or analyze the data later on.
        2. Use a Pivot Table: Another option is to use a pivot table to summarize the data by year. You can create a pivot table from the individual monthly datasets and group the data by year. This way, you don't necessarily need a separate "Year" column in your original datasets. The pivot table can dynamically summarize the data based on the date information present in each dataset.
        3. Combine Data Before Summarizing: Instead of creating separate spreadsheets for each month and then summarizing by year, you could combine all the monthly data into one dataset first. Then, you can use formulas, pivot tables, or other methods to summarize the combined dataset by year. This approach may simplify the process and eliminate the need for a "Year" column in the individual spreadsheets.

        Ultimately, the best approach depends on the specific requirements of your analysis and how you plan to use the summarized data. Consider factors such as data organization, ease of analysis, and compatibility with your visualization software when deciding on the most appropriate method for summarizing your data by year.

Share

Resources