Excel Data Mining SIMPLE BUT POWERFUL TIPS
EXCEL DATA Mining As it’s obvious that most of the businesses use the grids of columns and rows to store information in Microsoft Excel. It is also used as a database in order to organize the information. So mining excel sheets is the most trending & valuable task in order to extract the relevant information. This can be achieved successfully by using the different Add-ins, which make functions less time-consuming & easy too..
SIMPLE TIPS To Practice the Excel Data Mining in a Useful Manner with Various Excel Add-ins.Â
01
Change the format of numbers
When someone import data from sources other than excel numbers are imported as text. So to convert 1000 values, you’ll need to wait for some moment in excel. In Power Query Add-in, this is even easier, just: • Right-click on the column header of the column you want to convert. • Go to change type. • Then select the type of number you want (Decimal, whole number,..)
02
Unpivot Columns in a Data Set
If you are going to create a PivotTable or use any statistical package, it is strongly recommended to have each variable in a single column. With Power Query Add-in, it can be achieved in a quickly: • Select the columns you want to pivot • Right click and select Unpivot Columns.
03
Merge Data from Several CSV Files into a Single Folder
Sometimes the data is stored in several CSV files and is required to be imported & merged into a single worksheet. With RDBMerge Add-in it can be achieved successfully: • Go to Data and click on ‘RDBMerge Add-in’ • Within a dialog box, click on Browse • Browse the folder and press OK • Select CSV files from dropdown list • Select cells range you want to extract. To import, select the First cell. • Click on Merge
04
Fill Empty Spaces in the Excel Content
If you are a data analyst, surely you will receive data with empty spaces.You can exclude blank space with power query easily. • Within the Power Query select the columns where you want to fill the empty spaces. • Right-click on the header of any of them • Go to Fill>Down
05
Counting Unique Items with Pivot Tables
It’s extremely easy to count unique items in a table using the Excel Data Model. Steps are: • Open the Pivot Table dialog box (Go to Insert->Pivot Table), select the data source and check on “Add this on the data model”.
• Insert all the desired fields for the Pivot Table(Row fields,Columns fields and value fields). • Change the summary function for the field you want to count to “Distinct Count” • Right-click on any value of that field • Go to “Summarize Values by”> More Option > “Distinct Count”
LOOKING FOR EXCEL DATA MINING EXPERTS ?
COGNEESOL Phone: +1 646 688 2821 Email: info@cogneesol.com Address: 30 Wall Street,8th Floor New York,NY 10005-2205