What is data mining?

Here are some important Data Mining definitions:

Mining means extracting something to find out important result. e.g. mining earth for extracting diamond, gold or coal. Data mining is an interdisciplinary sub-field of computer science. It is the computational process of discovering patterns in large data sets. Data mining tools predict about future trends and customers behaviors.

Data mining (sometimes it is called as data or knowledge discovery) is the process of extraction of raw data for different perspectives and transforming it into useful information. Further this information can be used to increase revenue, cuts costs, or both. This helps to most of companies to focus on the most important information and allowing businesses to make proactive, knowledge-driven decisions.

Data mining is goal of extraction of patterns and knowledge from large amounts of data, not only the extraction of data. It is also information processing which include collections, extraction, warehousing, analysis, and statistics of data.

Uses of data mining:

Data mining has been used in many applications. Some of the notable examples where data mining can be used are business, medicine, science, and surveillance. 

Data mining mostly used for business to analyse the historical business activities, stored as static data in data warehouse databases. Main goal is to reveal hidden patterns and trends to discover unknown strategic business information to increase business and revenue.

Data mining has been widely used widely in the areas of science and engineering field, such as bioinformatics, genetics, medicine, education and electrical power engineering.

Spatial data mining is the application of data mining methods to spatial data. Data mining offers great potential benefits for GIS-based applied decision-making and there are so many more examples where data mining is used now days.

Sources of data for mining:

World going toward digital, data is increasing in size day by day and it is difficult to store this data.

Some of the below sources that is increasing data day by day in our daily life.

1. Real time data captured in one electronic system (e.g. CCTV, Cameras, and Satellites)
2. Government reports
3. Historical data and information
4. Mass media products
5. Web information
6. Social media sites
7. Government Official statistics
8. Observation
9. Medical or scientific research data

Integrate excel data into word document

Most of the time we come across the scenario that we have complex data in Microsoft excel spread sheet and we want to copy some or more data cell into word from excel. We do copy and paste it manually, but think if we need to do it on regular basis, what we do?  We needs to think about automation between excel to word data integration. So how can we do that? This article will explain you about to how to import excel data into word or export excels data to word document automatically.

 

So here is the data of employee for salary in excel. We want that data integrated into word without copy paste manually. 

Step 1: Create word template in which we want to be integrate from excel, please see the below screenshot for salary slip, this will be a word template for every time use in future.

Step 2: Create Excel template from which data to be imported to integrate into word template as per cell required in word template. Please see the below excel template.

Step 3: Get the value of this field by any method(i.e. Vlookup) from excel data generated by payroll system. For this see below screenshot.

Step 4: Copy data for Employee Name from excel cell, Please see below screenshot.

Step 5: Place the cursor at Employee Name in word document where actually we want data to be integrated and press Ctrl+Alt+V(Paste special). Please see below screenshot.

 Step 6: Select Paste Link option button as Unformatted Text and press OK. Please see the below screenshot.

Step 7: Do it(Step 4 to step 6) for all fields of word for which we want to be integrate data from excel.

we will get finish product which will look like below.

For any help regarding this process, feel free to write in comment.

Display zero at the beginning of number in excel cell?

How to display zero (0) at the beginning of any number in excel cell?

Have you ever tried to do this in excel? If you try to add 0 at the beginning of any number in excel cell, that number will disappear and display remaining number. Following is the simple method to display zero at the beginning of any number of fixed length.

If you want three digits fixed length number in any one column. For example 022, 035, 080 in one column follow the below steps:

• Fill that numbers in one column that will look like 22, 35, and 80.
• 
  
  
  
  
• Select column, right click and select format cell as shown below.
• 
  
  
• In number Tab, select Custom.
• 
  
  
• Make type field empty and fill 000 manually in that field.
• 
  
  
• Press OK.
  

Excel useful shortcut keys

