create new tab taking data from one column

Jeovany and I have both pointed out that you are going to have to clean up the company names used. Any time you see 2 entries in the Company Slicer, there is a problem with the company names. Most likely problem one or more spaces at the end of the company name.   Simple fix, insert a new temp column, copy the company names using the =trim() function.  Enter the function, click in the company name column, close the parenthesis and hit enter in the first row of the table, it will automatically be copied down to the rest of the cells. That is one of the automation features of Tables. Then copy the new column, paste special > values to the original column and delete the temp column.

#1

No, you have to right click and select delete.  I checked, having a blank row in the table does not cause problems.

#2

Sorry, not that I know of.

#3

The lists tab contains the unique list of company names for the drop down on the input.  I forgot to mention it. When you enter the company column, look for a drop down arrow displayed on the right side of the cell.

#4

It's real easy. That is the beauty of the PivotTables. Easy to change, to make new ones to meet new requirements.

In the Pivot Table Fields pane, in the sum Values area, drag all of the other names out of it. That will show just the sum of the desired column. I think that is what you are looking for.

Here is the link to the learning resources I mentioned before. There are links to some free webinar recording and articles.  If you have any more specific questions feel free to ask.

In the dashboards section, in the past Mynda Treacy has offered free webinars and discounts on her courses in September. So if you sign up to look at one of the webinar replays now you can get on her mailing list for notice when the new webinars come up to get the discount.  If I remember correctly her courses run around $300-500, if you can get your company to pay for them it will really worth the cost.

myonlinetraininghub.com-  MyndaTreacy  

The others also offer good courses, pick any of them (one or more) that seem to best suit your needs.

********************************* .

The following Wiki has a collection of links the various “advanced” Excel tools. There links to free webinars, short articles and a few free and paid ebooks.

.

I like to download the webinar replay recordings for future reference, and download the example workbooks.  If you are gung ho, you could add time references into the workbook back to the recorded session to make future cross reference easier to find specific features / instructions (I’ve been doing that).

.

These free sessions run roughly 1 hour each followed by a short shill for their related courses (which definitely sound worth the price).

.

Getting Started with “PowerQuery” / “Get & Transform” > Tables > PivotTables > PivotCharts > Dashboards ... PowerBI WIKI:   https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620?tm=1537369871055****

https://1drv.ms/w/s!Am8lVyUzjKfpoBGWeAh0z876s7ri

e wiki- Get & Transform - Data Modeling - Tables - PivotTables - PivotCharts - Dashboards -PowerBI.docx

Since Office 2007, MS has introduced several "Power" tools that add a LOT of automation. Unfortunately these tools are rarely talked about, so they are effectively “hidden”. If you are interested in seeing some demonstrations of these features and their automation, the following Wiki has links to several free webinar replays you can view. I've used them as a starting point to learn about these new Power features.  Even as a novice level programmer in them, I am a HUGELY impressed by these tools and constantly try to learn new "tricks" using them.

PowerQuery (PowerPivot) > Tables > PivotTables > PivotCharts > Dashboards ... PowerBI ****.

At the very end of the wiki there are links to free download PDF ebooks about these features.

.

********************************* .

Here are brief descriptions of the various "Power" tools.

Hint: with these “Power” tools, MS has been up to it’s usual games, changing names.  For example one tool is called PowerQuery, except in 2016 when they called it “Get & Transform”, yes they returned to PowerQuery in 2019.  There are very vague mentions to “PowerPivot” and “Data Modelling” but MS rarely defines them

.

PowerQuery (Get&Transform) is used to import data from more than 80 different data sources inside and outside of Excel. Then it allows you to "clean" and format the data. PowerQuery records your changes so you can import new data later and it will automagically apply the same manipulations

.

PowerPivot / Data Modelling

Power Pivot is all about analysing data using the familiar PivotTable tool every Excel user should know

PowerPivot is a data modeling technology that lets you create “data models”, which is “geek speak” for creating relationships between different input tables, and creating calculation columns. With Power Pivot you can work with VERY large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel. In a nutshell, it allows you to use multiple data sources. Then you could import, merge and perform analysis on the resulting data.  Excel can “natively” only store 1 million rows of data. Well before you hit the 1 million row limit, Excel will start performing very poorly, particularly if you have lots of VLOOKUPs etc.  Power Pivot compresses the source data and stores it as a database within the Excel workbook. This means that you can store 10s (even 100s) of millions of rows of data directly in an Excel workbook in a highly compressed and performing way. You can distribute the result to other people without them needing to have access to the original source data.

.

Table

Tables take data in grid form and apply simple row/column formatting. Tables use “structured references” in preference to simple ROW COLUMN (A1) references. In Structured References you use the column names to make the fomulas easier to interpret. When you enter a formula in a Table, it automagically copies the formula to all of the rows in the table, including new rows as they are added to the table. PowerQuery exports/saves data to table format. PivotTables prefer input data to be in table form.

.

PivotTable does complex automated calculations using a “drag and drop and click” user interface. The calculation and formatting options are simply amazing. In the webinars, the “Pivottable pros” are able to provide examples as fast as they can describe them. With practice you should be able to create “quickie” examples while talking to users. PivotTables can be connected to “Slicers” which give the users a simple, visual, way of filtering PivotTables (and linked PivotCharts)

.

PivotCharts can be generated from PivotTables to provide a dynamic graphical interpretation of the PrivotTable generated data

.

Dashboards are “simply” collections of PivotTables and/or PivotCharts that provide the user with dynamic control ie via slicers or filters, of much data in a single compact display.

.

PowerBI is a new (free) standandalone application that incorporates all of the “PowerTools”. It actually provides more features, and more chart options. MS is very actively working on developing this tool, adding new features in monthly updates.

************************************************

***************************************************************************

************************************************

Hi

Thank you for your reply, ref your replies

1 ok I understand

2 ok they will have to learn to press TAB

3 what does selecting the drop down on the company name on the all data do? I tried it and clicked another name but nothing happened?

4 I have been playing with this all day

As you can see it highlights everyone, how do I get rid of the blanks. so Anglian, Cannon and HCC would not show.

My other question is if I click on one line of sum of total, it creates a sheet1 for the company, if I then go back to report and choose other options and by doing so, creates sheets 2,3, etc. Do all these separate sheets keep their data and refresh based on their relevant rules?

I hope you understand that last bit! lol

Regards

Stephen

#3

The company name drop down only works on the "All Data" tab.  I just tried it works as advertised for me. I click on the drop down arrow, pick a new name it shows in the cell. Have you downloaded the file or are you trying it from OneDrive (I haven't tested that).

#4

Remove the "Sum of Totals" from the Sum Value area of the pane.

#5

The "Drill Down", sheet 1.

As far as I know, no, these drill downs do not link back. You have to regenerate them to reflect changes.

Actually, it is simple enough for you to test:

• Create a drill down
• Just delete a row from "All Data" tab
• refresh the pivot, you'll see the changed value
• Create a new drill down, you'll see it no longer shows the deleted row, while the original one still has the deleted row

If you right click in generated drill down sheet, you'll see that the "refresh" option is disabled.  Linking back is a "missing feature".  So is the useless name.

Hi again

#3 I'm note sure point of that, I did click it and it changed the name, now I cannot remember what name was there?

#4 

As you can see I deleted the sum total, but it still shows the blanks?

#5 so once you have done the drill downs for maybe printing purposes, you might as well delete the tab?

Regards

Stephen