excel-pivot-table

5 Helpful Excel Pivot Table Tips to Incorporate

The truth of the matter is that when it comes to large amounts of data sets and analysing them quickly, there is no greater feature in Excel than the Excel Pivot Table tool. Versatile, easily used, speedy and accurate when executed correctly, the tool is one that you will find yourself using to sort your data often. If you are new to Excel Pivot Tables, there are 5 helpful tips that you should definitely incorporate into your work:

1. Grouping

The data in your Pivot tables can be grouped using numbers. With this capability, you can actually make use of it to customise your group by a lot of things. For example, data ranges for age, for price, for numerics and so on so forth as long as it allows easy deciphering for your reports. Simply make use of the Excel Pivot Table tool to create a PivotTable and add your relevant elements into the relevant labels provided. You can then right click any data value and select Group. This will enable a dialog box that will let you enter different intervals (in the BY section) as you require.

2. Syncing

To save yourself a lot of time, maximise the use you get out of the Excel Pivot Table tool by making tables your source data. When you do that, Excel automatically helps you expand/shrink your table with every addition or decrease of data. Your PivotTable will always be in sync with the changes. Select the data range you want and create your table first, then choose to summarise with the pivot table tool.

3. Naming/Renaming

Excel PivotTables usually display names that are in it’s source data. So if you run into cases where you have to change the field names, you can actually do so easily without having to redo the table again. Simply choose the field name that is displayed in the PivotTable and manually type in your new name. However, do note that you cannot use the same field name twice. A feasible alternative to that would be adding spaces towards the end of your field name. You can also consider adding fields as a value.

4. Formatting

Automatically format the value fields of your PivotTable by ensuring that the number format on the relevant fields are already set. Choose the cells directly and apply the format first or use the field settings (Value field settings) from the menu to format as you desire.

5. Data Removal

If the data in your PivotTable is no longer necessary, then you can actually choose to remove it without compromising the functioning ability of your PivotTable. PivotTables come with something known as the pivot cache- which contains a duplicate of the data that was used to create the table. Make sure to refresh so that your pivot cache is up to date then delete the data you do not need. You will still be able to make use of your PivotTable as usual.

excel-experts

5 Excel Experts to Learn From

It can be difficult navigating your way through the ins and outs of software programs on your own at times when you don’t know which directions to proceed in. If you have a passion for all things Excel and wish to learn more about the software, what better source to seek insight from than the Excel experts themselves? There are currently 94 and counting special individuals that have been recognised officially by Microsoft with certifications (Most Valued Professional Certification), in acknowledgement of their skills in advanced analysis of data and programming using Excel.

Here are 5 Excel experts you should definitely learn from if you are a beginner:

1. “Mr Excel”

Out of all Excel experts out there, there is one notable Excel expert known as “Mr Excel”. Mr Bill Jelen has quite a rich background with Excel, beginning from teaching to running a publishing company with other Excel MVPs and co-publishing Excel books. He has since then progressed on to creating a website using his moniker, where he makes free resources available to interested Excel users. You can also opt for DVD tutorials, listen to his podcasts on excel tips, read through the tutorials or participate in his user forum where you can interact and ask him questions.

2. Chandoo

Passionate about processes like data visualisation and data analysis, Excel Expert Purna (Chandoo) Duggirala began developing and converting a personal site into a platform for Excel discussions with like-minded individuals. The Excel expert has since then began to share free resources and tutorials for interested Excel users to learn from. Not only can you glean insight from the practices he places up, you can also purchase customised templates, enroll for courses, trade insight in the online forum section and receive tips from newsletters through his website.

3. Debra Dalgleish

From humble beginnings as a private consultant, Debra Dalgleish has since honed her skills into that of an Excel expert, creating three books about Pivot Tables and designing Excel applications. She has conducted a vast amount of Excel training classes and operates another resource for Excel users- Contextures. The resource website contains valuable information, like video tutorials, download-friendly resources, and practices on applications.

4. Dick Kuseleika

Dick Kusleika is one of the most notable Excel Experts out of all Excel experts. Stemming from a job that mainly features avionics tests, Dick Kusleika studied his way into achieving enough Excel knowledge to specialise in Excel consulting and even co-authored two Office and Access books! He has since progressed on to running an Advanced Excel blog- Daily Dose of Excel, featuring on excel programming, data analysis/visualisation and other interesting practices. He has also invited a host of other Excel experts to co-write with him, adding on to the user experience for those interested in Excel.

5. John Walkenbach

John Walkenbach is another well-known Excel expert. Owner of a website known as the Spreadsheet Page, the Excel expert frequently updates with Excel resources and tips for intermediate Excel users. Interested Excel users can find Excel add ins, tutorials, practices and even video tutorials.

These are the 5 Excel experts you should definitely be learning from in order to further your learning when it comes to Excel. However, if you are looking for more hands on and real time practice, you can consider signing up for local Excel training courses conducted by experienced trainers instead for starters.

loading-using

Why You Should Be Loading And Using The Solver Add In With Excel

Whenever users mention Excel, the solver add in is always pretty quick to follow into the conversation. There’s a good reason why this is the case, and that reason is also why you should be using the solver add in together with Excel. Here’s what you have to know:

The Solver Add in is a specially designed add in program for Microsoft Office Excel that is made available when the user installs Microsoft Office or Microsoft Excel. The Solver add in is essentially an add in that works as a suite of commands, also known as what if analysis features. With the add in, you will be able to easily find maximum or minimum values for formulas under different constraints or limits, and values. The solver add in is also capable of adjusting values of decision variable cells to produce the results the users need. Through a variety of different methods like linear programming, evolutionary algorithms and more, the solver add in is a formidable tool to have, especially when users need to perform data analysis.

However, the add in is only usable after you load it into Excel.
Users can load the add in by looking for the File tab and choosing options. You will find the selection for Add ins inside the options list. Choose the option and you will be directed to the manage box. Choose Excel add ins and authorise the move by selecting Go. You will be directed to what add ins are available for your Excel software. Locate the solver add in and select the check box beside bit before choosing okay. If you receive a prompt saying the solver add in has not yet been installed, you may have to install the add in manually first.

Once you have loaded the solver add in, the function would be available under your Data tab, right in your analysis options! If the loading is successful, you will then be able to begin to explore and use the solver add in with no problem.

The add in’s main purpose is to help users arrive at a solution, hence it’s widespread popularity with Excel users. Do know that however, the solver add in is not a fix all. The add in mainly uses three requirements to derive solutions- mathematical relationships, model size and integer constraints. If your projects contain poor scaling, it could affect the add in’s ability to derive a solution, costing time and quality.

does-and-donts-in-reports

The Dos and Don’ts of Producing MYOB Reports

The MYOB software allows users to perform a series of essential business practices in their day to day working processes, and one of their capabilities would be their ability of allowing users to produce MYOB reports. However, not everyone require the same default set MYOB comes with. To accommodate that, MYOB allows users the opportunity to create their own custom MYOB reports. If you are new to producing MYOB reports, here are some basic guidelines for you:

Do select all data needed for printing

When printing MYOB reports, save yourself the time from having to skim through page after page by simply choosing to print only the relevant ones. Filter through all the information by choosing categories/data ranges that you want included. You can do so in the Report Customisation or Report Display windows. These windows are found under the Report Menu. Locate the index to reports option and open it up. Choose the reports you want and click on either display or customise.

Do select the correct finishing options

Finishing options for your MYOB reports are a good feature to remember to use since it provides additional options for you to choose from. For example if you want to reuse the settings of your MYOB report, you can choose to add to the reports menu, and safeguard your data from being changed during report generating. This is an effective way to ensure that there are less possibilities of having to reprint your report.

Do change report formats

Visual engagement is always important when it comes to documents, including your MYOB reports. Make sure your reports are legible and easy to read by making use of the report format options. By making use of the Format option under the Report customisation window, you will be able to make changes to the report’s font, size, alignment, the effects as well as page margins.

Don’t forget to save

Remember to save your customised MYOB reports as templates for future reuse whenever necessary. Eliminate the hassle of wasting time having to redo another MYOB report from scratch. You can locate your customised MYOB reports in the custom view under the index to reports dialog box. Make sure to back up an original copy of your MYOB files just in case before you apply the changes.

