Learn Business Analytics Training with Free Lessons & Tips

Filtering data in MS Excel refers to displaying only the rows that meet certain conditions (The other rows gets hidden).

Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set filter to do this. Follow the below mentioned steps to do this.

• Place a cursor on the Header Row.

• Choose Data Tab » Filter to set filter.

• Click the drop-down arrow in the Area Row Header and remove the check mark from Select All, which unselects everything.

• Then select the check mark for Size 36 which will filter the data and displays data of Shoe Size 36.

• Some of the row numbers are missing; these rows contain the filtered (hidden) data.

• There is drop-down arrow in the Area column now shows a different graphic - an icon that indicates the column is filtered.

Using Multiple Filters:

You can filter the records by multiple conditions i.e. by multiple column values. Suppose after size 36 is filtered, you need to have the filter where color is equal to Coffee. After setting filter for Shoe Size, choose Color column and then set filter for color.

Sorting data in MS Excel rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort data by Amount from smallest to largest or largest to smallest.

To Sort the data follow the steps mentioned below:

• Select the Column by which you want to sort data.
• Choose Data Tab » Sort Below dialog appears.

• If you want to sort data based on a selected column, Choose Continue with the selection or if you want sorting based on other columns, choose Expand Selection.

• You can Sort based on the below Conditions.

  • Values: Alphabetically or numerically.

  • Cell Color: Based on Color of Cell.

  • Font Color: Based on Font color.

  • Cell Icon: Based on Cell Icon.

Sorting option is also available from the Home Tab. Choose Home Tab » Sort & Filter. You can see the same dialog to sort records.

A cell is a single element in a worksheet that can hold a value, some text, or a formula. A cell is identified by its address, which consists of its column letter and row number. For example, cell B1 is the cell in the second column and the first row.

A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon.

Example of Ranges:

• C24: A range that consists of a single cell.

• A1:B1: Two cells that occupy one row and two columns.

• A1:A100: 100 cells in column A.

• A1:D4: 16 cells (four rows by four columns).

Selecting Ranges:

You can select a range in several ways:

• Press the left mouse button and drag, highlighting the range. Then release the mouse button. If you drag to the end of the screen, the worksheet will scroll.

• Press the Shift key while you use the navigation keys to select a range.

• Press F8 and then move the cell pointer with the navigation keys to highlight the range. Press F8 again to return the navigation keys to normal movement.

• Type the cell or range address into the Name box and press Enter. Excel selects the cell or range that you specified.

Selecting Complete Rows and Columns:

When you need to select an entire row or column. You can select entire rows and columns in much the same manner as you select ranges:

• Click the row or column border to select a single row or column.

• To select multiple adjacent rows or columns, click a row or column border and drag to highlight additional rows or columns.

• To select multiple (nonadjacent) rows or columns, press Ctrl while you click the row or column borders that you want.

With MS Excel 2010 Named styles make it very easy to apply a set of predefined formatting options to a cell or range. It saves time as well as make sure that look of the cells are consistent.

A Style can consist of settings for up to six different attributes:

• Number format,

• Font (type, size, and color),

• Alignment (vertical and horizontal),

• Borders,

• Pattern,

• Protection (locked and hidden).

Now, let us see how styles are helpful. Suppose that you apply a particular style to some twenty cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 12 pt. rather than 14 pt. Rather than changing each cell, simply edit the style. All cells with that particular style change automatically.

Applying Styles:

Choose Home » Styles » Cell Styles. Note that this display is a live preview, that is, as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection.

Creating Custom Style in MS Excel:

We can create new custom style in Excel 2010. To create a new style, follow these steps:

• Select a cell and click on Cell styles from Home Tab.

• Click on New Cell Style and give style name.

• Click on Format to apply formatting to the cell.

After applying formatting click on OK. This will add new style in the styles. You can view it on Home » Styles.

Graphic Objects in MS Excel:

MS Excel supports various types of graphic objects like Shapes gallery, SmartArt, Text Box, and WordArt available on the Insert tab of the Ribbon.Graphics are available in the Insert Tab. See the screenshots below for various available graphics in MS Excel 2010.

Insert Shape:

• Choose Insert Tab » Shapes dropdown.

• Select the shape you want to insert. Click on shape to insert it.

• To edit the inserted shape just drag the shape with the mouse. Shape will adjust the shape.

Insert Smart Art:

• Choose Insert Tab » SmartArt.

• Clicking SmartArt will open the SmartArt dialogue as shown below in the screen-shot. Choose from the list of available smartArts.

• Click on SmartArt to Insert it in the worksheet.

• Edit the SmartArt as per your need.

Insert Clip Art:

• Choose Insert Tab » Clip Art.

• Clicking Clip Art will open the search box as shown in the below screen-shot. Choose from the list of available Clip Arts.

