Blog Archive

Excel Tip: Search and Highlight Data


Many of us use spreadsheets to manage huge lists of data, like customer data bases, salesperson data bases etc.
Today we will learn a little conditional formatting trick that you can use to search a worksheet full of data and highlight the matching cells.
Search a large spreadsheet of data using conditional formatting
First identify which cell you want to use as search bar. Lets say we choose F4.

Now, Select the data cells you want to search and go to conditional formatting.
Formulas in Conditional FormattingWe will write a simple formula that returns true if a cell has the content you typed in the search bar (F4) and false if the cell doesnt. You can try something like ISERROR(FIND(LOWER($F$4),LOWER(B7)))=FALSE.
But there is a problem with this, it returns true when the search bar is empty, and thus you end up highlighting all cells. So we add a further condition that will highlight the matched cells only if the search bar contains some data.
The formula looks like,
=AND($F$4<>"",ISERROR(FIND(LOWER($F$4),LOWER(B7)))=FALSE)
Finally set the formatting you want to use. I choose dull orange color. You can choose blue, green or pink too.
Hit ok and you are good to go.

What is a Pivot Table - How to Make it

Excel pivot tables are very useful and powerful feature of MS Excel. They can be used to summarize, analyze, explore and present your data.
In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.
In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.
Excel Pivot Tables: Tutorial
click here to see a video tutorial of making pivot tables in excel

Example uses of Pivot Tables

As I said before pivot tables are very powerful and useful. There are numerous uses of pivot tables that we can talk about them until Christmas.
Here are some example uses of pivot tables:
  • Summarizing data like finding the average sales for each region for each product from a product sales data table.
  • Listing unique values in any column of a table [learn more]
  • Creating a pivot report with sub-totals and custom formats
  • Making a dynamic pivot chart
  • Filtering, sorting, drilling-down data in the reports without writing one formula or macro.
  • Transposing data – i.e. moving rows to columns or columns to rows. [learn more]
  • Linking data sources outside excel and be able to make pivot reports out of such data.

Excel Pivot Table Tutorial: How to create your first pivot table

Let us make your first pivot table. We will use example data in the following format. Download the excel pivot tables tutorial workbook with the data.
Pivot Tables in Excel - Tutorial

Step 1:  Select the data
Select the data range from which you want to make the pivot table.

Insert Pivot Table in Excel WorksheetStep 2:  Go to Insert ribbon and click on new Pivot table option
To insert a new pivot table in to your spreadsheet, go to Insert ribbon and click pivot table icon and select pivot table option.

Step 3:  Select the target cell where you want to place the pivot table. For starters, select New worksheet.
Excel will display a pivot table wizard where you can specify the pivot table target location etc. Select "New worksheet" option and your pivot table will be placed in newly created worksheet.

Step 4:  Make your first pivot report
The pivot report UI is very intuitive and sandbox like. To make powerful analysis, all you have to do is drag and drop fields in to the pivot table grid area. In excel 2007, you can also control this by using the "Pivot table panel".
The pivot report is divided in to header and body sections. You can drag and drop the fields you want in each area. The body itself contains three parts. Rows, Columns and Cells. You can use any fields in these areas too.
For the above sample data, I have set this criteria:
Pivot Table Settings - Row, Column, Header and content settings
And the outcome is this pivot report.
Example Pivot Report - Excel Pivot Tables
It might be a bit difficult to understand how this works. But believe me, if you have seen any reports or worked with any other reporting systems, then the idea of pivot tables, pivot reports and pivot charts becomes quite simple to you.
You can use the excel pivot table features to make a more complicated pivot report like this in no time.
Example Pivot Report - A very detailed Pivot Table with sub-totals and totals

Some useful tips on Excel Pivot Tables

  • You can apply any formatting to the pivot tables. MS Excel has some very good pivot table formats (and they are better in Excel 2007 and 2010).
  • You can easily change the pivot table summary formulas. Right click on pivot table and select "summerize data by" option.
  • You can also apply conditional formatting on pivot tables although you may want to be a bit careful as pivot tables scale in size depending on the data.
  • Whenever the original data from which pivot tables are constructed, just right click on the pivot table and select "Refresh Data" option.
  • If you want to drill down on a particular summary value, just double click on it. Excel will create a new sheet with the data corresponding to that pivot report value. (This is extremely useful)
  • Making a pivot chart from a pivot table is very simple. Just click on the pivot chart icon from tool bar or Options ribbon area and follow the wizard.