Don’t forget to use preview

Before printing, a good practise to do would be to make use of the display option to check that your report is ready for printing. This is the stage where you can still make changes if the print preview of your report is not ideal. To access print preview, look for the View list and choose the Print preview option. Print only when your MYOB reports are ready.

These are the basic guidelines to producing MYOB reports. Take a while to experiment with your MYOB reports, it can take some time to create ones that best fit your corporation’s requirements but the results are always worth it. If you wish to learn more about the full extent on what MYOB is capable of, it is recommended that you take up MYOB training classes for a guided experience.

list-and-records

Keeping track of MYOB lists and records- 5 practices

With all your MYOB records, keeping track of them can pose to be a large and rather overwhelming task. However, as long as you make use of these 5 practices, you will find that keeping track of them is not as complicated as it seems to be. Here is what you should regularly practise. Make use of these 5 steps and you will be able to retrieve, find and organise all your MYOB lists properly:

1. Finding Records

Your MYOB records are stored under relevant lists. As the lists get longer, finding your records manually can take up quite a while. So this is why the MYOB software has made the options for refined searching available. Users can search for records under tabs, or using the search fields to narrow down their searches. The search results can be filtered through a series of different search terms and fields. Do so and press tab once you are done to sort the list. You can reset by clicking icons next to your search fields. You can also make use of the advanced filter options to further narrow down your search.

2. Changing Records

There are some areas to MYOB records that users can make changes to. To do so, access the MYOB list that the record is stored under and look for the record you want changed. Once you have successfully located the record you want, make use of the zoom icon (arrow) next to the record. This will enable you to see the record details in the information window. Make the changes you need and simply click OK to successfully change the records.

3. Activating/Inactivating

Keep your MYOB lists properly managed by inactivating records you don’t use. This will remove the options from selection lists and shorten your records so selecting things will become easier. You can still use inactive records if you have to, but you will have to manually enter it. Look for the records you want to inactivate or reactivate and click on the arrow next to them so that the details are available. Choose the profile tab and look for the option to select.

4. Deleting Records

MYOB users are able to delete records that are no longer required. However, in order to do so, their records have to meet a certain condition- they must not be used in transactions. If they have been used in transactions, they will be required to delete the related transactions before being able to delete the records. To delete, visit the Lists menu in MYOB and choose the related list. Find the record and choose the arrow next to the record so that the details appear. You will then be able to access the edit menu and manually choose to delete the record.

5. Combine Records

There are times where one will find themselves at a need to combine two MYOB records into one. For example, a merge between two suppliers or accounts for different departments and the like. In order to accommodate that requirement, MYOB allows users to do so. However, users will have to identify the primary and secondary records when combining them. Take note that the primary MYOB record will become the source that stores all the transaction information (including the transaction information from the secondary record) since the secondary record will be deleted so make sure to use the combine function carefully.

These are the 5 practices one has to make sure to do in order to keep track of MYOB lists and records. While getting used to them can take some time to ease into, as long as you are able to keep these in mind and regularly keep track of your records, finding them would take no time at all!

International-transaction

5 Things to note about international transactions in MYOB

For many SMEs, MYOB is an ideal accounting software to keep track of their data. Not only can you manage your payroll, keep track of your invoices and customer records, you can even input transactions for better budget planning. While most corporations require only local transactions since the nature of their business is local based, there are cases where one has to deal with international transactions. In order to maintain international transactions using MYOB, there are 5 things to take note of:

1. Multiple Currency

When it comes to keeping track of different currency transactions, it’s essential to take note of the currency exchange fluctuations so you can predict how it affects your business. Hence, MYOB comes with options for reports that can assist the user in managing unrealised gains/losses according to the potential changes in international transaction value as well as realised gains/losses (actual changes in value). In order to do so, you will need to use the Multiple currency feature in MYOB. But you will have to set preferences accordingly before you do.

2. Preferences

Before you are able to begin maintaining multiple currency transactions, you need to first configure the settings and tailor your MYOB software to your requirements. Look at the default settings for the preferences before entering transactions and make sure it’s set appropriately. You can do so by using the Setup menu section and choosing the easy setup assistant. Click customise and make sure that the Deal in Multiple Currencies option is enabled so you can perform multi-currency accounting.

