Table In Excel Posts

Table In Excel

Premium Links

Popular Questions

How do I create a table in Excel that summarizes key data without entering each cell address individually? I want to create a table in excel that summarizes only my totals, which occur every 21 rows. In other words, the first data row would have cells with data from b21, c21, d21 and so on. The second row would have data from b42, c42, d42, and so on. I could of course enter each cell manually, but it would be a lot faster if I could write a formula so I would only have to enter and then copy the first row. I meant, copy the second row of summarized data, since the first row would typically include only the source cell data. The second row would probably contain the formula that would be copied for the rest of the rows... unless you have a better way. Let me expand my question. I know how to auto sum. I have 50 states. Each state has 20 rows of numbers, with a sum on row 21. The next state's totals are on row 42, row 63, and so on. I want to create a report that shows these totals only on row 1, row 2, row 3 and so on. I can create the report manually, but I was hoping that there was a formula to tell Excel to move down 21 rows for each new row of data in the table. Thanks.

tanner replied: "On your tool bar there is a symbol that looks kinda like a M turned sideways this allows you to auto sum, you go to the cell below the cells you wish to calculate, click this symbol and it hi-lites all cells above the cell you are working in then gives you the sum of these cells. You can do this vertically or horizontal depending on which way you are working your data. another way is to enter all of your data, go to the next cell and enter =sum(hi-light the cells you want the summary to include)and it will automatically calculate hope this helps."

aladou replied: "If you don't want to change the way your main table is structured, this is probably the easiest way to do what you want: Let's say you want your summary data in rows 2 to 51: - first put an index number in column Z, starting with Z2=1, and autofill down to Z51=50 (you can hide this column later if need be) - put this formula in cell B2: =OFFSET('Sheet 1'!B$1, $Z2*21-1, 0) (replace Sheet 1 with whatever your main data sheet is called) - copy and paste this formula to your entire table, across as many columns as needed, and down to row 51 The formula uses row 1 as a starting point, and will pick up everything from row 21 (1+1*21-1), then row 42 (1+2*21-1), and so on. A less "elegant" way to do it would be to put the first summary at row 21 of your summary page, then copy it to rows 42, 63, and so on, which will update the cell references to the corresponding rows in your main table, and then go back through and delete all the blank rows. And as you probably know, there are several ways to summarize a table if you don't mind putting it in the proper format first, such as Pivot tables, Subtotals, etc. If you need more info, add more detail or e-mail. Cheers. _________ EDIT: I'll throw out one other idea... If your state subtotals have something like "Alaska Total" in column A, you can use VLOOKUP to look up the totals: If A2 in your summary has "Alaska", this VLOOKUP will get Alaska's subtotals: =VLOOKUP(A2&" Total",'Sheet 1'!$A$1:$Z$1100, 2, FALSE) - the ,2, means it will get column B data, and if you change it to 3, 4, etc., it will get C, D, etc. ."

How do i create a table in excel so I dont have to type name of a company over and over? How do i create a table in excel so I dont have to type name of a company over and over?

Shibi replied: "Is the name to be in the same column? Excel will Auto-fill that data for you if so. Or, you could copy and paste the 'data' from cell to cell. I'm not sure that you really need a full table to do this... I guess it depends upon what you want to do with the name of the company (IE: mailing macro for Word? Address labels? Filing labels? etc.). More info needed to help you better, I guess."

aguadiablo10 replied: "If you are using the Microsoft Office Excel spreadsheet package, there are two ways. Firstly if the name is in the same column, then as you retype the company's name, the name should reappear and just press enter on the keyboard. Secondly you can copy and past it into different cells, the square boxes. Either by selecting and pressing Ctrl and c then Ctrl and v on the keyboard. Or on the menu on top."

doug42217 replied: "If the company name is going to be in different cells throughout the workbook, then you can use the Auto-Correct feature. Click on Tools>Auto Correct For example, you can set it up so that every time you type "MLB", MS Excel would automatically change that to "Major League Baseball" Be careful with that though. Make sure you dont use a common text string or a common acronym as your shortcut."

How do I use a command button to interact with a Pivot table in Excel? I am using Excel 2003. I have a pivot table that is Page Area filter on Date. I want to create a button that will set the date to the current date. How do I script the VB to change the Pivot table? Thanks!! Ben Recording a Macro will not work as you can't type a date into PivotTable, it must be selected from the drop down (as far as I know).

Michael E replied: "I would start by recording a Macro of you changing the date. Then edit the macro replacing the manualy entered date with Date(). Then assign the macro to a command button. If you need more help has a free message board that is better suited (formatting, message board style, etc.) to Excel help than this one."

cozmosis replied: "Sub PivotToday() ' ' PivotToday Macro ' Macro recorded 12/9/2008 ' On Error GoTo Err ActiveSheet.PivotTables("PivotTable1") _ .PivotFields("Date").CurrentPage = Date Exit Sub Err: MsgBox ("Today's date is not available in the pivot table") End Sub This macro assumes ... the active sheet has the Pivot Table the Pivot Table is named "PivotTable1" the field in the page area filter is called "Date" FYI: the word "Date" at the end of the line... .PivotFields("Date").CurrentPage = Date ...is a VB function that returns today's date. You could make this any value or make it equal a cell value if you wanted."

