Question about Microsoft Office Standard for PC

1 Answer

To display only the values in the data occupied cells

I am working with excel pivot table.while i m doing some manipulations i m considering the empty rows and columns such cases it is giving like #DIV.the question is i dont like to display the value in the empty would u please give me the solution for this?

Posted by on

1 Answer

  • Level 2:

    An expert who has achieved level 2 by getting 100 points


    An expert that got 5 achievements.


    An expert who has written 3 tips or uploaded 2 video tips.


    An expert whose answer got voted for 20 times.

  • Expert
  • 112 Answers
Re: To display only the values in the data occupied cells

Try this. say you want to divide column 'B' By Column 'C': =if(type(B1/C1)=16,0,B1/C1). Explanation: Type is function that returns Error Message. In the case of #DIV it will give back 16. The IF function mentioned above will show '0' if there is error and the actual calculation otherwise. Let me know if you have any further questions and please don't forget to rate. Thanks.

Posted on Sep 12, 2007

Add Your Answer

0 characters

Uploading: 0%


Complete. Click "Add" to insert your video. Add


3 Points

Related Questions:

1 Answer

Sum numbers in a column down until you come to a blank, then start again

Sounds like your issue can be solved readily with a pivot table.
If you give the columns of names a title like "NAME" and the number columns a name like "VALUE". All other columns should contain a unique name.

Then select the entire table, including the labels at the top ensuring that the range extends entirely over the column you need to sum the values of.

In Excel 2013, choose the INSERT menu and select the PIVOT TABLE command.
Insert the Pivot table into a new worksheet.
A new sheet will open with a strange-looking control panel on the right of the window.
Make sure that the box for NAME and VALUE (only) are checked

You will notice that Excel assumes that you want the SUM of the values for each NAME summed. The results are in the leftmost area of the worksheet.

If this works for you, please vote my answer as "helpful".

Sep 03, 2014 | Microsoft Excel for PC

2 Answers

What is excel


Microsoft Excel is a commercial spreadsheet application . It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.
Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. In addition, it can display data as line graphs, histograms and charts, and with a very limited three-dimensional graphical display.


Jun 04, 2011 | Microsoft Excel for PC

1 Answer

I built a spreadsheet that uses a lot of DSUM equations to analyze a database of transaction information. I'm noticing that it is taking a very long time for the spreadsheet to calculate, even after...

The DSUM function is very useful, but it does use a lot of processing. Every DSUM function does a scan of every row of your table. There are a few ways you can reduce computing time.
First (and the most obvious) is to reduce the number of DSUM functions or reduce the size of your table. I presume you have already tried this.
Secondly consider using Pivot Tables to do the task, or to reduce the size of your table. There is a Pivot Table wizard under the Data menu. It's a fairly user friendly feature of Excel, so I suggest you try it out on your table. Pivot tables will be many times faster than DSUM functions because they only scan the table once. There are some tutorials available on the internet.
Third, if your DSUM functions are only summing one value in the table, then it would be much quicker if you can sort the table on the lookup value (or criteria). Then use LOOKUP, VLOOKUP or MATCH functions to find the value you're looking for. On a sorted table, these functions are many times faster that DSUM functions.
I hope this helps a little. It's hard to diagnose without seeing the spreadsheet and knowing the details of the problem you are trying to solve.

Oct 23, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

Procedure how to establish aging of account receivable in excel

Once your data has been input into Excel - I assume you can do this via importing an outstanding debtors deport.

The easiest way I can see is by Pivot Table.

Your data should include open invoices and their respective due dates, it will be easier if you could covert these due dates into months - Create a pivot entering customer, outstanding values as your data and due months as your range.

Feb 22, 2009 | Microsoft Business & Productivity Software

1 Answer


lookup value = value searched

table array = database

topmost row of lookup array must contain the data IDs and all IDs must be sorted in ascending order.

row index number = row number containing data to be shown; first row = 1

hlookup(x,tablearray,y) will look for x on the first row of the lookup table and return the value in the cell on the yth row

if formula cannot file exact x, it will look for the value closest to. but not greater than x

Nov 13, 2008 | Business & Productivity Software

3 Answers

Excel formula help

formula for the keys or short curt for excel

Jul 15, 2008 | Microsoft Excel for PC

2 Answers

Min and Max Date range filter

Check out: Tools -> Data -> Filter -> Auto Filter.
You may want to look at the help function to get a better idea of how powerful this feature is.

Let me know if this helps.

May 08, 2008 | Microsoft Excel for PC

1 Answer

Data for excel chart

You need to create subtotals so that it looks like something like this:

Prod/units Amt. Level Total

xxxx 10.00 1
yyyy 20.00 1

dddd 40.00 2
ffff 30.00 2


To do this I would suggest using the Pivot Table feature of Excel. It is not too hard to use but you do have to know what you want and also have to be sure you are getting results as expected. Like any tool, it has to be used right. You can also use sub-total functions supplied in VBA as well in your cells.

If you want to avoid using VBA functions in cell and deal with ranges, etc., use the Pivot Table Wizard.

Some reading:

Also Google "Excel pivot table wizard" to get more.

Good luck!

Feb 23, 2008 | Microsoft Office Standard for PC

1 Answer

Excel Pivot view

Highlight the entire pivot table and name it as a range. Then in the formula, replace the reference to the table addresses with the range name. Then wherever you move the table (move, not cut and paste) it will automatically gather the right data. Thanks for rating FixYa!

Oct 19, 2007 | Microsoft Office Standard for PC

1 Answer


Check this out - Basically if you have the data sorted this way: Category Purchases A 12 B 13 A 15 Just select the area go to data -> pivot drag the Category to the Left Row and then the Pirchases to the inside of the table and you have your first pivot table. Let me know if you have any further questions and please don't forget to rate.

Aug 27, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Office Standard for PC Logo

Related Topics:

50 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers


Level 3 Expert

18257 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides