All Questions
72
questions
0
votes
0
answers
21
views
Subtracting value from pivot table (only when the data is available)
The data in my pivot table changes every week. Sometimes the value for certain data will not be available during certain week and available in other week. For now I had to manually check the data and ...
1
vote
0
answers
100
views
Excel Pivot Table - Calculated field returning "1" for all rows
Column C (Final Contracts) in My Pivot Table is a count of "Leads.LeadID" in My Data per User Name. Column I (Test) in My Pivot Table is a calculated field with the formula =count(Leads....
0
votes
1
answer
319
views
How to un-pivot data in a table on Excel for Mac
I've read the suggested results pertaining to un-pivoting a table using the method from a Jon Walkenbach article, but I'm on a Mac, and can't get the desired result.
My scenario is exactly the same as ...
- 21
0
votes
0
answers
79
views
Creating a technical excel data sheet to display efficient data
I am creating a table in excel that holds milestone achievement data with their respective amount. This table has multiple milestones that have only one amount, meaning that a set of milestone ...
0
votes
0
answers
77
views
Filterby multiple varying values in excel pivot table, without manually selecting
So I have a database connection that pulls data into a pivot table in excel, however I have a column called "Customer" I want to filter on, but i want to filter on specific customers (i want ...
1
vote
1
answer
516
views
Create scatter plot using two arrays of data in Excel [duplicate]
I'm an aspiring eSports rookie who has been tracking his training progress for some time now. I have two tables in an Excel worksheet: The first is Accuracy, with rows being date and columns being ...
- 13
0
votes
0
answers
20
views
PivotTable Conditional Formatting
I'm having trouble to understand how to apply PivotTable Conditional Formatting.
Homepage have a pivottable and a 'Date' filter.
Dataset have columns like:
Provider | Date | OC Number | Invoice Nº | ...
1
vote
1
answer
139
views
GetPivot formula is not taking cell reference into account
I'm trying to deduct sales on each date from the stock. That means if I sold 10 units on 7th July from the stock of 100 units, I'd have 90 units left, and If I sell 20 units on 8th July the ...
0
votes
1
answer
48
views
custom per-category formulae over summarised rows
I'm trying to figure out some pivot table magic here, where I can have the top-level row-field be a custom formula for each category. So, for example, with data like this:
Edit: Providing in CSV ...
- 163
0
votes
1
answer
112
views
Reusing row values in pivot table
From a list of contracts I want to learn which month the invoices are due. The invoice frequency is different per contract, ranging from monthly (12x) to quarterly (4x) and yearly (1x). Also the start ...
- 3
0
votes
1
answer
2k
views
How can I apply specific functions to pivot tables in Excel
I have some data that looks like this:
Item
Mass
CGx
CGy
CGz
Option
A123
10
4.2
0.1
0.5
AB01
C789
24
2.2
0.0
0.2
CD02
B456
12
3.8
0.0
0.7
AB01
D123
26
1.2
1.0
0.2
CD02
If I want to get the total ...
- 188
0
votes
1
answer
197
views
How to conditionally merge tables in Excel
I have two tables of data in Excel that were exported from our HR system. One contains estimated/planned workhours, the other contains actual worked hours. Each line has a date, hours worked, and the ...
1
vote
0
answers
69
views
How do I extract design information from a pivot table in an external worksheet (properties) (Excel)
I am teaching a course in Microsoft Excel, using Excel 2019. As part of an exam in this course, I ask students to create pivot tables. I will then need to grade these exams.
Each student submits an ...
- 36
0
votes
1
answer
154
views
How can you format each row from a pivot table to display transposed with the headers repeating themselves?
From a pivot table, I am attempting to take each row of data and format it transposed with the headers from the pivot table present in each instance. Here is a link to a Google Sheet that has a sample ...
0
votes
1
answer
231
views
how do I fix these issues Pivot tables and/or the IF formula in Excel 365?
I am managing a waitlist spreadsheet with many tab that requires monthly data to be extracted e.g. number of referrals, wait from assessment to intervention in wks etc. People move from the referral ...
- 3
0
votes
0
answers
87
views
Date error in GetPivotData in MS Excel
I have a row with dates, 2018-01-01, 2018-02-01 ... 2020-12-01, that I want to reference to, from a GETPIVOTDATA formula.
I've managed to reference to another cells for Gross Profit, AM, Region, and ...
- 23
0
votes
1
answer
52
views
Plotting combined column charts in Excel
I have data like as shown below in csv file for 3 terms such as A, B, C.
I am trying to plot them using a column chart.
Basically what my data indicates (ex: Terms A) is out of 200 items, 150 belongs ...
- 149
1
vote
2
answers
594
views
Excel Pivot Table Formula
I have an excel sheet that contains data like that shown below. I have the pivot table that shows departmental time and percent and an external value of total cost.
How do I create a formula that ...
- 113
0
votes
0
answers
22
views
How to move specific cells in a row from one worksheet to another based on Month in Month Due column
I have been googling for a while trying to find a solution for how to copy specific cells in a row from one sheet to another. I have only been finding ways to copy entire rows. I have a workbook and ...
0
votes
0
answers
142
views
Create a pie chart for different items with filter
I need to create a pie chart for sold and not sold for 4 items. Items (pen, pencil, book, box) should be in filter. If I click any item my chart should show sold and not sold number.
data.csv:
Item,...
0
votes
0
answers
76
views
Maintaining weighted average proportions in Excel when there is null data
I am not sure how to recalculate weights in Excel, preferably in a PivotTable, when there is null data (i.e., survey administrators didn't write anything).
Please refer to this workbook, where I have ...
0
votes
1
answer
137
views
What Excel function to categorize, sort or lookup values within a cell
I am trying to create a workbook (non-VBA) for collating materials required for student experiments. At the moment, I have a list of all the experiments and their materials. Below is a excerpt of the ...
- 41
0
votes
2
answers
58
views
Easiest method of displaying how many instances for each hour?
I have a call log which has thousands of calls in it. Each call is an entry in a CSV file which includes a datetime stamp.
What I would like to be able to do, is easily view how many calls there are ...
0
votes
2
answers
2k
views
How do I summarize a value according to multiple columns in Excel?
Summing a column according to one other column is quite easy with a Pivot table. However, it doesn't seem to be possible with multiple columns.
Similar to R's group by, I want to summarize one value ...
- 123
0
votes
2
answers
199
views
How can I combine a column with a row to generate new columns?
I have data stored as an excel file taken from the U.S. Energy Information Administration. The below is a snippet of that data. I want to transfer this to a GIS-usable format. As such, I want to ...
- 129
1
vote
2
answers
1k
views
Remove column names from formulae (Convert into regular cell coordinates without sheet names)
I have a defined table of several rows and columns containing data and formulae. Some of the columns' cells' formulae are:
=[@[Weight]]*1000
Where 'Weight' is the name of a column heading.
I want ...
- 177
1
vote
1
answer
157
views
Pivot or Formula for Reverse Lookup
I am creating a list of features and the audiences that will be targeted for those features. Each stakeholder group (audience) is separate by a comma in the list. Now, if I want to create a pivot or ...
- 11
2
votes
1
answer
1k
views
PivotTable Calculated Item IF Statement Issue
I have two columns of data in a pivot table but one of the columns is only partially populated. I've been able to use 'Calculated Item' to add a third column that will calculate the difference between ...
- 31
0
votes
1
answer
409
views
Excel group transposed columns by date
I'm having trouble transposing data to get the format that I'd like. I have pay period end dates in one column and employee names in the other column, and I'd like format it so that each column header ...
0
votes
1
answer
816
views
Excel pivot values column to Text
I have data in Excel pivot and my text data in value column shows number instead of Text . Any one can help me please. In below image of pivot value field red circled:
- 23
1
vote
1
answer
134
views
Combine none-unified data in excel based on row-id?
I have the need to combine different excel sheets of data into one single master file.
I have one master file with e-mail addresses and several excel-files from our CRM with data in different ...
- 11
1
vote
1
answer
23k
views
How can I assign an asterisk to a cell which has been formatted as 'Currency' and is also used as raw data for a Pivot Table?
I have a spreadsheet full of raw data, where I have created a Pivot Table to help organise and manage said raw data.
For the purposes of this question, I would like to use the below image to help ...
- 219
0
votes
1
answer
684
views
How to sort duplicate rows of values into unique rows and multiple columns
Say I have a sample table like this:
And I want to make it like this:
Basically, it is making values in column A unique by moving all duplicate values of each unique row into the next column over. ...
- 119
0
votes
1
answer
458
views
Microsoft Excel Pivot Table Filter - % of Parent Total
I am having trouble with filtering my pivot table/chart.
My objective is to understand the % of those products that have <= 20% DC Share across all Indexes but when I apply a filter for this on ...
- 1
2
votes
1
answer
6k
views
Grand Total Not Displaying Correctly for Pivot Table's Calculated Field
I have the calculated field Break Lateness to determine how late an agent is from Break 1 by using the following formula:
IF( 'Break 1' > TIME(0,15,0) , 'Break 1' - TIME(0,15,0), TIME(0,0,0) )
...
- 125
1
vote
0
answers
61
views
Referencing a Pivot Table Value, reaching arbitrary limit?
I've come across a really strange case with a formula I'm using to reference a pivot table where I'd love your help.
Currently I have a report that is a simple table, as seen here:
The report draws ...
- 11
0
votes
1
answer
2k
views
In an excel pivot table, how can I only select rows that has a particular value in it?
Here's the pivot table I have :
Mat Cust A B C D E F G H I J
1 P 0% 39% 18% 19% 0% 0% 0% 24% 0% 0%
2 Q 0% 52% 21% 19% 0% 0% 0% 7% 0% 0%
3 ...
0
votes
1
answer
6k
views
Add Slicer Selections to sheet in a list - Excel VBA
I have a pivot table With a slicer that a user can make multiple selections in. I am trying to list the selected values in the slicer so they can then be joined together in another cell using ...
- 28
0
votes
0
answers
357
views
Excel - Pivot table drill down using SUMIF
Not sure if this is possible, maybe with VBA but I can't work it out.
I have a pivot table that I want to be able to filter totals to separate totals less than/greater than a value. (eg all totals >5)...
- 28
6
votes
1
answer
31k
views
Working with Averages in Pivot Table (Calculated Field)
I have the following data :
customer_id customer_name id date contract_value costs profit_extra_work
200027 Company A 00.161.559 08/07/2015 499.5 50.55 0
...
- 419
0
votes
1
answer
491
views
Excel SUM from two tables based on ID in both tables
I have 2 tables in Excel:
Table1 with ID, PointsA ... &
Table2 with ID, PointsB ...
ID from Table1 and Table2 have duplicates and some in Table1 are in Table2 and vice versa but not all of ...
- 101
0
votes
1
answer
261
views
Summarize different tables into one table
I have many tables and I want to summarize these table into one table.
As shown in the image I want to summarize the above tables into the one appears at the bottom. Notice that the above tables may ...
- 3
1
vote
1
answer
64
views
How do I extract dates from a weekly tracker?
Let's say in each hour of the day in a week I record for example the food I eat or the physical activity I do.
At the end of the week, I have a table where the rows are hours (12am-12:59am to 11pm-...
- 71
4
votes
1
answer
32k
views
How can I get the number of rows in an Excel pivot table for use outside the pivot table?
I have a pivot table in Excel of the form:
Country Server Status
Australia AU1 1
AU2 1
AU3 4
New Zealand NZ1 4
NZ2 ...
- 443
0
votes
1
answer
106
views
Excel Pivot Table Can't Access Average of Rank
In regards to making a Pivot Table. When I'm trying to use the Top 10 filter, I am unable to even see the option for Average by Rank, only Sum of Rank. What is preventing me from being able to select ...
- 11
1
vote
2
answers
126
views
How do I count rows with similar data in 2 columns as 1 data entry?
The SPC program test data is entered into is not able to perform compliance validation. Below is an image of data exported to excel. If the date and time are an exact match, it is considered 1 ...
- 113
4
votes
2
answers
5k
views
Hierarchical or recursive counting in excel (preferably within a pivot table)?
I'm trying to find a way to aggregate data in a hierarchical data set, preferably within a pivot table but other methods might be OK as well. Consider a data set (greatly simplified for the example) ...
- 350
0
votes
2
answers
3k
views
In Excel, I am trying to calculate a percentage of two sums
I have a rather large dataset, 44 columns and about 8k to 9k rows each month. The table has every trip that our customers have either completed or not shown up for. I have started with a simple pivot ...
0
votes
2
answers
2k
views
Pivot table representing data as parent/child rather than desired summary of totals
Ok, so struggling with a pivot table and I'm not sure of how to phrase the title exactly, so apologies if it isn't 100% accurate.
Please ask for more information where it is lacking in my description....
- 205
0
votes
1
answer
20k
views
Excel Pivot Table - How can I return a date in the row value?
I have raw data that includes hundreds of employees who have completed many different training courses and I am needing to insert a pivot table that shows the course name as the column labels, the ...
- 1