• Click on Clip Art to Insert it in the worksheet.

Insert Word Art:

• Choose Insert Tab » WordArt.

• Select the style of WordArt, which you like and click it to enter a text in it.

Before you begin consolidating the data, make sure that there is consistency across the data sources. This means that the data is arranged as follows:

• Each range of data is on a separate worksheet.

• Each range of data is in list format, with labels in the first row.

• Additionally, you can have labels for the categories, if applicable, in the first column.

• All the ranges of data have the same layout.

• All the ranges of data contain similar facts.

• There are no blank rows or columns within each range.

In case the data sources are external, ensure usage of a predefined layout in the form of an Excel template.

Suppose you have the sales data of various commodities from each of the regions East, North, South, and West. You might need to consolidate this data and present a product wise summary of sales from time to time. Preparation includes the following:

• One worksheet per region – i.e. four worksheets with names East, North, South, and West. These could be in the same workbook or different workbooks.

• Each worksheet has same layout, representing the details of product, number of units, and amount.

• You need to consolidate the data product wise. Hence, ensure that the column with the label Product is the first column and it contains the Product labels.

MS Excel data validation feature allows you to set up certain rules that dictate what can be entered into a cell. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and 10. If the user makes an invalid entry, you can display a custom message.

Validation Criteria To specify the type of data allowable in a cell or range, follow the steps below while you refer to which shows all three tabs of the Data Validation dialog box. Select the cell or range.

Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box having 3 tabs settings, Input Message and Error alert. Settings tab Here you can set the type of validation you need.Choose an option from the Allow drop-down list.The contents of the Data Validation dialog box will change, displaying controls based on your choice.

Any Value: Selecting this option removes any existing data validation.

Whole Number: The user must enter a whole number.For example, you can specify that the entry must be a whole number greater than or equal to 50.

Decimal: The user must enter a number. For example, you can specify that the entry must be greater than or equal to 10 and less than or equal to 20.

List: The user must choose from a list of entries you provide.You will create drop-down list with this validation. You have to give input ranges then those values will appear in the dropdown.

Date: The user must enter a date. You specify a valid date range from choices in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2013, and less than or equal to December 31, 2013.

Time: The user must enter a time. You specify a valid time range from choices in the Data drop-down list. For example, you can specify that the entered data must be later than 12:00 p.m.

Text Length: The length of the data numberofcharacters is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 a single alphanumeric character.

Custom: To use this option, you must supply a logical formula that determines the validity of the user’s entry alogicalformulareturnseitherTRUEorFALSE.

Input Message tab:

You can set the input help message with this tab. Fill the title and Input message of the Input message tab and the input message will appear when cell is selected..

Error Alert Tab:

You can specify error message with this tab. Fill the title and error message. Select the style of the error as stop, warning or Information as per you need.

8 Steps To Learn Data Science

There have been a lot of surveys over the past few years on the educational background of data scientists. As a result, there have also been many different results. In the O’Reilly Data Science Salary Survey of 2014, about 28% of the respondents had a Bachelor’s degree, while 44% had a Master’s degree and 20% had a Ph.D. Common fields that data scientists have as backgrounds are mathematics/Statistics, Computer Sciences, and Engineering. 

In general, you could conclude that the degree that you need to have completed to become a data scientist is usually a Master’s degree or Ph.D. The field that you come from is of less importance, but you have an advantage if you have a quantitative background.

Step 1. Get Good at Stats, Maths and Machine Learning:

The perspective on the definition of data science might have changed over the years, but data science has remained a somewhat technical occupation. A sound knowledge of statistics, mathematics, and machine learning are still considered a main requirement for anyone to do data science.

Getting up to speed with these three can be a pain, especially for those who have no technical background whatsoever. Luckily, you have more than enough qualitative resources to help you out on this: Khan Academy offers online courses on a variety of mathematics topics that will undoubtedly be of great value to you, but make sure to also take a look at the Linear Algebra course from MIT Open Courseware. For statistics, DataCamp, Udacity and OpenIntro’s material might help you, and for Machine Learning, you should keep an eye out for the content on DataCamp, Stanford Online and Coursera.

Step 2. Learn to Code:

Developing your hacking skills is also one of the things that you need to take into account still if you want to learn data science.

You can start by getting familiar with the computer science fundamentals: get to know the basic data structures and search algorithms. Then, step up to understanding how end-to-end development works: the stuff you will work on will be integrated with other systems, so it’s best to understand how development from beginning to end, from the requirements gathering and analysis to testing and maintaining code. When you have grasped this concept, it’s time to pick a language. You can go for an open source language or a commercial one. Things to take account in your decision are the learning curve, the industry you want to work in, the salary that comes with being proficient in the language.

Step 3. Understand Databases:

When you start out learning data science, you see that a lot of tutorials focus on you retrieving data from flat files. However, when you start working or when you get in touch with the industry itself, you see that most of the work happens through a connection with one or multiple databases.

And there are a lot of databases out there. Companies might work with commercial ones like Oracle or they might opt for open-source alternatives. The key to seeing the forest for the trees here is to understand how databases work. Learn about the why and how of databases and the what will come. Concepts that you should grasp and know your way around in are the Relational Database Management Systems (RDBMS) and data warehousing. That means that relational versus dimensional modeling should not hold any secrets for you, nor should SQL or the Extract-Transform-Load process (ETL) surprise you.

Step 4. Explore The Data Science Workflow:

A next phase in the learning process would be to explore the data science workflow. A lot of tutorials or courses focus on only one or two aspects of it, but lose the general overview of the process that you will need to go through once you’re working as a data scientist or in a data science team. It’s essential not to lose sight of the iterative process that data science is.

For data science beginners that know how to program, the easiest way to discover how the data science workflow works is by practicing your coding skills: get started on your journey with R or Python. There are several in-built packages and libraries in both R and Python that will make your coding life easier. 

Step 5. Gain Understanding of Big Data:

Big data might have been a hype, but it’s definitely out there, and it’s important to realize this and understand what it encompasses. Three things to learn about big data are:

1. See why big data requires a different approach of data processing. The best approach to do this is probably by looking at big data use cases. You can read up on some here.

2. Get familiar with the Hadoop framework: it’s widely used for distributed data storage and processing.

3. Don’t forget about Spark. Getting the hang out of Spark in combination Scala is the way to go. And, even better, you kill two birds with one stone: you practice your coding skills and widen your view on data science.

Step 6. Grow, Connect and Learn:

Grow: Once you have gotten to this point where you already master the fundamentals, it’s time to grow: practice as much as you can by doing data science challenges, like the ones you find on Kaggleor DrivenData. They will definitely challenge you to put the theory into practice. Also, you should also let your intuition grow.

Connect: As a data science learner, you might fall into the pitfall of staying occupied with your learning and that of other learners, but it is equally important to connect to those who already have some more experience in the field. This way, you build up a network to fall back on in case you have questions, need advice or tips, or whatever. These people will motivate you to keep up the good learning and will challenge you to go even further.

Learn: Continuous learning and data science could be synonyms. The Kaggle and DrivenDatachallenges that have been mentioned above will teach you a thing or two about how data science is done in practice. Apart from these relatively small exercises, you might consider starting up a pet project and explore some things even on a deeper level.

 Step 7. Immerse Yourself Completely:

Just like a language bath, you’re in need of a data science bath. Depending on your skills and knowledge that you already have, you might consider a bootcamp, an internship or a job. A bootcamp is an amazing way of kickstarting or boosting your data science learning. As a plus, you meet a lot of people, and you have an opportunity to build or extend your network. Are you having trouble finding one? Check out Galvanize and Metis, but also don’t forget that your Meetup Groups might also organize bootcamps and workshops for the community!

Secondly, when you have already got the basics of data science under control, you should consider getting an internship. A lot of the big companies like Facebook, Quora and Amazon have looked for interns before, so this is a great place to start your search. Also, you can use your social channels or your network to get first-hand information on open positions for internships. Lastly, also take a look at startups: these smaller companies can be willing to let you learn on the job as long as you learn quickly. AngelList is worth checking out for startup jobs.

Step 8. Engage with The Community:

This last step is one that can be overlooked sometimes. Even when you have a job in data science or as a data scientist, you still need to remember that data science equals continuous learning. There are new advancements all the time, and it’s of key importance to stay informed and curious about what’s happening around you. So don’t hold back to contribute to discussions on social media, subscribe to a newsletter, follow the key people of the data science industry, listen to a podcast. Whatever you can do to engage with the community!

To stay up to date with the latest news, you can register to the following newsletters: the bimonthly KD Nuggets newsletter and Data Elixir or the Data Science Weekly newsletters. Next, follow some of the key people in the data science industry on Twitter. This will also keep you up to speed with the latest. Just some of the people that might interest you are DJ Patil, Andrew Ng, and Ben Lorica.

Join some communities online. LinkedIn, Facebook, Reddit. They all offer the possibility to connect with peers. You should take on the opportunity to become a member of one of those groups:

• On LinkedIn, make sure to take a look at the “Big Data, Analytics, Business Intelligence”, “Big Data Analytics”, “Data Scientists” or “Data Mining, Statistics, Big Data, Data Visualization, and Data Science” groups.

• At Facebook, the “Beginning Data Science, Analytics, Machine Learning, Data Mining, R, Python”, “Learn Python” groups might interest you.

• Subreddits that you can keep an eye on are “/r/datascience”, “/r/rstats” and “/r/python”, among many others!