Keys Combination	Action/Description
ALT+SHIFT+F1	Inserts a new worksheet.
SHIFT+F2	Adds/Edits a cell comment.
SHIFT+F3	Displays the Insert Function dialog box.
SHIFT+F6	Switches between the worksheet
SHIFT+F8	Enables you to add a nonadjacent cell.
ALT+F8	Displays the Macro dialog box to create.
SHIFT+F9	Calculates the active worksheet.
SHIFT+F10	Displays the shortcut menu for a selected item.
ALT+SHIFT+F10	Displays the menu or message for a smart tag.
SHIFT+F11	Inserts a new worksheet.
ALT+F11	Opens the Microsoft Visual Basic Editor
ARROW KEYS	Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY	Moves to the edge of the current data region in a worksheet.
SHIFT+ARROW KEY	Selection of cells by one cell.
CTRL+SHIFT+ARROW KEY	Selection of cells to the last nonblank cell in the same column or row.
BACKSPACE	Clears the content of the active cell
DELETE	Remove the cell content
CTRL+END	Moves to the last cell on a worksheet
CTRL+SHIFT+END	Selection of cells to the last used cell on the worksheet.
ENTER	Completes a cell entry and selects the cell below.
ALT+ENTER	Starts a new line in the same cell.
SHIFT+ENTER	Completes a cell entry and selects the cell above.
ESC	Cancels an entry in the cell.
HOME	Moves to the beginning of a row.
CTRL+HOME	Moves to the beginning of a worksheet.
CTRL+SHIFT+HOME	Selection of cells to the beginning of the worksheet.
PAGE DOWN	Moves one screen down in a worksheet.
ALT+PAGE DOWN	Moves one screen to the right in a worksheet.
CTRL+PAGE DOWN	Moves to the next sheet in a workbook.
CTRL+SHIFT+PAGE DOWN	Selects the current and next sheet in a workbook.
PAGE UP	Moves one screen up in a worksheet.
ALT+PAGE UP	Moves one screen to the left in a worksheet.
CTRL+PAGE UP	Moves to the previous sheet in a workbook.
CTRL+SHIFT+PAGE UP	Selects the current and previous sheet in a workbook.
SPACEBAR	One space, selects or clears a check box.
CTRL+SPACEBAR	Selects an entire column in a worksheet.
SHIFT+SPACEBAR	Selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR	Selects the entire worksheet.
ALT+SPACEBAR	Displays the Control menu for the Microsoft Office Excel window.
TAB	Moves one cell to the right in a worksheet.
SHIFT+TAB	Moves to the previous cell in a worksheet
CTRL+TAB	Switches to the next tab in dialog box.
CTRL+SHIFT+TAB	Switches to the previous tab in a dialog box.

Excel control combination shortcut keys