3. Records

When you use the MYOB software, there are already some international currencies that have automatically been set up for you. But if you are doing business with other currencies, you will have to create records yourself. To do so, choose the Lists menu and select the Currencies option. Then select new to create a new currency type and enter details accordingly before choosing OK.

4. Exchange Rate

When it comes to international transactions in MYOB, users are given the option of using default exchange rates or manually enter new rates for the transactions. To change the default exchange rate, look for the command centre and choose the Lists menu. Look for the Currencies option there and choose it so a dialog window opens up. Choose the currency entry to be modified and click edit. You will then be able to make the relevant changes.

5. Currency Calculator

The currency calculator can be used to calculate exchange rates based on the foreign and local amounts. You can access this nifty feature by using the Help menu in your MYOB software and locating the currency calculator from the options available. The currency calculator can help track fluctuations in exchange rate as well as value of your transactions. Take note that since the currency exchange rate is constantly fluctuating, there are different methods you need to use in order to reconcile accounts that contain international transactions. Set security preferences to allow changes to these records and click Rate against the record to change the exchange rate so it matches your bank statement. Alternatively, you can choose to reverse the transaction from the edit menu, which will enable you to retype the payment with the current exchange rate.

These are the 5 things to take note of when dealing with international transactions in MYOB. As long as you make ensure that the crucial data values are accurately entered, things should go smoothly for you. International transactions often require a bit more attention to detail since there are issues like Exchange rates to keep in mind. So remember these and keep them in mind when dealing with transactions of this nature.

5-benefits

5 Benefits of Attending Excel Training Courses

Software programs take some time to explore and get accustomed to. If you are new to Microsoft Excel and require direction when it comes to learning just what Excel can do, you should consider signing up for Excel training courses. By doing so, you are paving your way to new opportunities, capabilities, knowledge and more productivity. Still not convinced? Here are 5 reasons why you should consider signing up for Excel training courses:

1. Usefulness

Excel training courses are usually made available by training agencies or professional trainers who possess relevant experience as well as practical knowledge. By allowing yourself to be coached by experts, you place yourself in the position to learn not only the functions of excel, but also stand to gain from additional advice or insight offered by your trainer. You can take the opportunity to have all your doubts/questions answered and will be able to fully benefit from the experience since the trainers are in the best position to show you how exactly you can use Excel for your business. For example, data analysis is useful when generating financial reports and other relevant business tasks.

2. Resources

Most training agencies who offer Excel training courses are likely to recommend additional resources that are reliable, accurate and effective for new Excel users. They could even be only accessible for use exclusively for course participants. Not only will you be able to own reference material that could be used for practise and continuous refreshing of your skills, you will also be able to learn how to maximise the use of such material. For example, downloading and activating add ins to your own Excel program for daily office work!

3. Tailored Direction

Most Excel training courses come with a curriculum that is tailored for the course participant. Some training agencies even go a step further by designing basic, intermediate and advanced learning level based ones just so they can better match up to the participant’s learning level. You will be able to learn what you’ve not touched on before, gain a good grasp on what an average learner’s command of Excel should be, and receive updated training practices that are more relevant to the requirements today’s corporations require.

4. Problem Solving

Even the best software programs can become occasionally vulnerable to certain bugs or errors. You will no longer have to spend a long time consulting the official Microsoft Excel staff for troubleshooting solutions, or be at a loss of what to do when your software contains vulnerabilities. You will be able to learn how to eliminate the inconvenience in less time than required, all by yourself. This saves you a lot of time and frustration from having to wait to use your program again.

5. Productivity

Understand common excel errors and learn to avoid or spot them quickly so that you can create more accurate data reports at a faster time. Manage your workload faster with the important tips you have learned from your Excel training and free up some of your schedule for more productive things.

These are just 5 of the reasons to consider attending training courses. If you prefer local-based Excel training courses, do some research before attending one. Some training centers like MOCD studio offer Excel training courses that are startup friendly, and you can even stand to gain by claiming for things like PIC if you fulfill the conditions.

Page 1 of 1412345...10...Last »