Using Excel as a database

%3CLINGO-SUB%20id%3D%22lingo-sub-2736912%22%20slang%3D%22en-US%22%3EUsing%20Excel%20as%20a%20databse%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2736912%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%20an%20Access%20database%20that%20holds%20just%20over%2017%2C700%20records.%26nbsp%3B%20Since%20I%20have%20come%20up%20against%20shortcomings%20of%20Access%20(pivot%20tables%2C%20maximum%20calculated%20fields%2C%20etc)%2C%20I%20have%20been%20using%20Excel%20to%20view%20the%20data%20using%20Power%20Query.%26nbsp%3B%20This%20has%20worked%20really%20well%2C%20except%20for%20one%20problem.%26nbsp%3B%20Every%20time%20I%20need%20to%20make%20a%20change%20to%20a%20record%2C%20I%20have%20to%20go%20to%20Access%2C%20find%20it%2C%20change%20it%2C%20go%20back%20to%20Excel%2C%20and%20refresh.%26nbsp%3B%20If%20I%20find%20data%20that%20is%20missing%2C%20I%20have%20to%20take%20that%20and%20enter%20it%20into%20Access%2C%20then%20go%20back%20to%20Excel%20and%20hit%20refresh.%26nbsp%3B%20Same%20for%20%22retired%22%20products%20that%20need%20to%20be%20marked%20as%20such.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20this%20all%20in%20Excel%20(store%2C%20edit%2C%20and%20add%20data)%3F%26nbsp%3B%20I%20have%20looked%20at%20trying%20to%20create%20custom%20views%20so%20that%20I%20could%20have%20a%20worksheet%20that%20just%20shows%20a%20certain%20product%20with%20its%20specific%20columns%2C%20but%20I%20have%20found%20nothing.%26nbsp%3B%20I%20suppose%20I%20could%20create%20macros%20on%20the%20backend%20that%20would%20hide%20and%20show%20columns%20and%20apply%20filters%2C%20but%20I%20do%20not%20want%20to%20make%20it%20too%20complicated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20using%20Excel%20M365%20on%20Windows%2010.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2736912%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2737025%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Excel%20as%20a%20databse%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1151232%22%20target%3D%22_blank%22%3E%40marcartmann%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%20that's%20the%20way%20it%20works%3A%20Backend%20for%20data%20management%20und%20frontend%20for%20reporting.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Good morning.

 

I currently have an Access database that holds just over 17,700 records.  Since I have come up against shortcomings of Access (pivot tables, maximum calculated fields, etc), I have been using Excel to view the data using Power Query.  This has worked really well, except for one problem.  Every time I need to make a change to a record, I have to go to Access, find it, change it, go back to Excel, and refresh.  If I find data that is missing, I have to take that and enter it into Access, then go back to Excel and hit refresh.  Same for "retired" products that need to be marked as such.

 

Is there a way to this all in Excel (store, edit, and add data)?  I have looked at trying to create custom views so that I could have a worksheet that just shows a certain product with its specific columns, but I have found nothing.  I suppose I could create macros on the backend that would hide and show columns and apply filters, but I do not want to make it too complicated.

 

I have using Excel M365 on Windows 10.

 

Thank you!!

1 Reply

@marcartmann 

Well that's the way it works: Backend for data management und frontend for reporting.