Free Microsoft Excel 2013 Quick Reference

Tabular Format

I am new to the forum and hope that there is some help out there. I am in the process (for the first time) importing an EXCEL worksheet into Access. Access says I need to make sure that my worksheet is in Tabular format.

How is this done?

I saw information regard Pivotal Tables, but it is confusing.

Please help,

Chad


Post your answer or comment

comments powered by Disqus
I am in need of converting a "Crosstab" style worksheet into a "tabular" format. Basically, I have values that are spread across columns and I want them down the side with the headers repeating themselves.

My thoughts on the mechanics are it would do the following:
>Insert a column to the left of the data.
>Count the number of populated rows in the data set (this will change)
>Using this count, loop through each column and copy and paste the values down the 1st few rows...all the while populating the former header column down the side.

I am uploading a "before" and "after" look to give an idea of what I need it to do.

Does this make sense? Can anyone help with the vba?

Thanks! This site is the best.

Cheers!

James

Hi,,

I don't know, whether it is possible of not in excel 2007. I even know that there's are some excel expert sitting to help me out.

My problem is somewhat unique.

I have one excel file (attached herewith) which has one row with the name of the picture in one column, state of the picture, i.e. who is doing the modelling, who is doing the rigging and etc etc. and when you move your mouse over the picture, comment box pop up and shows picture. Now I want when i move my mouse over the text rigging or modelling, it should show the name of the artist, when he has started working on it and when he is going to finish it in a tabular format, and that too pop up in comment box or some other tabular format for fraction of seconds as long as your mouse is over the text. when your mouse is over the texturing button, it should display texturing information only and when your mouse is over the rigging, it should show rigging information only.

And is it possible that which ever work is in IP, it should be in Orange color (whiche ver process is in , the text rigging, or modelling or rigging should turn orange)
and which is done to green color and which is not started as RED color.

Please help me out
I am attaching excel file of 2007 version.

Love

Ranbir

Hi,

The below is crosstab report in pivot format. I want to change it to Tabular Report (rows and column). Please let me know how can I do purely in excel and not using VBA.
Current Data :

Segement Power Range Air Filter-Cabin Air Elements Air Metal Housing - Cylindric
Agriculture B. 5 - 75 NA AF25785 AF25785

Construction C. 76 - 175 AF4940 AF876/AF875M AF437KM

Industrial D. 176 - 300 AF25785 FF5259 NA

Required Data :

Segement Power Range Product Family Part No
Agriculture B. 5 - 75 Air Filter-Cabin NA

Construction C. 76 - 175 Air Filter-Cabin AF4940

Industrial D. 176 - 300 Air Filter-Cabin AF25785

Agriculture B. 5 - 75 Air Elements AF25785

Construction C. 76 - 175 Air Elements AF876/AF875M

Industrial D. 176 - 300 Air Elements FF5259

Hi Team,

I want to convert data in matrix format into tabular (database) format. Please suggest technical way to handle it. currently, I have to do copy & paste data to populate data in table (database format). Will be grateful. Thanks & Regards. Nishchint.

INPUT DATA

Article Article Description Site 1 Site 2 Site 3 Site 4 108020365 BRITANNIA BISC 50 50 113G 231 8 5 7 108020368 BRITANNIA BISC 50*50 FP 151g 335 15 20 15 108020392 BRITANNIA BISC DIGESTIVE 100G 167 10 10 5 108020402 BRITANNIA BISC GDAY PISTA 185G 405 23 10 24 108020419 BRITANNIA BISC NICE TIME 160G 545 25 20 36 108020432 BRITANNIA BISC TIGER 61.5G 1269 53 100 70
REQUIRED OUT PUT DATA

