Categories
Microsoft Excel

Using Get & Transform in Microsoft Excel 2016

Get & Transform is a powerful data analysis tool in Microsoft Excel that allows you to import, refine and work with data from a range of sources, including files saved to your computer and web pages on the internet. You can shape data in order to display the details relevant to your purposes and hide information that is not required, without making permanent and irreversible changes to the data.

Categories
Microsoft Excel

Understanding Advanced Filters in Excel

Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter in Excel enables you to analyse the data in a list by filtering it according to specific criteria. Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter in Excel enables you to analyse the data in a list by filtering it according to specific criteria.

Categories
Microsoft Excel

Creating Slicers in Excel

Slicers are special field filters that can be applied to Excel tables. They are most useful for further dissecting an existing PivotTable report in a worksheet. Slicers are actually graphics objects comprising of a rectangle and special filter buttons. Slicers are inserted into the worksheet from the Slicer command on the INSERT tab of the ribbon.

Categories
Microsoft Excel

Understanding Power Pivot in Excel

Excel’s reputation as an analysis tool grows with each successive upgrade. While data analysis was originally confined to data in a worksheet, Excel is now being used for analysing data from a variety of sources including databases and larger corporate data sources. Whilst this can be done using Excel itself, Power Pivot is an add-in that consolidates the steps towards achieving this.

Categories
Microsoft Excel

The PMT Function in Excel

PMT is an abbreviation for payment. The PMT function calculates the regular payment that needs to be made for an amount of money, based on a fixed interest rate and for a fixed duration. It is ideal for estimating the regular repayments to be made for a loan, but remember that in most situations fees, taxes and charges are added, so check with your lending institution first.
The general function to calculate regular repayments on a loan is =PMT(interest rate, duration, loan amount).
Before you start, ensure that the data you are working with in your Excel worksheet looks similar to the screen capture in Step 1.

Categories
Microsoft Excel

The DATE Function in Excel

The DATE function in Microsoft Excel is used to assemble a date from three separate pieces of data, being the year, month and day. This is useful if the information is provided in separate columns and you need to combine them. DATE will take the year, month and day and convert them to a single serial number representing the date. The result is automatically formatted as a date.
The general function to convert numbers into a date is =DATE(year, month, day).
Ensure that the data you are working with in your Excel worksheet looks similar to the screen capture in Step 1.

Categories
Microsoft Excel

The NOW Function in Excel

The NOW function returns the current date based on the computer’s system date, the NOW function returns the current date and the current time. While the NOW function can be used for dates, it is mostly used for working with time arithmetic. Read on to learn how to use this function!
The general function to work with time arithmetic is =NOW().
Before you start, ensure that the data you are working with in your Excel worksheet looks similar to the screen capture in Step 1.

Categories
Microsoft Excel

The TODAY Function in Excel

Excel converts dates into sequential numbers beginning from January 1, 1900, you will be able to use a range of special functions dedicated to dates. The TODAY function returns the sequential numbers for today’s date and will update accordingly if the workbook is opened on another day. Get excited!
The general function to find out the sequential number for today’s date is =TODAY().
It is also handy to know that the sequential number for TODAY changes every day, so your worksheet will show updated results each time you open it.
Before you start, ensure that the data you are working with in your Excel worksheet looks similar to what is present in the screen capture in Step 1.

Categories
Microsoft Excel

The MOD Function in Excel

The MOD function is useful maths function that will return the remainder after a division has taken place. MOD is an abbreviation of modulo or modulus. While it actually performs division based on the two arguments that must be given, it only returns the amount remaining after that division has occurred. Follow on to learn how it works!