How to move your cursor around your worksheet quickly?

Trick To Quickly Move Around Your Worksheets, Quickly Move your cursor position by Double-Click.




There is a very quick, easy and useful trick in Microsoft Excel about which most of the excel users don't know. You can move your cursor postion around your MS Excel Worksheet by Double-Click. You can select your data by this trick too.This feature has been available in Excel for many years, but most users have never discovered it.

To quickly jump to the end of a range of cells, double-click the edge of the currently selected range in the direction you want to move. When you double-click an edge of a cell, the active cell will jump in that direction to the last cell until it encounters a blank cell. (This works the same as holding down the CTRL key and pressing one of the arrow keys).

Double Click & Move

How to select data from the cursor position to the end around your worksheet by double click?



A similar trick can be used for selecting a range of cells or extending the current selection. Hold down the SHIFT key and double-click the edge of the currently selected range and the range will be expanded in that direction until it encounters a blank cell.

Double Click & Select

Read More

Count Commas (,) in Excel Worksheet.

How to count comma or commas (,) in your Microsoft Excel Worksheet with Example. Find out the number of commas in excel cells.




At work, Sometimes we need to count the number of commas in a range of selected cells. However, there is no Excel Function to do this type of task, but here we will know a trick to find out the count of Commas (",") in MS Excel.

As there is no worksheet function that will produce the desired count, here we will use a formula or two. To figure out the number of commas in the range of Excel Worksheet when there could be multiple commas per cell, then you need to use the given below formula:

SYNTAX:-


=LEN(text)-LEN(SUBSTITUTE(text, old_text, new_text, [instance_num))

Note:- Here you can leave instance_num blank as it is optional, so that the SUBSTITUTE Function will work from the starting till the End of the Cell or Text.

EXAMPLE:-

Suppose you have some data in A1 to A10 Cells, that data contains multiple commas (,) in each cell itself. Now you need to count the number of commas (,) present in one cell, then use the above formula in Cell B1 as given below and fill till the end of the Data (B10).

Comma Count

 =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))

If you just want to know the number of cells that have at least one comma in them, the following formula will work just fine:

SYNTAX:-

=COUNTIF(range,"*,*")

EXAMPLE:-

=COUNTIF(A2:A11,"*,*")

There is an alternative to find out the count of commas in your whole worksheet then follow the given below steps, but the below method will give you the count of commas of whole excel sheet or selected range in your Excel file.

    1) Select the range of cells in which you want to count commas.

    2) Press Ctrl+H to display the Replace tab of the Find and Replace dialog box.

    3) In the Find What box, enter a comma.

    4) In the Replace With box, enter a comma.

    5) Click Replace All.

Excel does the replacement and displays a dialog box that shows how many replacements were made.

Display Box

Read More

Concatenate Dates with Text in MS Excel With Example.

Concatenate Dates with Text, How to Write Date with Text in Excel Worksheet Cell? Easiest way to concatenate dates with text with example in MS Excel





Last week my friend ask me how to concatenate Date with a String or Text, but that time I couldn't give him the solution because I was on Himachal Trip. So now here we will learn how to Concatenate a text with another formate value like Date, Number, time etc.

If you have a column containing dates in your Excel Worksheet and another column that contain some text. Now you want to concatenate date with text, but if you concatenate the date with text then what will be the format of Result you will get after concatenation.

So to concatenate text with date you need to change the format of your date column. You need to convert your date into text format.

How to convert Date format into Text Format in Microsoft Excel Worksheet, How to use TEXT Function in MS Excel Worksheet or What Excel TEXT Function do?

The Microsoft Excel TEXT function returns a value converted to text with a specified format.
To change or convert Date format into Text format you will need to apply "TEXT" Formula or Function.

What is the SYNTAX of TEXT Function?



Syntax

The syntax for the Microsoft Excel TEXT function is:
TEXT( value, format )

Where
 
value is the value to convert to text.
format is the format to display the results in.

Here is an example to concatenate date and text. In B Column from B3:B10 we have some date values and in C Column from C3:C10 we have some text values. Now to Concatenate both the values B3:B10 and C3:C10 use the given below Formula in cell D3 and Fill down the formula.

Formula:- =CONCATENATE(TEXT(B3,"dd-mm-yyyy"),C3)

Read More

View Two Excel Sheets Simultaneously / together

How to View Two Sheets of a Workbook Simultaneously / together in MS Excel.





Sometimes we need to see more than one sheets of a Excel Workbook together / simultaneously, so if there is any requirement like this how can you do that in Microsoft Excel. Here is a very simple and easy way to do this.

1-New Window

So if you want to view more than one page of a workbook, you can use the "New Window" option. Many Excel users never use this option in their whole life. In Excel 2007/2010 "New Window" option is located in the 'View' menu or in Excel 2003 located in the 'Window' drop-down menu. This "New Window" option opens up a second window for the current workbook, so that you can view different worksheets of the same workbook in one Excel viewer.

Arrange Window

You can arrange your windows as you want like Tiled, Horizontal, Vertical, Cascade. To arrange Excel sheet windows use Excel's automatic 'Arrange' command which is also located in the 'View' menu of Excel 2007/2010 or the 'Window' drop-down menu of Excel 2003. Alternatively, you could size the windows manually in the Excel viewer.

