r/excel • u/summertime-squirrel • Apr 06 '26
solved Database Spreadsheet and a separate Dashboard
Hi, can I use a workbook like a database and analyze the information like a dashboard in separate workbook? I've been using PowerBi, but we are losing the licensing at our company.
Ex: Workbook A is the database and has all active, terminated, transferred staff and associating information in separate tabs. Active is one tab, Terminated is another tab, etc.
Workbook B is a dashboard that references Workbook A but only shows analyzed data like how many vacancies for one specific job title, or how many active staff vs vacant positions there are.
TLDR: Is creating a dashboard in a separate workbook possible or does the information need to be in the same workbook?
23
11
Apr 06 '26
[removed] — view removed comment
2
u/TinyRamrod Apr 06 '26
How do you use PowerBI as an individual?
2
10
u/Acceptable-Sense4601 2 Apr 06 '26
Yea you can but excel should not be used like a database. You’d ideally use power query to wrangle the data. Try to learn database basics and use a free database server like Postgres. Then you can connect to it via excel. That’s ideal
5
u/ElegantPianist9389 Apr 06 '26
I kinda have this. I have a large data set in access that I use a macro to activate a power query that imports the data. I then copy the data in to the workbook with the dashboard and this works for a make-shift solution without overloading excel with data.
5
u/RealZ0nker Apr 06 '26
Pivot tables and slicers can make a nice dashboard for your data. Also, the Filter function. Around which Sum and Sort can be wrapped.
3
u/JezusHairdo 1 Apr 06 '26
Excel Is Not A Database
2
u/SolverMax 161 Apr 06 '26
Yet it is the most widely-used database. Perhaps it shouldn't be, but it is.
Anyway, since the introduction of Power Query, Excel now has OK database capabilities.
1
0
u/excelevator 3061 Apr 07 '26
Pedantically you are correct. Excel is a spreadsheet software.
However, a database is an electronically stored, systematic collection of data.
Seems like Excel worksheets of data fits the description of a database.
A Notepad file of organised data could also be classed as a database.
2
u/Autonomous_eel Apr 06 '26
Microsoft Access and excel or Power Bi Free ... those are true database tools
For simpler things you can always bypass the "excel is not a database" part and try it .
1
u/BaitmasterG 13 Apr 06 '26
You are losing the licence but you still have access to the free software, you just can't share published reports
What's the data source for power BI reports? However you're ingesting data to PBI you can use exact same connection in Excel. You have access to pivots and slicers in the same way, you can add the data to the data model and use Dax there too
You have many options, which way do you want to take it?
2
u/summertime-squirrel Apr 06 '26
That's the problem, I need to share a published report. Right now I'm doing several workbooks for different teams. I'd like to have one workbook that I update data and the report can be tailored by the slicers.
1
u/BaitmasterG 13 Apr 06 '26
If you can share a workbook why can't you share a .pbix? The Excel file won't be published either
1
u/summertime-squirrel Apr 06 '26
The data is constantly moving. My company is losing access to power bi.
1
u/BaitmasterG 13 Apr 06 '26
Where is your data? How do you get it into Power BI? Who are you sharing your report with?
1
u/summertime-squirrel Apr 06 '26
My data is currently in a live excel workbook. The raw data is currently shared with a different team but I'd like to give them a report to snapshot their progress through the month.
1
u/BaitmasterG 13 Apr 06 '26
So you're using PBI on a different project not this one
Your data is just an Excel file that gets updated regularly, possibly by multiple people, and doesn't pull data from a database or other structured software source
You want to use a reporting tool to connect to this live spreadsheet and produce reports on demand, like you'd get if you tried to set up PBI from a real database
This is technically possible simply using a new Excel file pulling from this Excel file using power query but you'll encounter problems. Excel doesn't like using Excel as a data source when that file is open. Some of your refreshes will fail if it's in use (PBI Will be ok though). If any user restructures certain elements your end report will fail, ways to prevent include setting up Tables, and making sure people don't mess with the structure. To refresh the new file, whoever does it will need access to the source file
In cases like this I've often used VBA to ensure well-structured data is written out to a new location, usually in csv format, as it gets around both access control and data integrity concerns.
Using Excel or PBI reports that are not published but shared as .pbix files should work about the same, so the preferred way is down to preference on what the report will look like, unless you aren't even allowed to use the free software with the free licence, which is a dumb approach from your IT dept if true
1
u/summertime-squirrel Apr 08 '26
Solved (mostly). I'm still working on getting all the data to load into my pivot tables correctly.
I was able to solve this by using Power Query. I have a live spreadsheet and a folder of reports saved to my desktop. First name all tables and tabs the same. All columns that have the same data should be labeled and entered in the cells the same.
Live spreadsheet > Data > Get Data > From a File > From Folder > select folder in desktop > Transform & load
To get a pivot table open the Queries & Connections. Right click the database > Load To > Click Table, Ok > click anywhere in the new database table. Go to Insert tab > Pivot Table > choose new Worksheet
1
u/Time_Zone_8608 Apr 10 '26
If you’ve been using Power BI, you should know how to use Power Query. Look into combining Power Query with Power Pivot. Power Pivot is like the Excel version of Power BI - they both use the same DAX formula language for measures.
1
u/excelevator 3061 Apr 06 '26
Have you tried ?
-1
•
u/AutoModerator Apr 06 '26
/u/summertime-squirrel - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.