July 28, 2013

Some Excel Tricks and Tips

Then what is difference between vlookup and match function in excel?



VLOOKUP function search for a specific value in a table array and you can return any value column value in that row (you just need to specify the position of column value to be returned in vlookup function)
whereas
MATCH return the position of key from the table array.

Take a look at some basic function that helps you in excel.

How to find the largest number in excel?
How to find the smallest number in excel?
How to find the fourth largest or fourth smallest number in excel?
How to find the maximum or minimum value of an array in excel?
How to find the most frequently occurring number from an array?
How to find the middle value of the array?
How to find the medium value in the array?
How to find average value of an array based on specific condition?

Basic Formulas in Excel
Excel Basic Formulas 
Thank you all for your valuable supports in office


How to find the sum of values in a table based on certain criteria specified by user.


=SUMIF(A:A,">10") will sum up all the values which is greater than 10 in Ath column of excel sheet.

Syntax

SUMIF(tablerange, criteria, summationcolumn)

SumIF in Excel
Sum of values based on certain criteria 

Thanking you for your supports



How to sum values in excel table based on a given criteria

use =sumif(table_array,criteria,sum_column)

excel summation
Summation of similar data in excel
Application

budget year wise calculation
Budget preparation 






Thanking you for your support


How to create year wise sales graph in microsoft office excel?


Step 1: In insert tab choose the graph you need to draw.
Step 2: Select the graph, and go to design tab and select " select data "
Step 3: A window will be open, where you can see " chart data range " , now choose the data table for graph
Step 4 :Select format / Design tab to change the chart design and settings.

M S Office Excel Graph
Sales Graph in MS Office Excel



Description : The example shows how to draw sales graph in excel (bar graph)

Read Full Tutorial 


Task assigning template in excel

 
TASK ASSIGN EXCEL SPREAD SHEET TEMPLATE
TASK ASSIGN EXCEL TEMPLATES

Download link for excel file
filename :javabelazy100200
password : sachintendulkar
Excel Template direct download here
100+ Downloads in a week.

Data bars in excel

DATA BARS IN MS OFFICE
DATA BARS IN MS OFFICE
Browse to conditional formatting tab in excel tools, then choose data bar in it, change the type to percentage (if you didnt want to show the value in data bar , check show bar only )
author : +belazy 


Drop down list in Excel

DROP DOWN IN EXCEL
Drop Down List in Excel

How to help multiple user to work on same excel sheet simultaneously?


How to consolidate multiple sheets into one single sheet in  MS Office Excel?





How to create a Pivot table in   MS Office Excel?

PIVOT TABLE CREATION IN EXCEL
WORKING WITH PIVOT TABLE


Read Full Tutorial



How to create a column wise report using pivot table in excel sheet?

TAKING REPORT USING PIVOT TABLE

click here


How to validate data date validation in each cell in excel sheet?


Data validation tab in data will restrict end user typing errors. we could eliminate possible type error through this feature, Just set the validation criteria here, you can validate string, date, int , decimal etc here. Input Message : will shows a title message with heading and description. Error alert: will alert end user when the validation failed. Thanks to +msoffice2003 +belazy 


For Excel free templates visit this page  page2

July 10, 2013

to search whether a date is in between two date fields through my sql query

How to search whether a date is in between two date fields through my sql query




see the below table

----------------------------------------------------------
id | start_date    | end_date     | amount | status  | particular
-----------------------------------------------------------
1  | 12-02-2012 |13-07-2013  | 1729    | active  | Zulily
-----------------------------------------------------------
2  | 07-02-2014 |23-02-2014  | 1989   | active  | IOS 7
-----------------------------------------------------------
3  | 07-03-2014 |16-03-2014  | 1987    | active  | Windows 8.1
-----------------------------------------------------------
4  | 12-06-2014 |13-07-2014  | 1987    | active  | Ouya
-----------------------------------------------------------
5  | 12-10-2014 |28-11-2014  |2013    | active  | Comet C/2012
 -----------------------------------------------------------




 SQL query : select * from tbl_interest_rates where now() between start_date and  end_date;
Dated : 10 jully 2013

result  : 1  | 12-02-2012 |13-07-2013  | 1729    | active  | Zulily



This example will find whether the given date is inbetween dates in the given table.

+Shimjith Kumar
+Vipin Cp


http://belazy.blog.com/

July 03, 2013

How to change password of adminstrator from user account in window xp

To change password of administrator from user account in window x p

1) Go to C:/windows/system32.
2) Copy cmd.exe and paste it on
desktop.
3) Rename cmd.exe to sethc.exe.
4) Copy the new sethc.exe to system
32,when windows asks for overwriting
the file,then click yes.
5) Now Log out from your

guest account

and at the user select window,press shift key 5 times.
6) Instead of Sticky Key confirmation dialog,command prompt with

full administrator privileges

will open.
7) Now type “ NET USER ADMINISTRATOR “yournewpassword” and press enter.
You will see “ The Command completed successfully” and then exit the command prompt and login into administrator with your new password.

How to reset administrator password

There is a chance you might have forgot administrator password if you havent touched with your system for a long time, if you are using windows xp try this

open the system in safe mode ( you can do this by press f8 after your restart

go to run (windows + r) then type " control userpasswords2 " , a popup window will open listing all users in your system, select your user and reset password

Thanks for reading ... your name will be saved for our future reference....

Read more: http://javabelazy.blogspot.com/p/tech.html#ixzz2nvgQ0I9a



http://belazy.blog.com/

Facebook comments