CTRL Key Combination	Action/Description
CTRL+A	Select all.
CTRL+B	Apply bold/removes bold formatting.
CTRL+C	Copy the selected cells.
CTRL+D	Fill Down contents and format of the topmost cell.
CTRL+F	Displays the Find and Replace dialog box and Find tab selected.
CTRL+G	Displays the Go To dialog box.
CTRL+H	Displays the Find and Replace dialog box and the Replace tab selected.
CTRL+I	Apply/removes italic formatting to the selected text.
CTRL+K	Displays the Insert Hyperlink dialog box.
CTRL+L	Displays the Create Table dialog box for selected cell/cells.
CTRL+N	Creates a new, blank workbook.
CTRL+O	Displays the Open dialog box.
CTRL+P	Displays the Print tab.
CTRL+R	Fill Right the contents and format of the leftmost cell.
CTRL+S	Saves the active file.
CTRL+T	Displays the Create Table dialog box.
CTRL+U	Apply/removes underlining.
CTRL+V	Paste the contents of the Clipboard at the selected cell.
CTRL+W	Closes the selected workbook window.
CTRL+X	Cuts the selected cells.
CTRL+Y	Repeats the last command or action.
CTRL+Z	Undo to reverse the last action.
CTRL+1	Displays the Format Cells dialog box.
CTRL+2	Apply bold/removes bold formatting.
CTRL+3	Apply/removes italic formatting to the selected text.
CTRL+4	Apply/removes underlining.
CTRL+5	Apply/removes strikethrough.
CTRL+6	Alternates between hiding and displaying objects.
CTRL+8	Displays or hides the outline symbols.
CTRL+9	Hides the selected rows.
CTRL+0	Hides the selected columns.
CTRL+PgUp	Switches between worksheet tabs from left-to-right.
CTRL+PgDn	Switches between worksheet tabs from right-to-left.
CTRL+SHIFT+(	Unhides any hidden rows within the selection.
CTRL+SHIFT+&	Applies the outline border to the selected cells.
CTRL+SHIFT_	Removes the outline border from the selected cells.
CTRL+SHIFT+~	Apply the General number format.
CTRL+SHIFT+$	Apply the Currency format with two decimal places.
CTRL+SHIFT+%	Apply the Percentage format with no decimal places.
CTRL+SHIFT+^	Apply the Scientific number format with two decimal places.
CTRL+SHIFT+#	Apply the Date format.
CTRL+SHIFT+@	Apply the Time format.
CTRL+SHIFT+!	Apply the Number format with two decimal places.
CTRL+SHIFT+*	Selects the current region around the active cell.
CTRL+SHIFT+:	Enters the current time.
CTRL+SHIFT+"	Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL+SHIFT+Plus (+)	Displays the Insert dialog box.
CTRL+Minus (-)	Displays the Delete dialog box.
CTRL+;	Enters the current date.
CTRL+`	Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+'	Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+F1	Displays/hides the Microsoft Office Excel Ribbon.
CTRL+F2	Displays the Print Preview window.
CTRL+F4	Close the selected workbook window.
CTRL+F5	Restore the window size of the selected workbook window.
CTRL+F6	switches to the next workbook window if multiple workbook window is open
CTRL+F7	Performs the Move command on the workbook window when it is minimized.
CTRL+F8	Performs the Size command on the workbook window when it is minimized.
CTRL+ALT+F9	Calculates all worksheets in all open workbooks
CTRL+F9	Minimize a workbook window to an icon
CTRL+F10	Maximize/restore the selected workbook window.

Excel Function keys shortcuts

Function keys	Action/Description
F1	Displays Microsoft Office Excel Help.
F2	Edits the active cell and positions the insertion point at the end.
F3	Displays the Paste Name dialog box.
F4	Repeats the last command or action.
F5	Displays the Go To dialog box.
F6	Switches between the worksheet, Ribbon, task pane, and Zoom controls.
F7	Displays the Spelling dialog box.
F8	Turns extend mode on or off.
F9	Calculates all worksheets in all open workbooks.
F10	Turns key tips on or off.
F11	Creates a chart of the data in the current range.
F12	Displays the Save As dialog box.

Define Statistics

Statistics is defined as study of the collection, analysis, interpretation, presentation, and organization of data. Statistics have so many definitions when applying it to a scientific, industrial, or social problem.

Some are the popular definitions:

“Classified facts representing the conditions of a people in a state, especially the facts that can be stated in numbers or any other tabular or classified arrangement” by Merriam-Webster dictionary

“Numerical statements of facts in any department of inquiry placed in relation to each other” as per Statistician Sir Arthur Lyon Bowley.

Statistics as branch of mathematics pertains to the collection, analysis, interpretation and presentation of data. Some scientist considers statistics to be a distinct mathematical science rather than a branch of mathematics. And some considers statistics is concerned with the use of data. Mathematical statistics is the application mathematics concept in statistics. Use of Mathematical techniques to collection and analysis of data includes mathematical analysis, linear algebra and differential equations.

Data Analysis with excel

This article will explain you how excel features are used to data analysis process:

1. Sort: You can apply sort to one or multiple cells to get expected data. You may sort data A-to-Z or Z-to-A manner. This is very simple and easy method for data analysis. Custom sort have advance features like sort by cell color, font color and cell icon.

2. Conditional Formatting: Conditional formatting is very rich functionality for data analysis in Excel that enables user to highlight cells with different colors, depending on the cell's value/percent.  By applying conditional formatting to your data, you can quickly identify variances in a range of values with a quick glance. Following example will give quick answer about high temperature value.

3. Filter:  Filter your data in excel by certain criteria to get expected output. Filtered data displays only the rows that meet criteria that you specify and hides rows that you don't want displayed. You can apply filter to multiple columns at a time. After you filter data, you can copy, find, edit, format, chart, and print the subset of filtered data without rearranging or moving it.

4. Charts: A picture is equal to thousand words so generating chart with a sheet full of numbers is easy to understand. This is very useful and easy tools for data analysis and to create visualizations. There are so many different charts we can create for data analysis.
5. Tables: Table will allow you to analyse data easily and quickly.
6. Pivot Tables: Pivot Table is very powerful and easy to use data analysis tool in excel. You can extract information from rows and columns by inserting pivot on that data.  
7. Analysis ToolPak: This is an excel add-in program used to data analysis for statistical and engineering data analysis.
8. Solver: Excel is having one more tool which is called solver. This uses techniques from the operations research to find optimal solutions for all kind of decision problems.

What is Data Analysis

It is the process of inspecting, cleaning, transforming, and modelling raw data with the intention of discovering useful information. That information is further used to suggesting conclusions, and for decision-making.