Download the excel pivot tables tutorial workbook and practice yourself

Click here to download the excel pivot tables tutorial workbook. [.zip version of tutorial here]The workbook has sample data and one pivot table in it. You can play with it to learn more.

Checkout the video tutorial to make excel pivot tables

Click here to see a video tutorial of making pivot tables in excel
http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/

Helpful tips in Applying For Jobs

Most of professionals do not know how to apply for a job even experienced one also here are few tips points you all should remember and apply when you apply for any job.

  1. If you don't meet the criteria DON'T APPLY for that JOB.
  2. If you apply must mention name of the POST Position applied for in SUBJECT of your email e.g NSS Core Network Engineer Huawei & Ericsson Experienced 3 Years experience. This will help the recruiter to sort out your resume easily rather to download and read your resume and then sort you.
  3. While applying for any job must write executive summary of your carrier e.g I have 2.5 years of experience on NSN 1234 BTS 1 Year experience on HUAWEI 456 BTS with your main projects e.g worked for vodafone worked for telenor at least 3-4 lines of your professional experience.
  4. Resumes should be MS-WORD format rather to send PDF file or password protected file because in WORD file each and every department or manager can write comments codes to your file in PDF its impossible.
  5. Apply Once for each position rather to send your resume from all of your email accounts for the same position DOUBLING BAD IMPRESSION.
  6. Don't use logos of multinationals in your resumes. only iff you have the right to do so.
  7. Email address should be professional like ahmed.khan@domain.com rather to have loveboy2008@domain.com heartbreakkid@abc.com
  8. Resume should be up to date please check twice your resume before applying.
  9. Contact numbers should be working and active all time.
  10. Resume name should start with your name e.g ali_NSS.doc khan_BSS.doc asad_RNP.doc rather to have MY-Resume1 CV-1 New-2010 updated_2010. Please AVOID this other wise 99% chances that your resume could go to TRASH.
  11. TRY to save your time and time of the person who is hiring you he/she doesn't have the time to reply you and tell you your mistakes he/she doesn't have the time to rename your resume he/she doesn't have the time to sort your expertise and resume.
  12. Be Honest in interview and tell truth about your PAY and incentives 99.99% you could be caught either for a local job or International MEMBERS OF THIS GROUP TRIED THIS AND FAILED ;-).
  13. NEVER try to reach Hiring Manager or person directly DON'T Call email him unless he/she allows you to do so.

How to cook a delicious dynamic chart



How to cook a delicious dynamic chart that will have your boss drool (chandoo.org)

Link to Chandoo.org - Learn Excel & Charting Online

Posted: 31 Aug 2010 01:43 AM PDT
Dynamic charts are like my favorite food, Mangoes. They tempt, tease and taste awesomely. In this post, we are going to learn how to create a dynamic chart using check boxes and formulas. Are you ready for some excel chart cooking?

What our mouth-watering chart will look like when its done:

Dynamic Chart with Check boxes and formulas - Demo

Ingredients:

Some data, Few check-boxes, IF formula and a dash of espresso

Instructions for preparation:

  1. First get your data. Make sure its clean and arranged neatly, like below, in the range B4:E11.
    Dynamic Chart - source data
  2. Since our data has 3 series (sales, profits and number of customers), we will take 3 check boxes and place them somewhere on our worksheet.
    Insert check boxes from developer ribbon / forms tool bar (tip: show developer ribbon in excel 2007)
    Insert Check box - Excel
  3. Now, we want the check boxes to tell whether to show or hide a particular series of data in the chart. So, link each check box to one cell, say C13, D13 and E13.
    Set linked cell - check box -excel
  4. We will use IF formula to roast our data based on what the check boxes say. So, create a similar table and load it with IF formulas like this:
    =IF(C$13,C4,NA())
    Dynamic chart - processed data based on check box status
  5. Finally, make a chart with the data in this new table you created.
  6. Put everything together and neatly arrange with your favorite colors and labels.
  7. Serve hot and see your boss drool.

Download the prepared chart:

You can download FREE dynamic chart template and serve it instantly.

More recipes on dynamic charts:

Do you use dynamic charts?

I like dynamic charts a lot. They provide a wealth of information in a compact form. I use them whenever possible, especially in dashboards and analytical outputs.
What about you? Do you use dynamic charts often? What techniques do you use when implementing dynamic charts? Share your experience and tips using comments.

Email delivery powered by Google