•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Trying to figure out what is best to showcase the workbooks overview
Our take
Hi everyone,
I’m looking for guidance on creating an effective overview sheet for an Excel workbook I’ve inherited. It tracks various items and quantities across multiple project sheets, but I want to consolidate this information into a clear overview. My previous experience involved using Pivot tables and charts, but I’m struggling to replicate that success now. I’m fairly adept with Excel, so I believe I just need a fresh perspective. Any insights or suggestions would be greatly appreciated to help me move forward! Thank you!
Hi all,
I've inherited an Excel workbook tracking a number of items and quantities. There is a general overview sheet, and then numerous other sheets breaking down what items are needed for different projects. It has some duplication across the sheets for this reason!
However, I wanted to give it an overview sheet, where it can be easily seen what is allocated where and if the quantity needed is available per project.
I've done this before in a previous job role, but as they say "use it or lose it!" and I have lost it. I cannot remember how I set it up - it was pre-covid. I think Pivot tables/charts were involved, but it's not offering me all I want when I try it now. Maybe I'm having a mental block, or maybe there is a much better way of doing this now! I'm pretty savvy with Excel, so fire away - think I just need the mental push.
Any help appreciated!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Looking for project tracking ideasHi, As the title suggests I’m trying to create an Excel sheet that tracks the progress of various projects. Essentially, I was given a messy excel document to look after. I don’t have particularly advanced skills and nor do I want to spend too much time on this. So… One sheet lists projects as rows and they all have a reference code. There is a column designated for “action updates” where people overwrite progress each month. Other columns exist for project status, dates etc. My idea was to create another sheet which also lists the projects but acts as an action/change history log. I attempted to have a drop down through grouped cells that would act as a historic list of all changes and action updates around each project. The row with the corresponding project number would act as a “live view”, so using an X look up to display this data on the original sheet. Is there a better way to create something like this? There must be loads of ways to do something like this, but I just can’t think which way to do it!! Thank you for any help!!! submitted by /u/Formal_Disk_3760 [link] [comments]
- Resources that help you get better at laying out Excel spreadsheets?When I have the opportunity to see someone else's sheets, sometimes I feel like "oh that's a better way to lay out the inputs v data/calcs. I'm comfortable with the data and formulas used, but I always feel like I could use improvement in laying out my information for others to understand. My brain thinks about in one sequence but that may not be the same for others. Are there any resources you've used that helped you get better at synthesizing the building of your workbooks? submitted by /u/brooklyn735 [link] [comments]
- Need Excel Help – Investor Distribution Comparison (High Visibility Project)Hi everyone, I’m an accountant in the real estate industry, and I’ve been given a high-visibility project that I really want to knock out of the park. For the past ~2 years, I’ve been manually calculating and distributing investor returns using a QFR-based Excel process. This feeds into our accounting system (Sage Intacct) and ultimately into our ACH distributions. Recently, our company developed new portal functionality that allows investor distributions to be processed automatically with the click of a button. Before this goes live, I’ve been asked to validate it by comparing historical distributions (January & February) against what the portal generates. What I Have So Far My initial thought was to create a basic comparison like: Historical Amount Portal Amount Variance (=B2 - C2) But that feels way too basic for something this important. I want to elevate this into something management can actually gain insight from—not just a simple variance check. What I’m Trying to Build I’d love help or ideas on how to make this spreadsheet more robust and “presentation-ready.” Specifically: 1. Comparison Tab Clean layout comparing historical vs. portal data Meaningful variance analysis (not just raw differences) Flags or indicators for material discrepancies Anything that helps quickly identify issues at a glance 2. Summary / Dashboard Tab High-level view for management Total distributions (historical vs portal) Total variance and % variance Count of mismatches or exceptions Any visual elements (charts, conditional formatting, etc.) that improve clarity 3. Edge Cases / Notes Tab I also need a third tab that outlines nuances the developers need to consider before production, such as: JE import creation requirements Rounding issues Wire vs. ACH needs for custodians Investor-specific scenarios I understand the logic behind these items, but I’m struggling with how to present them in a clean, structured way. My Skill Level I’d say I’m beyond a beginner in Excel, but definitely not advanced—I know the basics well but haven’t fully leveraged things like dashboards, advanced formulas, or more polished presentation techniques. What I’m Looking For Specific formulas or features I should incorporate Layout or structure suggestions Ideas to make this more insightful for management Anything that would make this feel like a “senior-level” deliverable This is a big opportunity for me to stand out, so I really appreciate any advice you can share. Thanks in advance, I'm excited to dive into the community! 🙏 submitted by /u/Affectionate_Net3153 [link] [comments]
- Is there any way to make a relational DB like thing in excel?For context, my work involves maintaining a lot of excel trackers. Basically we maintain these to track project details for a client like project deliverables, project codes (for employee clock-ins), project milestones, project assigned to PMs or not, etc - all in different excel files. This might sound like simple info, but we capture a lot of details related to project in all those files - like the main tracker will have bascially columns for capturing info from every section of the contract signed with client. The clock-in codes tracker will have its name, parent account ID, clock-in category, project ID, and a few other columns. Just adding one project's details to all trackers takes about 30-50 mins right now (depending on complexity and category of the project). However, maintaining multiple files leads to a lot of duplication effort - basically you add name of the project, project ID, PM name etc so many times. Anyway this can be changed? Like we add all data in one sheet and maybe pull it into different views for different purposes? I have done some research with gpt and on youtube, but they suggest going the power apps/ power BI way, but I am not too well-versed with those. And I was thinking if there is another solution that can be done in excel itself? Or if power BI is the way, then maybe can you guide me to a starting point for that? Thanks in advance. submitted by /u/WorldlyDot_1 [link] [comments]
Tagged with
#Excel alternatives for data analysis#Excel compatibility#Excel alternatives#natural language processing for spreadsheets#generative AI for data analysis#google sheets#rows.com#financial modeling with spreadsheets#interactive charts#Excel#workbook#overview sheet#Pivot tables#items#quantities#projects#charts#tracking#general overview#duplication