SITE NAME Article Article Description TARGET STOCK SITE 1 108020365 BRITANNIA BISC 50 50 113G 231 SITE 1 108020368 BRITANNIA BISC 50*50 FP 151g 335 SITE 1 108020392 BRITANNIA BISC DIGESTIVE 100G 167 SITE 1 108020402 BRITANNIA BISC GDAY PISTA 185G 405 SITE 1 108020419 BRITANNIA BISC NICE TIME 160G 545 SITE 1 108020432 BRITANNIA BISC TIGER 61.5G 1269 SITE 2 108020365 BRITANNIA BISC 50 50 113G 8 SITE 2 108020368 BRITANNIA BISC 50*50 FP 151g 15 SITE 2 108020392 BRITANNIA BISC DIGESTIVE 100G 10 SITE 2 108020402 BRITANNIA BISC GDAY PISTA 185G 23 SITE 2 108020419 BRITANNIA BISC NICE TIME 160G 25 SITE 2 108020432 BRITANNIA BISC TIGER 61.5G 53 SITE 3 108020365 BRITANNIA BISC 50 50 113G 5 SITE 3 108020368 BRITANNIA BISC 50*50 FP 151g 20 SITE 3 108020392 BRITANNIA BISC DIGESTIVE 100G 10 SITE 3 108020402 BRITANNIA BISC GDAY PISTA 185G 10 SITE 3 108020419 BRITANNIA BISC NICE TIME 160G 20 SITE 3 108020432 BRITANNIA BISC TIGER 61.5G 100 SITE 4 108020365 BRITANNIA BISC 50 50 113G 7 SITE 4 108020368 BRITANNIA BISC 50*50 FP 151g 15 SITE 4 108020392 BRITANNIA BISC DIGESTIVE 100G 5 SITE 4 108020402 BRITANNIA BISC GDAY PISTA 185G 24 SITE 4 108020419 BRITANNIA BISC NICE TIME 160G 36 SITE 4 108020432 BRITANNIA BISC TIGER 61.5G 70

Hi

In my daaily work, I usually need to work with huge text files in present in the following format:
-----------------------------
// Start
// Trial1:
//1. Set1
//2. Set2
//3. Set3
// Trial2:
//1. Set1
//2. Set2
// Trial3:
//1. Set1
//2. Set2

// Start
// Trial1:
//1. Set1
//2. Set2
//3. Set3
// Trial2:
//1. Set1
//2. Set2
// Trial3:
//1. Set1
//2. Set2
//3. Set3

----------------------------

There are usually 100s of such start blocks in each text files and "set#" are usually 1 to 15 within a "trial#" block.

The "Set#" are usually string values which are not unique with respect to other Set#s.

Also, each block starts with only "// Start" , and within each Start block, there are there specific "Trial" blocks only as "Trial1", "Trial2" and "Trial3".

Is there a way, to enlist these in blocks in Excel so that:

a) Each start block is present in single row, with each trial block occupying 3 consecutive columns, side by side
b) Next start block start from the next row.

A reference output format in excel is attached.

Please guide ...

I have a bunch of data with my time periods in a row like this:

Sheet1

*CDEFGHI4*Week 1Week 1Week 2Week 2Week 3Week 35*Dollar SalesUnit SalesDollar SalesUnit SalesDollar SalesUnit Sales6Product 11005010050100507Product 21005010050100508Product 31005010050100509Product 410050100501005010Product 5100501005010050

Excel tables to the web >> Excel Jeanie HTML 4

And I would like to have my data in a tabular format with my time periods in a column instead of the row. Besides manually copying and pasting (and besides VBA because I don't know it), is there a way to format like the following table?

Sheet1

*KLMN5**Dollar SalesUnit Sales6Week 1Product 1100507Week 1Product 2100508Week 1Product 3100509Week 1Product 41005010Week 1Product 51005011Week 2Product 11005012Week 2Product 21005013Week 2Product 31005014Week 2Product 41005015Week 2Product 51005016Week 3Product 11005017Week 3Product 21005018Week 3Product 31005019Week 3Product 41005020Week 3Product 510050

Excel tables to the web >> Excel Jeanie HTML 4

I tried Access but I couldn't make it work (admittedly I'm not well-versed in Access).

I can't get a macro to work because it keeps cutting and pasting the same area of the spreadsheet.

Help please! :o

I often download data from various web sites - this is data that is displayed on a web page in tabular format, and often consists of hundreds of lines. I typcally Select All, then Copy, then go to a blank spreadsheet and Paste. After a bit of reformatting, I'm usually in good shape. The problem is, that sometimes the contents of a cell might have been "5-2" (without the quotes), and it comes through on Excel as "May 2". Without proofing each cell, I can't just make the assumption that the original was "5-2" - it might have been "5-2-10" (but not a date).

