Skip to main content


Showing posts with the label Excel

How to get computer name in VBA?

 Computer name shall be in capital letters only. So when you want to use computer name in VBA. follow the method mentioned below  1. Go to my computer 2. Right click 3. Click on properties.  4. You can see the computer name.  5. Copy the name and paste it to word processor 6. Capitalize the name 7. copy it and use in VBA.  Method 2: 1. In VBA type - msgbox  Environ$( "computername" ) 2. Debug by pressing F8 3. when you will cross above mentioned command line, Excel will popup the computer name.  4. use the same name in VBA.  Thats all. 

Amount in words in Excel for Indian Rupees with quick access toolbar button

Writing amount in words is required for cheque preparations and many other things. In the below video we have shown how to make a quick access toolbar for writing amount in words in MS Excel. You can also find function which is prepared to do the same. The Addin file can be purchased at just Rs. 50/-. Follow the procedure to get the Addin file. 1. Paytm us to number +91-9925015825 2. Share the receipt with message "AIWSOE Addin req. 3. We shall share the Addin file to you on whatsapp. 4. Watch the video and follow the procedure shown to add the quick access toolbar. Hope you will like the Addin and the way to save the time.

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 s

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

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.

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 calculator    Converter But, 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

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 "Fo

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

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 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. F

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. 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 do iteration process in excel?

Solver is the option that you need. To install solver add in do the following. Go to Excel options → Add ins → Manage add ins → go → select solver add in and press ok to install it. If you have installed solver, Go to data → in analysis ribbon solver to open the solver function box. Ste target cell is the cell where you want the desired result. By changing cell will be the cell that you want to change to get the desired results. Constraints are the conditions that you want to be followed. Provide the respective cell addresses and press solve to solve the problem. It will show all the options to get the desired result. Enjoy solving.   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 add line break in a cell in excel?

There are two options to add line break. 1.       Wrape text and adjust the width of cell to see the cells in two line. On the Home tab, in the Alignment group, click Wrap Text . 2.       double-click the cell, click the location where you want to break the line in the cell, and then press ALT+ENTER .   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.