But supposing, for whatever reason, you don’t want to use an Excel database as your pivot table’s data source? Well, there are some other options to create a pivot table without manually entering the information into Excel first. Here are a few more data sources that you can use to create a pivot table in Excel.
Office data connection files
The office data connection (ODC) file extension was created by Microsoft and contains properties to connect to and retrieve data from an external data source. It contains a connection string, data queries, authentication information and other settings. Microsoft recommends that you retrieve external data for your pivot tables and reports using ODC files.
External relational databases
If, for instance, you’re using another relational database program, like Microsoft Access or Filemaker Pro, you can also import data directly from these programs into your pivot table, rather than manually entering the data into an Excel worksheet. In the case of connecting data from an MS Access database, you can do this quite simply by selecting Access from the ‘data source’ dialog box. For all other external databases, you would select the ‘from other sources’ dialog box and follow the steps in the data connection wizard.
Using another pivot table
Each time that you create a new pivot table, Excel stores a copy of the data for the report in memory, and saves this storage area as part of the workbook file. To use one pivot table as the source for another, both must be in the same workbook. If the source pivot table is in a different workbook, copy the source to the workbook location where you want the new one to appear. Keep in mind that when you refresh the data in the new pivot table, Excel also updates the data in the source pivot table, and vice versa. When you group or un-group items, or create calculated fields or calculated items in one, both are affected.
Create a database in Excel first
The easiest and most efficient way to create a pivot table is to create a database in Excel first. Here, you can update and manage as much information about your business — including customer data and financial data — and then use that as a data source for a pivot table.
MICROSOFT EXCEL IS THE most widely-used spreadsheet application in modern computing. It’s ubiquity means most people use Excel on a regular basis, despite never having had any formal training in its many, many, MANY functions.
With its 2013 release, Excel got a serious update, which made it the perfect application to create and manage client and customer databases. Although there are many CRMs available on a subscription that provide the same functions of a database created in Excel, just in a more visually appealing format, they often lack reporting and analysis functions, requiring you to export your data in a Excel sheet anyway.
Flat file databases
Excel’s original ‘flat file’ database still remains the easiest and most basic database to set up and manage, and depending on your business and how you’ll use your database, a flat file database may be all you’ll ever need. If set up correctly, a flat file database will allow you to easily import your customer data into Word, your accounting software, an email marketing service, and so forth.
A relational database is a database that’s structured to recognise relations among the information stored in them. Microsoft offers a relational database program, called Access, which is available with Microsoft Office Professional or higher, or can be purchased separately.
Alternatively, you can create your own relational database in Microsoft Excel, providing you have the 2013 version or newer. When Excel got its update in 2013, it became easier to link charts and cells and to perform searches — all essential features if you’re working with large amounts of business data.
Correct Excel set up is crucial
Once Excel has been set up, it’s as easy as it is powerful to use. Of course, the key is to set it up correctly, so you can avoid errors or having to re-enter large amounts of data to make the format suit another third party software application.
Just as we do with our MYOB training courses, we also offer Lifetime access to our MS Excel and Word training courses, which means each time Microsoft releases a new version of MS Office, we update our course content so you’re always totally up to date with the latest versions of Word and Excel.
Keeping Excel in Your Memory
Let’s be straight up here; once you get the hang of Word you probably won’t need to refer back to our course content all that much. Excel, on the other hand, is a different ball game.
In many ways Excel is a lot like algebra or a foreign language: if you don’t use it often, you’ll forget it. Sure, you’ll remember bits — J’adore Dior! E = mc2! — but you’re likely to struggle through your day-to-day if it’s something you rely on heavily at work.
Because many people use accounting software like MYOB, their use of Excel is fairly infrequent. For instance, suddenly trying to create a PivotTable will probably leave most of us stumped! And let’s not forget that by the time most users have mastered how to create macros in their worksheets, Microsoft will have released a new version of MS Office and we’ll be back to square one again. This, in large part, is a key reason why we offer Lifetime access to our training courses— because we, just like you, also forget stuff.
New Features in New MS Word and Excel (in a Nutshell)
Now that Microsoft has released their highly anticipated MS Office 2013, it’s likely you won’t be able to find the ‘Paste Special’ button again, so we’re updating our course content to reflect the new changes.
Here’s a low-down on some of the new features in the new MS Word and Excel:
Open and edit PDF files in Word – finally! Gone are the days of having an additional piece of software installed on your PC to enable this.
Threaded review comments
Read mode with page turning
Alignment guides – hallelujah! Why have they never had this before!
Flash fill – we’ve always had this to an extent, but flash fill just got a whole lot more intelligent!
PowerView – for the real Excel pro, but still a welcome addition.
New PivotTable tools
Improved functionality when opening new Excel windows
Recommended PivotTables and charts
New chart controls
Get a link
Publish Excel data to social media – we don’t recommend using this often, because snore. But it’s still great if you want to quickly share your yoy sales results with your Twitter followers or Facebook friends.
Although the cap on self-education expenses could do well to be a little higher — $5,000 per person, say — the cap itself won’t deter people from further study; from gaining the power of knowledge. It will only serve as an impetus for individuals and businesses to investigate other non-traditional learning options, such as online study and training methods.
Our Online Courses Below the Cap
For budding entrepreneurs or individuals seeking the skills to start their own business, or to manage a small business, our Small Business Management course is currently only $1,397 — well below the proposed cap.
We have reduced the price of our Small Business Management course, which is usually more expensive, for a limited time to allow you the opportunity to gain the necessary skills you need to start their own business or gain employment managing a small business without exceeding the self-education cap.
Take advantage of this great offer now and enrol in our Small Business Management course; discover the valuable skills you need to successfully manage a small business, including writing a business plan, conducting market research, legal and risk management, and much more!
We recently created 9 new training videos for our online MYOB training course and one of them demonstrated how to export and import data from an MYOB training company datafile. Exporting and importing is used to extract transaction and sales information and also customer or supplier card files from MYOB to use with Microsoft Excel in performing mail merges.
We have included the simple accounts list that we use in our online MYOB bank reconciliation course as a file you can download and open up in Notepad or Microsoft Excel. The image above shows what the file looks like when opened up in Notepad. It’s called a TAB-DELIMITED text file and this example includes a header record that gives you the name of each field (column)
If you need training on Microsoft Excel and data storage types you’ll be thrilled to hear that this course is one of 9 courses that are all included for one low price and LIFETIME student membership at the EzyLearn online Microsoft Excel course. Click here to enrol.
Computer software training is now available online for free. The video, training workbooks and exercises are created by Steve Slisar and EzyLearn and you can now enjoy the whole experience in your own home or office for no charge.
There is a tour at the website that shows you exactly how to use all the training tools available to you and shows you how to register for free.
This is the ideal training tool for schools to use to teach students or even make parents aware of it. It is also a good computer software training tool for seniors groups or other community groups or even experienced computer users to help members of their family like mums or grandparents.
Make sure you subscribe to our FREE Computer Training Newsletter to be kept up to date with new training placed online or Government funded training initiatives.