It is much more convenient having two windows open for one Excel file than having to repeatedly switch from one worksheet to the other!

Read More

PSTET 2014 Online Application Form / PSTET 2014 Jobs

PSTET 2014 Online Application Form at www.tetpunjab.com / Punjab State TET Notification.
PSTET Jobs Application form.

PSTET Recruitment

PSTET (Punjab State Teacher Eligiblity Test) 2014 –  Punjab State TET Update | PSTET Online Application Form / Apply for PSTET through online on PTSTET official website portal @ www.tetpunjab.com.

The Department of School Education, Punjab state Government has published a Notification 2014. They are going to conduct the PSTET which is Punjab State Teacher Eligibility Test and now to invite all interested those are eligible, they can fill the application forms for PSTET 2014. For PSTET 2014 all candidates can apply through online application form between 7th July to 18th July, 2014. So there is a golden opportunity for all candidate who want to work as a teacher. PSTET is conducting to select Teachers for Classes I-V (Primary Stage), Classes VI-VIII (Elementary Stage). The Test will be conducted by State Council of Educational Research and Training, Punjab. All information related to PSTET Eligibility criteria such as Education qualification , Age limitation, Application Fee, How to Apply Selection Procedure, and Important Dates all information are given here.

PSTET Eligibility Detail 2014 :-

Name of Test :- PSTET (Punjab State Teacher Eligibility Test)

Posts Type :- Teachers Posts

Location :- Punjab State

Official Website :- www.tetpunjab.com

Eligibility Criteria for PSTET 2014 :-

Educational Qualifications for PSTET:-

    1) For I to V Class Teacher all candidates must have done their 12th class having with diploma in D.ED/B.EL.ED.

    2) For VI to VIII Class Applicants must have done their graduation and also having diploma of D.ED or B.ED

Application fee for PSTET:-

-> SC/ ST/ and other reserved category candidate require to pay Rs.300/- for one paper or for both papers he have to pay Rs.600/- as application fee.
-> General / OBC category candidates need to pay Rs.600/- for one paper or for both papers he have to pay Rs.1200/- as application fee.

Note :- All candidates can pay their application fee by Debit or Credit card & by Challan of Axis Bank.

Selection Process:- Candidates will be selected on the basis of Written test and candidtaes who will qualify, will be called for Personal interview.

How to Apply for PSTET:-

    1) Visit official website or CLICK HERE

    2) Then Click the Apply online Button or CLICK HERE.

    3) After that fill all required detail and submit it,

    4) Take a registration number for future use.


Important Dates to Remind for PSTET 2014:-

    1) Online Application form start from :- 7th July 2014

    2) Last date for Online Applications :- 18th July 2014

    3) Submit Application fee Date:- 19th July 2014

    4) Admit cards will available from :-19th July 2014

    5) Written test will be held on :- 10th August 2014

Click here to visit official website at - www.tetpunjab.com.

Click here for Apply Online

Click here to check official Notification

Read More

Change Default Function COUNT to SUM in Pivot Table in Excel.

Excel Best Trick to Make SUM as Default Function in Pivot Table instead of COUNT, How to Make Pivot Table Default To SUM Not COUNT.

When Pivot Table is used in Excel by default it shows the COUNT of the values. But if you want to SUM the values in your data every time you use Pivot Table then how is it possible, what should we do to make Pivot Table Default to SUM instead of COUNT.

Everyone who has ever used Pivot Tables has encountered a common problem. Excel uses the COUNT function by default in subtotals rather than SUM, when you add certain fields to the Pivot Table.

Pivot Table

So, Here is an easy, quick and best trick to change the function from COUNT to SUM in Pivot Table.
However, there is  a method to do this. You can easily change the function from COUNT to SUM by right-clicking the column in the Pivot Table. But to do this for many column would be a lot of work.

Pivot Table Default settings COUNT or SUM?

Due to the columns in the original data table those contains non-numeric data (either text or blanks), the Excel uses the COUNT function for those columns in a Pivot Table.

There is no way / method to change default setting of Excel for this, however, by ensuring that your original data table does not include text or blanks in numeric columns you can avoid this problem.
But replacing all those blanks cells with zeros is going to be a big chore / problem. Well, it is not like that.





Method to Replace blank cells with zeros in Excel Spreadsheets. Follow the given below method to replace blank cells with zeros.


1) Select all of the (numeric) columns of data in your original source data;

2) Press the F5 key on your keyboard and click the Special... button in the Goto dialog;

3) Select the Blanks option*** and click OK. Only the blank cells are now selected;

Go to Blanks

How to select only blank cells in Excel 2003, 2007, 2010, 2013?

4) Type 0, hold down the CTRL key and press Enter. All blank cells are now filled with zeros. Note that holding down the CTRL key when pressing Enter forces Excel to enter the same value (or formula) in all selected cells;

5) Now go back, right click anywhere in your Pivot Table and select Refresh.

*** If any numeric columns in your source data contain text, repeat the steps above but instead select the Constants and Text options in Step 3.

How to select only text cells in Excel 2003, 2007, 2010, 2013?

Go to Text

Read More

Trick to make Excel Data fit to display

How to Make Your Spreadsheet Fit to the display, Fit Excel Spreadsheet to Screen. An easy trick to make your Excel data fit display / screen.

Many times You put a lot of preparation into designing your spreadsheets the way exactly you want Excel spreadsheets, but you find that your Excel spreadsheet is little wider to fit your display or screen. Now you want to view or find out where the last column or two are just off the right side of your screen or computer display. Maybe you have many spreadsheets like this so Here is the quick and easy solutions to resolve it or lets say to view your entire data within the spreadsheet in a single display/Screen or Make spreadsheet fit to your screen.

If you want to scroll up or down your data is not a big problem, anyone can use mouse scroller to do that but suppose your data is little wider in left or right side, then you can't simply scroll with mouse. So the problem here is that not only do you have to scroll up and down to navigate your spreadsheet but you also have to scroll left and right.
 

Zoom to Selection

Generally people use smaller font size, make columns narrower (Reduce the size/width of columns), or abbreviate data (just to make it all fit within the screen) to display data on the screen when the data is slightly wider than display. So, here below is a quick or simple method to zoom to fit selection / selected data.

Zoom to fit selection / Selected data within excel spreadsheet. 

There is a much easier way / method to make your data fit the width of your screen. 

1) At first, Select a range of cells from the left-most cell to the right-most cell,
 

Zoom To Selection 1

2) Now click the View tab and click the Zoom To Selection command. Excel will change the zoom percentage to fit the selected cells on your screen.

Zoom To Selection 2

Keyboard Shortcut key to Zoom to Selection in MS Excel - ALT+W+G.
 

You can see the zoom percentage for the active workbook in the Zoom Controls on the right side of the Status Bar.

Another method to Zoom to Selection is to click on the percentage value on the Zoom Controls and select Fit Selection from the Zoom dialog and click OK.
 

Zoom To Selection 3

You can use Zoom Controls to squeeze your spreadsheet to fit your screen.

Read More

Recover Your Unsaved Work in MS Excel Spreadsheet

Recover Unsaved Changes in MS Excel Spreadsheet / How To Recover A Excel Workbook You Forget To Save Changes To.

Many times you have accidentally closed a Excel workbook without saving changes to it and after that immediately you realized your error. This may happen after you have been working on a new workbook or after you have made changes to an existing workbook and forgot to save it, then what should you to do to recover your work in Microsoft Excel.
 

How to recover a workbook which you forgot to save changes to it?

 

Auto Recover

Excel versions before 2007 and With Excel 2007, situations like these would leave you completely out of luck. Think, If your computer crashed / Shutdown however, there was a possibility that MS Excel would retrieve the last AutoSaved copy of workbooks / Worksheets that were open at the time of the crash or Shutdown and allow you to recover them from the AutoRecover pane, but this was not very reliable. So, frequently saving multiple versions of your work is probably your best defense.

In Excel 2010, a new feature called 'Manage Versions' was introduced. well. However very few Excel users have even heard about it. You now have the ability to recover unsaved files as well as to open earlier versions (a few minutes ago) of a file you are currently working on it. You have to ensure that your versions feature is enabled.

How to check your versions feature is enabled or How to enable you versions feature enable:- 

 

1)- Click File.

2)- Click on Options.

3)- Go to Save tab.
 

Auto Recover Workbook

Make sure that the 'Save AutoRecover information every ....minutes' and "Keep the last autosaved version...' settings are checked.
If not then tick both the options checked. 

Ensure that your microsoft Excel AutoRecover settings are enabled to ensure you are safe from any problem.

Excel 2010 and later, automatically saves a version of the workbook you are working on every 10 minutes. 

How to open a recently autosaved version of your Excel File? 

To open a recently autosaved version of the file you are currently working on follow the given below steps:-
 

1)- Click File
 

2)- Go to Info Tab
 

3)- Choose one of the versions listed.
 

The workbook using a different name, thus allowing you to work back and forth between two versions of the same workbook.
 

How to open previous version of the current workbook in Microsoft Excel? 

Once you save and close a workbook, all of its old autosaved versions are removed.
Excel deletes all versions of the file except for the most recent autosaved version (i.e. Draft Version), if you close a workbook without saving changes. If you have need, you can also open the previous 'unsaved' version, while reopening the last saved version of a workbook.
 

How to open a previously 'unsaved' version of the current workbook? 

1)- Go to File Menu and click on Info Tab.
 

2)- Click Manage Versions.
 

3)- Recover Unsaved Workbooks.
 

Auto Recover Excel

To see all 'unsaved workbooks', from the File menu select Recent and click Recover Unsaved Workbooks on the bottom right of the screen.
 

How to recover unsaved versions of Excel workbooks? 

Excel saves also a draft version of new workbooks you create and work with but never save. These draft versions are saved for 4 days and after that they are deleted.
 

This is a great feature that can save Excel users a lot of rework if they accidentally close a workbook without saving, but only if they know it exists.

Read More