How do I set up a table in Excel? I want to set up a data table in Excel on one worksheet with the following headings: Item number, Description, Unit measure, and Price per unit. Then I want to use other worksheets to calculate the inventory value for each location by entering the item number (and having the spreadsheet pull the Description, Unit measure, and Price per unit) and the quantity. I would simply copy the entire worksheet for each location and enter the quantity on hand, but the inventory count sheets do not list the items in the same order for each location and it takes a long time to search for each item even with the item numbers in ascending order.

aladou replied: "If your data is on Sheet1, column A: item number column B: description column C: unit measure column D: price per unit and your quantity is on Sheet2, column A: item number column B: quantity On another sheet you can have VLOOKUP functions that look up each value. For example if you wanted to look up the price from Sheet1 and quantity from Sheet2 and multiply them together: A2 has your item number B2: = VLOOKUP(A2, Sheet1!$a$2:$d$100,4,false) * VLOOKUP(A2, Sheet2!$a$2:$b$100,2,false) Good luck."

How do I make a regular (instead of pivot) graph out of data from a pivot table in Excel in a swift way? Sometimes you have a table in Excel and wants to take a quick look at the shape of the curve that represents this data. Whenever that happens to me, I simply select the range and start the graph wizard, at which I can preview the curve. The trouble is that whenever the data is from a pivot table, Excel assumes I want to create a pivot chart. Is there a way to stop it from doing it, like a hidden option or a hack or something?

buzz_dawson44 replied: "I poked around and could not find a way do disable the pivot charts. All I can suggest is create another range of cells that reference those cells somewhere else on the sheet and make your graph from that data instead of the data in the pivot table."

How do I make this amortization table in excel? I need to make an amortization table, but I cannot figure out how to write the formulas. The loan is for 5000, with an annual interest of 15% for 5 years. Please Help! Thanks!

Dan replied: "here is an example of a simple amortization schedule. I'm assuming that interest in compounded annually, so you would enter 5 as the number of periods in this example. "

How do I create a lookup table in Excel 2003? Basically I have a list of data, and I want the user to be able to search for all data that is above or below a certain value. I think the best way is to create a lookup table, but other suggestions would be appreciated. Ty :)>

Cozmosis replied: "The easiest way to do this is to use AutoFilter -Select all your data -Select from the menu Data\Filter\AutoFilter This will put a dropdown list at the top of all your columns. The user can select "Custom" in any of the drop-downs. A dialog box will popup and they can define criteria ("data that is above or below a certain value") to filter the data. It's not hard to do, but if the user is not to bright, they may not be able to handle it."

How do I create a pivot table in Excel 2003? I know technically how to, go to Data than select Pivot table, ect. But I don't know how to actually get it to do what I want and show things where I need them to be. I have about 50 high schools with subcoloumns on where their graduates went. I want the user to select what high school they want and it'll give them the universities and such the graduates went to. Can anybody offer a clue or tell me of a really good tutorial?

April replied: "Microsoft Office Online offers a series of pivot table tutorials. They are comprehensive, about 40 minutes each, and free. Here's a link:"

I made a table in EXCEL. How can I ... ? I made a table in EXCEL and I have to copy/paste it onto WORD. But, the lines of the table don't show up. How can I make those lines show on Microsoft word? I'm working on a lab report. Thanks.

♣Bobby♣ replied: "you have to add boarders. If you are using 08' then there should be a little box with a line on the bottom under the home tab. If your using the older one you have to select it all then click edit cells"

Why are negatives mucking up my table in excel? I have been using the formula =INT(A1/60)&"hrs"&MOD(A1,60)&"min" as part of a timekeeping table where A1 is the minutes I have gained or lost each week. Why, when A1 is a negative value, does my table not work?

stanleys_2001 replied: "try "mucking" with the EDITING for the cell in question. Change the "format" to one that works."

The Light replied: "try this formula piece it back together it is all one line.... =IF(A1<0,INT(A1/60)+1&"hrs"& MOD(A1,60)-60&"min",INT(A1/60)& "hrs"&MOD(A1,60)&"min")"

Cozmosis replied: "The Excel INT function will always return the integer lower than its fraction, even if the number is negative. In the case of negative numbers, the resulting integer will be the larger one, for a lower value. Don't fall into the trap of thinking Excel will give integers based on an "absolute value" system. The Mod function does something similar but more complicated to explain. Here is an article from Microsoft that descries it The INT and MOD functions operate differently with negative numbers than you expect.. They (in a nutshell) round down. With negative numbers, down is a bigger negative number. Give this formula a try... =IF(A1<0,"-","") & INT(ABS(A1)/60) &" hrs "&MOD(ABS(A1), 60) & " min" The formula calculates the hour and minutes as a positive number and then assigns to the results a negative sign if needed. ."