Skip to main content

Posts

Showing posts with the label MS Office

How to use data validation in MS Excel?

Data validation is a tool that reduces errors, it helps in making cells constraint to some particular values only.

MS Excel says " Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid."

That means, using Data validation, you are controlling the cell values. You can allow people to enter data of their choice, but warn them once when they try to add it in the cell. You can also provide messages to show what kind of input you want for the cell, and instructions to help them correct if any errors comes in.

So, the question is, when is Data validation be helpful?


To give answer to this question, i can say, whenever you want your choice in a cell, data validation will be helpful. When you share your workbook, you want a predefined format to be followed by in order to get the accurate results from the entries. In such a case…

How to use Vlookup in MS Excel?

Hi friends,
Let us learn about Vlookup function of MS Excel. If, you have a big table and you dont want to drag your mouse each and every time you need some value from that table, you can use Lookup or Vlookup or Hlookup.  These are some of the best techniques of data extraction from tables. 
Vlookup is used to extract data from a table, follow these steps to use it.

How to load add-ins and make a customised ribbon button for it?

Hi,

Add-ins are the custom functions/commands/formulae or macros stored in an external file which can be used in any excel file to increase productivity.

In order to use Add-Ins, you should be allowing the add ins. to do that follow this steps.


1. Go to file -- options -- Trust Center -- Open trust center settings.



2. Go to Add-Ins tab -- Un-check all the tick marks of disabling add ins. If you want Add-Ins to be signed by trusted Publisher, check first option ticked.


3. Press OK. 
Now, to add the Add-Ins Follow these steps. 
1. Go to File-- Options -- Add-Ins -- Manage -- click on "Excel Add-Ins" -- press Go. 

2. You will be directed to Add-Ins already present, in order to add new ones, press "browse" button.

3. Excel add in folder opens. Paste your Add-Ins here. and select the one that you want to add followed by OK. you will be redirected back to present add-Ins. press OK again.  4. Add-Ins are added. 
To create a Customised Ribbon button for the Add-In that yo…

How to fill out the cells with the value of cell above in excel?

Sometimes, a quick tip saves so much time that we feel amazed. This is such one tip to save your hours in the future work. Selecting blank cells and adding value to is a repetitive work, and, if we can make this repetitive work a one time work, the time of all the clicks for repetition can be saved. But the question is how to do that? here is a way.
Here, we will be filling blank cells with the value of non blank cell above. That means each blank cell will be filled by the non blank cell value of above cell encountered first.

1. Select the column in which you want to fill the blank cells.  2. go to home -- Find and select -- Go to special. 

3. Select blanks and press OK.

4. press "=" followed by upper arrow to add the formulae that write the value of above cell. 

5. Press "CTRL+Enter" instead of only Enter. 

6. Bingo, you have done it. 

Watch out the video and learn to fill blank cells faster than never before.



Hope you find it useful. Comment if you have doubts o…

How to remove Line breaks (enters) from a paragraph?

Many a times we copy materials from PDFs. Those comes with line breaks, we have to manually remove all enters and join lines. But, no more we do it the same way. Follow the following procedure and removing it becomes a lot simpler.

1. Paste the material to your word file. It looks like this.







Multi purpose Calculator in Excel

Do you know excel can have calculator button which includes:   Standard calculator  Scientific calculator  Programmer calculator  Statistics calculator  Date calculator  Loan calculator  Fuel economy calculator  Vehicle lease calculatorConverterBut, it is not being included in the ribbon. To include the calculator in the quick access toolbar follow the steps as shown.

1. Go to file menu.
2. Click on options à quick access toolbar.
3. In choose Commands from list, select commands not in the ribbon.
4. Select calculator and add.
5. Click OK and you will find the calculator symbol on the top ribbon. 


Happy excelling.

Hope you find it useful. Comment if you have doubts or suggestions. Like, tweet and + post if you find it useful. follow us to get more.

How to solve equations in Excel?

Many a times we encounter problems of solving equations within spreadsheet for engineering calculations. Suppose you want to solve n²-3n+1.5674 = 0, and want to find answer of the same in the middle of your engineering sum. 

There are two options available in excel for the same.
1.You can use goal seek. Follow following procedure for using goal seek.

As shown in picture above you want x value for f(x) being 0. a.Go to Data -> What if analysis -> Goal seek

b.In set cell provide cell reference of F(x) (you want F(x) to be 0, right?) c.In to value type 0. d.In by changing cell provide cell reference of cell of x (you want value of x for which f(x) becomes 0.) e.Press ok and see the result. f.As we know it should give us two options for which f(x) becomes 0. But, goal seek provides least value for which f(x) becomes 0.
2. You can use solver option for the solution. a.Go to data -> Solver (if you can’t find solver in data menu, go to options->add ins->Excel add ins -> solver add…

How to hide error values from a cell in MS Excel?

Let's say your spreadsheet formulas have errors that you anticipate and don't need to correct, but you want to improve the display of your results. There are several ways to hide error values and error indicators in cells.
There are many reasons why formulas can return errors. For example, division by 0 is not allowed, and if you enter the formula =1/0, it returns #DIV/0. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. There are many ways to hide such values. Here are some methods to do the same.
Option 1:  Format text in cells that contain errors so that they don't show. Follow the seteps shown below. 1. Select the cells that contain the error value.
2. On the Format menu, click Conditional Formatting.
3. In the box on the left, click "Formula Is".
4. In the box on the right, type =ISERROR("reference"), where "reference" is a reference to the cell that contains the error value.
5. Click "Format", and th…

how to hide 0 values in a spreadsheet?

option 1: 
Perhaps the simplest way to hide all zero values in a sheet is to let the worksheet do it Just remember, this setting works at the sheet level. You must set it for each sheet in the workbook, as follows.

Click the File menu and then choose Options (under Help). In Excel 2007, click the Office button and then click Excel Options. In Excel 2003, choose Options from the Tools menu.Choose Advanced in the left pane. In Excel 2003, click the View tab.In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.Click OK.
option 2 :
The sheet setting will hide all zero values in the sheet. If you want to hide specific zero values, but not all, you can use a numeric format instead:

Select the cells that contain the zero values that you want to hide. Click the Home tab and click the Number group's dialog launcher (the small arrow in the bottom-right corner. In Excel 2003, choose Cells from the Format menu.Click the Number tab (if necessary…

How to add title on every page in MS excel?

Many a times we have lots of rows of data. When we want to print the same we need titles to be appeared on every page. There is an option for it in excel.
Follow the following procedure:
1. Just go to page layout.
2. Select the option called print title.
3. Select the area you want to see as title on every page.
4. See print preview. You can see the title appeared on every page.
Happy exceling.

How to publish word file as PDF?

To save or export a file to PDF or XPS, you must first install the Publish as PDF or XPS add-in for the 2007 Microsoft Office system.

1. Go to the Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office programs and follow the instructions on that page.

2. After you install the Publish as PDF or XPS add-in, you can export your file to PDF or XPS.

Happy Excelling.

Like us on FB

Hope you find it useful. Comment if you have doubts or suggestions. Like, tweet and + post if you find it useful. follow us to get more.

How to combine the contents of multiple cells into one in Excel?

You can use a formula with the ampersand (&) operator to combine text from multiple cells into one cell.

1. Select the cell in which you want to combine the contents of other cells.
2. To start the formula, type =(
3. Select the first cell that contains the text that you want to combine, type &" "& (with a space between the quotation marks), and then select the next cell that contains the text that you want to combine.
4. To combine the contents of more than two cells, continue selecting cells, making sure to type &" "& between selections. If you don't want to add a space between combined text, type & instead of &" "&. To insert a comma, type &", "& (with a comma followed by a space between the quotation marks).
5. To finalize the formula, type )
6. To see the results, press Enter.

You can use formula called “Concatenate” for doing the same task as shown above.
Fo…

How to repeat an action in Excel?

Many times you want to repeat some actions. You can do repetitive task faster by pressing Ctrl + Y for repeat. Some actions, such as using a function in a cell, cannot be repeated.

For example you have filled a cell with a color. And you want all the cells that need value to of the same color. Then do the following.

1. In first cell fill the color that you want the cell to be filled by.

2. Click on the cells that you want to fill with same color and press Ctrl + Y.

Happy excelling.