The normal Paste Special options are not available when I do this, so I can't paste them in as values. I have tried formatting the worksheet at text prior to doing the copy but that does not work.

any suggestions?

Thanks

I'm new to this forum thing so please bare with me. i'm having issues with organizing my data in a pivot table for Excel 2007. When I create a pivot and some of the rows have the same data, it only puts it once at the top and doesn't fill in the rest of the cells (in tabular form). Not sure if I'm explaining this well.
Cell A2, B2, and C2 have data.
Cell A3 is the same as A2 so it leaves it blank if i have something different in B3 and C3. Make sense?

Does anyone know how to fix it so it will fill in all the fields so each row shows the data (eg. so A3 will fill in)? otherwise I end up doing it manually.
thanks.

Hi

In my daily work, I usually need to work with huge text files present in the following format:
-----------------------------
// Start
// Trial1:
//1. Set1
//2. Set2
//3. Set3
// Trial2:
//1. Set1
//2. Set2
// Trial3:
//1. Set1
//2. Set2

// Start
// Trial1:
//1. Set1
//2. Set2
//3. Set3
// Trial2:
//1. Set1
//2. Set2
// Trial3:
//1. Set1
//2. Set2
//3. Set3

----------------------------

There are usually 100s of such start blocks in each text files and "set#" are usually 1 to 15 within a "trial#" block.

The "Set#" are usually string values which are not unique with respect to other Set#s.

Also, each block starts with only "// Start" , and within each Start block, there are specific "Trial" blocks only as "Trial1", "Trial2" and "Trial3".

Is there a way, to enlist these in blocks in Excel so that:

a) Each start block is present in single row, with each trial block occupying 3 consecutive columns, side by side
b) Next start block start from the next row.

A reference output format in excel is attached.

Please guide ...

Hey Gurus, I am trying to reformat a sheet (Excel 2007) from Horizontal (Time allocation) format to Vertical (Tabular) format by using macros. Could you please help me out on this? The macro should be able to run on any number of rows and columns.

Sample Input Data

ITEM LOC 1/30/2011 2/6/2011 2/13/2011
1534 VLLF 5500 11000 5500
7945 VMED 100 5500 10
7529 VBOC 20 200 20
1674 VSAM 5376 0 0

Expected Output

ITEM LOC STARTDATE QTY
1534 VLLF 1/30/2011 5500
1534 VLLF 2/6/2011 11000
1534 VLLF 2/13/2011 5500
7945 VMED 1/30/2011 100
7945 VMED 2/6/2011 5500
7945 VMED 2/13/2011 10
7529 VBOC 1/30/2011 20
7529 VBOC 2/6/2011 200
7529 VBOC 2/13/2011 20
1674 VSAM 1/30/2011 5376
1674 VSAM 2/6/2011 0
1674 VSAM 2/13/2011 0

Please see attached spreadsheet (contain sample data) for both input and expected output.

Advance thanks all for your help.

EPalanis

Hi Excel veterans, I seek your help!

To greatly increase my own efficiency, I seek an Excel 2003 compatible events calendar which can take data from one worksheet (tabular format) and have a calendar format generated on the next worksheet with the dates and info corresponding to the original worksheet.
General rules I can think of..

1) 2nd worksheet must be in calendar format, monday tuesday wednesday etc.
2) 1st worksheet is purely data entry, where event name will appear in calendar on the corresponding day
3) multiple events can take place in one day, ideally around 5 or so..
4) Must be fully compatible (or at least workable) in Excel 2003
IF POSSIBLE:
5) Calendar is able to capture no. of days of that event
6) Color coding for that line item in the calendar, say 5 colors for 5 conditions

