Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.
But what if your data source is not in your Excel spreadsheet but located on your desktop?
If it’s inside a CSV file – Comma Separated Values which is denoted by a .csv file extension & where the columns are separated by commas – it’s very easy to import data from csv and right into Power Query!
It’s very common nowadays to get data in the comma-delimited format.
Let’s suppose you have this set of data from the csv file:
The #1 complaint that I get from Pivot Tables is “Why do my values show as a Count of rather than a Sumof ?”
Well there are three reasons why this is the case:
1. There are blank cells in your values column within your data set; or
2. There are “text” cells in your values column within your data set; or
3. A Values field is Grouped within your Pivot Table.
1. BLANK CELL(S):
So if you have at least one blank cell in a Values column, Excel automatically thinks that the whole column is text based. Pretty stupid but that’s the way it thinks.
2. TEXT CELL(S):
Also if you have a cell that is formatted as Text within your Values column, then it will also cause it to Count rather than Sum. This usually happens when you download data from your ERP or external system and it throws in numbers that are formatted as text e.g. 382821P
A lot of Excel users get confused when they are recording an Excel Macro and they get prompted where they want to store their Excel Macro in?
Store Macro In
You have 3 options:
* Personal Macro Workbook
* New Workbook
* This Workbook
During the initial setup of recording a macro a prompt will present itself asking where the macro should be stored. This option ultimately determines not only where the macro will be saved, but also where you can access the macro and where it can be used.
The “THIS WORKBOOK” option will create a module within the active workbook where Excel will save the macro (VBA Code). By storing the macro in the active workbook, the macro can be used within the that workbook or if that workbook is open.
Since the macro is contained with the workbook it was created within, if the workbook is sent to another Excel user or placed on a network drive, the macro will be there as well and other users can use that macro when that workbook is open on their system.
Files that contain a macro or VBA code must be saved as a .XLSM (MACRO ENABLED WORKBOOK) or .XLSB file.
Some Common Uses:
Macros that are specific to a workbooks data
Macros that make data connections and refreshes of data contained with the workbook
Macros that clean up data specific to the workbook
Personal Macro Workbook
The PERSONAL MACRO WORKBOOK is a hidden workbook located on a machine that contains Microsoft Excel.
The workbook is always open, but, hidden from view by default.
If a macro is stored in the PERSONAL MACRO WORKBOOK, this macro can be used within any workbook on the computer that contains that personal macro workbook.
Macros stored in a personal macro workbook are not typically shared through normal exchange of Excel files. To share a macro contained within the personal workbook, one would need to explicitly share their personal macro workbook, export the module that contains the macro or copy the code to another file and send that new file.
Some Common Uses:
One-time setup macros that can be used on multiple workbooks
Macros that are more specific to an individual job that isn’t shared among others
Repetitive tasks necessary across multiple Excel files
A NEW WORKBOOK creates a brand new workbook at time of recording. This new workbook will contain the macro and all of the VBA code.
In order to use these macros you must open the new workbook that Excel created.
The webinar will be held between Tuesday the 27th of February and Wednesday the 7th of March! The times are shown on your local timezone, so please click the link below to register for a day and time that works for you..