I've created a project for myself where I'm importing my list of movies from a txt file and I've already alphabetized the movies and grouped the movies by franchise. Now I'm creating additional columns for the year, main actor(s), director, platform where purchased, duration and other relevant information (such as if it's a short video, feature length film and studio). And once I understand macros, I might create a VBA to link my spreadsheet from Wikipedia so it can pull in information to make my life easier.
Thanks for info, however cannot seem to get the results that I want. I need to create a database of products and prices, and from that database extract to a quote template?
hello I had some ideas to do automation with tools of our codes in a WordPress database, could you help me understand if the ideas I had are good and feasible
I followed each step you did but it gets error under notification (This formula or function used is dividing by zero or empty cell) can you find my error please?
Before I even start to figure this out I want to know one thing. I want to create pages of information and enter fields into that information so I can just copy and paste the whole document without finding all of the details. The document is about 5000 words and will be almost the same except for the field values (ie title of book, description, etc.) Can it do this without limitation before I waste time? I used to use filmmaker pro before claris wanted a million dollars for it every month. Thanks
A little misleading because you can't really use excel as a relational database. You are only using excel in this video as a single flat table with only 1 clear purpose. But try this technique to track a hospital patient health record. Tracking not only admission, doctor and diagnosis, but track all the case notes, all the separate treatments, all the treatment barriers. Then you really can't use excel. It would be foolish and easy to accidently create redundancies.
Fun fact as a data scientist the first thing they teach you is that there is a huge missconception where people think nd try to use excel as a data base while its not...
How do we add two columns to the spread sheet: Ennea (this will be a number in brackets next to their name) Source (this will either be in brackets or at the bottom in notes) Xx
Can you help me solve this question? Using appropriate spreadsheet and database application 1. Create a database on a spreadsheet table such that update of the records in the spreadsheet table should not affect records in the database Secondly, The records of the spreadsheet table should consist of five columns of which the first three accepts text and the last two takes numeric data
To achieve this, you can use Microsoft Excel as your spreadsheet application and Microsoft Access as your database application. **Here's a step-by-step guide:** **1. Create the Excel Spreadsheet Table:** * Open a new Excel workbook. * Create a new sheet and name it "Spreadsheet Table". * In the first row, enter the column headers for your table: * Column 1: **Text Column 1** * Column 2: **Text Column 2** * Column 3: **Text Column 3** * Column 4: **Numeric Column 1** * Column 5: **Numeric Column 2** * Enter data into the remaining rows, ensuring that the data types match the specified column headers (text for the first three columns and numbers for the last two). **2. Create the Access Database:** * Open Microsoft Access and create a new blank database. * Create a new table and name it "Database Table". * Add five fields to the table, corresponding to the columns in your Excel spreadsheet: * **Field 1:** Text (specify the appropriate text length) * **Field 2:** Text (specify the appropriate text length) * **Field 3:** Text (specify the appropriate text length) * **Field 4:** Number (specify the appropriate data type and size) * **Field 5:** Number (specify the appropriate data type and size) **3. Establish a Link Between the Excel Table and the Access Database:** * In Excel, go to the **Data** tab and click **From Other Sources**. * Select **From Access**. * Browse to the location of your Access database file. * Choose the "Database Table" and click **OK**. * In the Import dialog, select **Create a new table**. * Click **OK**. * The Excel table will now be linked to the Access database. **4. Update Records in the Excel Table:** * Make any necessary changes to the data in the Excel table. * When you save the Excel workbook, the changes will be reflected in the linked Access database table. **Key Points:** * The link between the Excel table and the Access database ensures that changes made in Excel are automatically updated in the database. * This approach prevents accidental overwriting of data in the database, as the Excel table acts as a separate data source. * You can use Access to perform more complex database operations, such as querying, filtering, and reporting, on the linked data. By following these steps, you can effectively create a spreadsheet table and a linked database that allows you to update records in the spreadsheet without affecting the database directly.
I am trying to create a page on my spreadsheet that collects all inputted info for a year. The spreadsheet will have info for each month but I won't the extra sheet automatically collect all info for a year. Any help is appreciated.
In simple terms, yes. AVERAGE can be used in both examples but DAVERAGE only when Excel has been structured correctly. DAVERAGE will help you troubleshoot the formula at a later stage if something goes wrong whereas AVERAGE could take longer to workout where the issue could be.
Great - am looking at using this as a records management tool - not financial data manipulation....any tips or signposts to other presentations? Do you post up excel templates with the formulas on anywhere to practice on? Thanks again :)
Not sure what you actually mean, however "=TODAY()-B1" would be today's date less the number of days listed in cell B1 or "=TODAY()-180" if B1 = 180. Hopefully this helps.
@@ThatOfficeGuy Thanks for responding. I gave that a try and it didn't work. Sorry I wasn't clear enough. In your video, you were getting the DAVERAGE and DSUM for the Region and Month. Let's assume, you have a date, which is MM/DD/YY instead of a month (Jan) and you want to DSUM all sales for the last 30 days. So the criteria would be something like >=today()-30. But I'm not able to get this to work. Assuming, you found a way to get this to work, can you see if you can get this to work when the number 30 is stored in another cell and the criteria will reference that cell. Thanks for your help.
When you do any adding or subtracting, make sure that both of the data sets are in the same format. You can't subtract 06/08/2021 from 365 the format doesn't align. If you instead convert it to a number or convert both sources of data to a date then it might work
Have you been starting to store data's and files to the modern science focused non physical data and files storages as back up data and files storages because there's also modern tech newly developed intrusion methods that can penetrate the usual encryptions and usual system defenses which necessitates modern science advancements varieties of data and files storages so that those years and years of works and encodings and funds used for storing those data's and files won't go wasted and wont be stolen by other hi tech companies specially the organization involved belong to a large alliance with plenty of resources because there's stiff rivalries of opposing alliances nowadays trying to dismantle the other?
oh god it's not a database it's a table all the data is jumbled up sorry as a programmer this triggers me. It looks like a failed A-Levels database normalisation answer
Create relationships with what you want to search for, Insert a pivot table using the database, then move the row headers around until you get what you’re looking for.
Hi, I need to create a form in excel. everytime I press on submit form, a new data entry should be created in other sheet. And if I want to modify any old entry, I should be able to recall that entry in that form for edit. Kindly share me any video link or suggestion. Thanks in advance.
Sorry, excel is amazing at crunching numbers, but it is not a database despite having DB formulas and even allowing expert users to build jaw-dropping databases in it. Microsoft has delivered several function to Excel to act like a DB and kill Access, but they could not kill it yet. Using excel to maintain a database is similar to traveling from Argentina to Japan in a canoe, whereas Access would be a small plane and SQL DBMS would be a Boeing airliner. Just make your choice right.
It does come down to choosing the right tool for the job, it all depends on what your goals are. I have used Access for years but the 2GB limit is a problem for most, I now use Power BI and it does a good job for what I need.
@@ThatOfficeGuy the 2GB is the same for both Excel and Access but in fact you can have 2GB per field if you split the database, I've been using Office since 1983, but as you say use the right tool for the job. Excel for spreadsheet's & Access for database's
@@andybawn1 Access is pretty much dead while OFFICE 365 will to defacto standard now. Access is much better but for the average user it would take some time to build. It is complicated for people who needs to just data entry than excel.
![Microsoft Excel Basic Formulas and Functions [Excel Basics for Beginners]](http://i.ytimg.com/vi/HUcRRnmidU4/mqdefault.jpg)
![Microsoft Excel Basic Formulas and Functions [Excel Basics for Beginners]](/img/tr.png)
If you like the video please consider subscribing to the channel and hitting the like button, it is much appreciated!
I've created a project for myself where I'm importing my list of movies from a txt file and I've already alphabetized the movies and grouped the movies by franchise. Now I'm creating additional columns for the year, main actor(s), director, platform where purchased, duration and other relevant information (such as if it's a short video, feature length film and studio). And once I understand macros, I might create a VBA to link my spreadsheet from Wikipedia so it can pull in information to make my life easier.
Brother can you post a video?
Very helpful. Thank you.
learnt something new, never heard of it. It's very helpful. Thanks for sharing this. Great, Keep-it-up.
thank you , this formula are amazing
🎯 Key Takeaways for quick navigation:
00:00 📊 Setting up a Database in Excel
05:06 📈 Using Database Functions in Excel
Made with HARPA AI
Thank you. I appreciate your video
Could also use pivot table in Excel for this.
Thanks a lot!!
Nice video.
Great video
Thanks for the precious video!
Thanks for info, however cannot seem to get the results that I want. I need to create a database of products and prices, and from that database extract to a quote template?
thank you, very clear and useful
Great video, I need to create a workflow using excel, what are the best options for that?
hello I had some ideas to do automation with tools of our codes in a WordPress database, could you help me understand if the ideas I had are good and feasible
Your video is very helpful🤗💖 How about Sales Prospecting Data Base?☺️
so touching for an excellent video
It was a helpful video!
Great channel name
I followed each step you did but it gets error under notification (This formula or function used is dividing by zero or empty cell) can you find my error please?
best tutrolias
Before I even start to figure this out I want to know one thing. I want to create pages of information and enter fields into that information so I can just copy and paste the whole document without finding all of the details. The document is about 5000 words and will be almost the same except for the field values (ie title of book, description, etc.) Can it do this without limitation before I waste time? I used to use filmmaker pro before claris wanted a million dollars for it every month. Thanks
Legend.
A little misleading because you can't really use excel as a relational database. You are only using excel in this video as a single flat table with only 1 clear purpose. But try this technique to track a hospital patient health record. Tracking not only admission, doctor and diagnosis, but track all the case notes, all the separate treatments, all the treatment barriers. Then you really can't use excel. It would be foolish and easy to accidently create redundancies.
👆🤓
@@danielmadsen100that will be the consolidation function
Ty, much of the data is repeated. Can you create a dropdown or combo box so you don't need to type it in every time?
Thankyou its very helpful
can you show how to input data into the Excel Database with Power Automate, please.
Great intro, thanks!
Fun fact as a data scientist the first thing they teach you is that there is a huge missconception where people think nd try to use excel as a data base while its not...
How do we add two columns to the spread sheet:
Ennea (this will be a number in brackets next to their name)
Source (this will either be in brackets or at the bottom in notes)
Xx
Can you help me solve this question?
Using appropriate spreadsheet and database application
1. Create a database on a spreadsheet table such that update of the records in the spreadsheet table should not affect records in the database
Secondly, The records of the spreadsheet table should consist of five columns of which the first three accepts text and the last two takes numeric data
To achieve this, you can use Microsoft Excel as your spreadsheet application and Microsoft Access as your database application.
**Here's a step-by-step guide:**
**1. Create the Excel Spreadsheet Table:**
* Open a new Excel workbook.
* Create a new sheet and name it "Spreadsheet Table".
* In the first row, enter the column headers for your table:
* Column 1: **Text Column 1**
* Column 2: **Text Column 2**
* Column 3: **Text Column 3**
* Column 4: **Numeric Column 1**
* Column 5: **Numeric Column 2**
* Enter data into the remaining rows, ensuring that the data types match the specified column headers (text for the first three columns and numbers for the last two).
**2. Create the Access Database:**
* Open Microsoft Access and create a new blank database.
* Create a new table and name it "Database Table".
* Add five fields to the table, corresponding to the columns in your Excel spreadsheet:
* **Field 1:** Text (specify the appropriate text length)
* **Field 2:** Text (specify the appropriate text length)
* **Field 3:** Text (specify the appropriate text length)
* **Field 4:** Number (specify the appropriate data type and size)
* **Field 5:** Number (specify the appropriate data type and size)
**3. Establish a Link Between the Excel Table and the Access Database:**
* In Excel, go to the **Data** tab and click **From Other Sources**.
* Select **From Access**.
* Browse to the location of your Access database file.
* Choose the "Database Table" and click **OK**.
* In the Import dialog, select **Create a new table**.
* Click **OK**.
* The Excel table will now be linked to the Access database.
**4. Update Records in the Excel Table:**
* Make any necessary changes to the data in the Excel table.
* When you save the Excel workbook, the changes will be reflected in the linked Access database table.
**Key Points:**
* The link between the Excel table and the Access database ensures that changes made in Excel are automatically updated in the database.
* This approach prevents accidental overwriting of data in the database, as the Excel table acts as a separate data source.
* You can use Access to perform more complex database operations, such as querying, filtering, and reporting, on the linked data.
By following these steps, you can effectively create a spreadsheet table and a linked database that allows you to update records in the spreadsheet without affecting the database directly.
How about an employee database?
Any news on this? Did you manage to solve it?
Helpful
I'd like to make a database of photos with their website link in Excel, please. Please can you advise? Thank you
For a warehouse let’s say I have 100 in stock and then I took 60 the remaining stock will be 40 I can show that in that data base??
If I'd like it to be in another excel page, is it the same process?
I am trying to create a page on my spreadsheet that collects all inputted info for a year. The spreadsheet will have info for each month but I won't the extra sheet automatically collect all info for a year. Any help is appreciated.
This isnt a database, pal. Is a spreadsheet.
I got lost when you added the DCOUNT DCOUNTA DMIN DMAX DSUM. HOW DID YOU GET TO CHOOSE THOSE. WHERE ON EXCEL DID YOU CLICK?
i highlighted the mini table but it doesnt work when i change the region
thankyou
You're welcome!
So a normal AVERAGE is for ad hoc calculations, and a DAVERAGE is for a properly managed database. Is that correct?
In simple terms, yes. AVERAGE can be used in both examples but DAVERAGE only when Excel has been structured correctly. DAVERAGE will help you troubleshoot the formula at a later stage if something goes wrong whereas AVERAGE could take longer to workout where the issue could be.
Great - am looking at using this as a records management tool - not financial data manipulation....any tips or signposts to other presentations? Do you post up excel templates with the formulas on anywhere to practice on? Thanks again :)
Any updates?
For the month/date criteria, I tried referencing a cell which contains a date, such as, today()-180 and it does not work. Is there a way to do that?
Not sure what you actually mean, however "=TODAY()-B1" would be today's date less the number of days listed in cell B1 or "=TODAY()-180" if B1 = 180. Hopefully this helps.
@@ThatOfficeGuy Thanks for responding. I gave that a try and it didn't work. Sorry I wasn't clear enough.
In your video, you were getting the DAVERAGE and DSUM for the Region and Month.
Let's assume, you have a date, which is MM/DD/YY instead of a month (Jan) and you want to DSUM all sales for the last 30 days. So the criteria would be something like >=today()-30. But I'm not able to get this to work.
Assuming, you found a way to get this to work, can you see if you can get this to work when the number 30 is stored in another cell and the criteria will reference that cell.
Thanks for your help.
When you do any adding or subtracting, make sure that both of the data sets are in the same format. You can't subtract 06/08/2021 from 365 the format doesn't align. If you instead convert it to a number or convert both sources of data to a date then it might work
I need a spreadsheet to manage staff monthly savings and loan with a database
Have you been starting to store data's and files to the modern science focused non physical data and files storages as back up data and files storages because there's also modern tech newly developed intrusion methods that can penetrate the usual encryptions and usual system defenses which necessitates modern science advancements varieties of data and files storages so that those years and years of works and encodings and funds used for storing those data's and files won't go wasted and wont be stolen by other hi tech companies specially the organization involved belong to a large alliance with plenty of resources because there's stiff rivalries of opposing alliances nowadays trying to dismantle the other?
❤
how did you record like that? what software did you use? 🙏
It is probably OBS
How does it escalate numbers
sorry it was not visible the criteria. To small video
From when a table became Database ???
oh god it's not a database it's a table all the data is jumbled up sorry as a programmer this triggers me. It looks like a failed A-Levels database normalisation answer
I want to create a birthday profile for client on excell so me the fumular
is it possible to create a searchable database using multiple criterion (two or more columns from the database table)??
Create relationships with what you want to search for, Insert a pivot table using the database, then move the row headers around until you get what you’re looking for.
Lots of good information but the presenter's cursor is flying around all over the place - too fast to keep up with.
there are problems with the audio, it glitches and hurt my ears :(
Hi, I need to create a form in excel. everytime I press on submit form, a new data entry should be created in other sheet. And if I want to modify any old entry, I should be able to recall that entry in that form for edit. Kindly share me any video link or suggestion. Thanks in advance.
Any information on this one?
I can't hear you
Sorry, excel is amazing at crunching numbers, but it is not a database despite having DB formulas and even allowing expert users to build jaw-dropping databases in it. Microsoft has delivered several function to Excel to act like a DB and kill Access, but they could not kill it yet.
Using excel to maintain a database is similar to traveling from Argentina to Japan in a canoe, whereas Access would be a small plane and SQL DBMS would be a Boeing airliner. Just make your choice right.
What i was thinking, despite it being good at storing data your're better off using phpmyadmin
i thought you were tom cruise
That is zoom class 😆😜
Excel is not a database!!
Kandungan anda sangat menyentuh
Excel is not a database; it is a spreadsheet.
Purila sir
You’d get more views if you didn’t have such long adds
l
why
Easier to learn to use Access, don't use a hammer to undo a screw :(
It does come down to choosing the right tool for the job, it all depends on what your goals are. I have used Access for years but the 2GB limit is a problem for most, I now use Power BI and it does a good job for what I need.
@@ThatOfficeGuy the 2GB is the same for both Excel and Access but in fact you can have 2GB per field if you split the database, I've been using Office since 1983, but as you say use the right tool for the job. Excel for spreadsheet's & Access for database's
@@andybawn1 Access is pretty much dead while OFFICE 365 will to defacto standard now. Access is much better but for the average user it would take some time to build. It is complicated for people who needs to just data entry than excel.
Excel is not a database product and if you think it is you are way off beam. Go back to your books.
Get a new mic
Why are you using Excel to create a database 😂
can you show me about how to create student databaces