Are there any templates out there which can fulfill at least the first 4 request? if so please do link =) I have looked at so many calendar templates and I only see one that fits the bill but that is not fully compatible with Excel 2003 =(

Thanks for the help in advance guys, you guys rock!

I have a sheet wherein I have to transfer amount of a security between two or more funds and the funds and security are arranged in a tabular format with security hiven in column headings and funds in row heading and amount in the table. I want the data to be arranged in
Security - From Fund -To Fund -Amount format.

Please see the attached excel which contain the format in which i get the data and the required format. Can anyone provide a formual or macro which can to the required arrangment.

Thnaks in advance for any help I can get

Hello,

I am using Excel to manage the sales data and inventory for a restaurant. I know that a database would be a more appropriate tool, but the owner is cheep and that won't happen. The problem I am running into is that what is easy for data entry is not ideal for creating pivot tables. Currently I have most data in cross tabular format.
EG:
date gross sales net sales #of sandwiches gift cards sold
1/1/1 $2104.04 $1975.33 122 5

What makes pivot tables most flexible is a single row per variable
EG:
Date Metric value
1/1/1 gross sales $2104.04
1/1/1 net sales $1975.33
1/1/1 # of sandwiches 122
1/1/1 gift cards sold 5

obviously the latter is a pain for data entry and the former much simpler.

Does anyone have any suggestions to make this process simpler? Each day I will need to collect about 50 data points. I also need a flexible solutions as recipes, promotions, and ingredients change frequently.

Thanks.

Fellow Forum Members,
Not sure if this is the is the proper subforum to post in or if my Keyword title is adequate, maybe the forum admin could address these concerns.

I'm using Excel 2007 and my objective is to setup a trading log that tracks my performance with charts that dynamically update to applied custom date range filters. The link below shows screenshots of what I'm trying to emulate with Excel 2007.

http://www.fxtraderlog.com/screenshots.htm

I have all my trading data laid out in a tabular format simlar to what is shown in the top screenshot. My tabular layout differs in that it includes 4 additional columns. My chronological data layout I'm using seems to be troublesome for conventional Excel charting methods.

When I apply custom filters for open dates and close dates, my Excel charts do not dynamically react. What I have determined is that I need to bypass the conventional charting methods used in Excel and focus instead on using Charts that rely on a VBA script or a formula. My objective is to have multiple charts (like the ones shown in the screenshots link above) all using the same data nested in my tabular trading log.

Can anyone out there help me out with setting up Charts that are more robust? I would appreciate example VBA scripts and / or links that will show me how to setup charts that dynamically react to custom Excel Filters, and can group together matching data sets in a column and show such data sets as a single slice in a pie chart. Currently, I'm getting multiple pie slices for the same data set. I need a formula or a VBA script that will clump together all of the data that belongs in the same group and show it as a single slice in a pie chart.

Any help that will help me get past these two hurdles will be greatly appreicated. Thanks.

Hi,

I would like to developed a summary of the my Lotus Notes log database and for that I need to split a cell containing many pieces of information into individual cells. Then I should be able to apply filters, use Pivots or whatever... so basically what I want to do is to bring my file (the one in which each cell itself contains many individual piesces of information) into a tabular format...

You'll be able to clearly understand my query if you take a look at the atatched text file

Looking forward to some help here. How can I solve this so that the soltion works whenever I get a new file.

Hi

I have a worksheet called "ComplaintData".
In this worksheet I have data in following range : "N1:V2".
N1 to V1 is headings and N2 to V2 is data transfered from listbox on userform.

I am trying to print this range via command button on my userform.

I am wondering anyone can help me with the code for selecting and printing this range when user clicks on the "Print" command button on my userform?

Also, Any ideas how I can format this print out to look different than the tabular format? I mean if possible I would like to print this in portrait, fit to one page and possibly not look like a table. (If it has to look like a table then I will have to go with landscape as the table wont fit portrait without getting too small and un-readable)

would appreciate any help!

cheers

Hi everyone,

I am trying to develope a way with a macro that can take some raw data from a few sheets (attached shows 2 sheets), and merge the data into a tabular format as i have shown, by transposing it and placing it in the right spot. Its appears to be something quite simple, but I am at a loss when it comes to VBA.

Thanks in advance

gmccreedy

Hello-
I am trying to create a user pop-up box that can accomplish the following:

a. Pop up box that requests an input for a particular machine number, type of error and also a particular calculation type (STDEV,Average,etc).
b. The result will be displayed either in a tabular format or if applicable, a chart

I have 10 worksheets where each worksheet is a different mahcine ID. Contained within the worksheets is various data-sets(Data listed by week). I am trying to enable my mangers ability to not have to look through my data or choose a worksheet within my workbook. I would rather have a input screen that would give him a choice. Any help would be appreciated.

It would be nice if the Chart Wizard had the option to plot 'Series in
Tables' as well as in columns or in rows. I frequently need to plot data
that are in tabular format (e.g., months in columns and years in rows), but
what I really want to do is plot all of the data in the table as a single
time series. In many cases, the data I am plotting are imported into the
spreadsheet (output from other programs), and I don't necessarily have the
option of easily changing the format to put all the data in a single row or
column.

Does anyone know of a way to accomplish this?

Thanks!
Hugh John Cook

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

I have a spreadsheet that contains one record per row, a dozen fields per record, showing the date and census figures for a number of local hospitals.

In that same workbook, I have another sheet that shows a 5 day moving average census at various facilities as well as a box that uses the lookup feature to find the most recent date entered and to display census figures from that date only in tabular format. This all updates automatically as a new row is added.

what I WANT is a formula that will find the last date entered, and then go over and pull off the last 5 entries in a COLUMN so that I can display tabular census over time for Hosp 3 for example. This all needs to be auto-updating so that when a new row of data is entered, everything else updates. In concept, it is 'ok, find last date and show me the census for Hospital 3 on THAT day plus the prior 4 days for Hospital 3"

date Hosp 1 Hosp 2 Hosp 3

mon 5 9 10
tues 7 8 11
wed 7 7 9
thurs 9 8 7

Hello,

We are using a pivot table to run a weekly payroll report. It is organized by a roster code with all of the individuals that fall within that last code (by last name). The pivot table report needs to be in chronological date order for each person. Here is a link to an example of the report:

Ideally, for each person, we would add information on the total number of hours in each Category for the week, so for Baker, that would be 35.25 Regular and 4.75 Sick.

Is there any way to add this kind of weekly subtotal by Category for each person to the pivot table while still keeping a similar (tabular) format to what we have above? Ideally it would be as automated as possible because we have the payroll data refreshing automatically from the database so that we can update the pivot tables.

Thanks so much in advance for your help!!!!!!!

I am very frustrated as I think this is very doable, but I just don't
know how to do this...so I have a pivot table:

USA
| June | Volume
| July | Volume
Canada
| June | Volume
| July | Volume
etc...
(except first line for each country is on the same line-i am having a
hell of a time trying to format posts for this board)
But I want this pivot table report to show in a tabular format, meaing
that the countries should show up on each page:

USA | June | Volume
USA | July | Volume
Canada | June | Volume
Canada | July | Volume

etc.

I several more groups and want all of them to show up on each
line...Thx

--
sergv
------------------------------------------------------------------------
sergv's Profile: http://www.excelforum.com/member.php...o&userid=26927
View this thread: http://www.excelforum.com/showthread...hreadid=401969

I rec'd an Excel workbook that only contains a Pivot Table. The Data Sheet
used to create it was not included but all of the data is embedded - the
field list is present and the pivot table can be modified. How can I view
the orig block of data in its original tabular format?
Thanks and Regards,
MM

Many times we need to get data which is on web (in tabular format) into Excel.
If we do copy-paste, all data is placed in a single column and then we need to go and manually (or thru Data>Text to columns..) edit it.

Instead you can try this.

Take an example of following scorecard of India Srilanka match.
http://www.cric8.com/livescorecard/o..._full2_553.htm

Say you want this data in the excel properly.

-Open a New Workbook
-Data -> Get External Data -> New Web Query
-in the field enter the url given above (or the data you want to import)
-select from: entire page / only tables /only specific tables
-If you like the colour scheme on the web, select from the formatting options
-if you want that the link should not be maintained after import, go to Advanced.. and check "disable data recognition"
-Press OK

The entire data will be placed in the excel sheet. No need to separate it in columns again.

This has benifited me and some of my friends immensely, hope this helps you as well.


No luck finding an answer? You could always try Google.