Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Xy Scatter Plotting & Filtering Series

Hi,

This has been troubling me for nights.

I have data columns of X, Y1, Y2, Y3, Y4...
I used XY scatter to plot them all on one chart, but would like to have a filter on the plotter to select the desired series for comparison, eg, Y1, Y4, and Y5 only. Is there a method of doing this?

I tried using pivot table without joy because it doesn't support XY scatter.
I tried grouping the data sheet, but this gets very messy and is not selectable from the chart.

Please Help!!!


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







I want to add data points to the scatter plot that are not part of the data
ie. add points to find their concentration on a standard curve.



I have a pivot chart with a bunch of data series, and every time we
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.

Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?

Thanks!
Keith





Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!


I'm trying to separate bars inside a bar chart into separate groups. Adjust the gap applies to all bars. How will I be able to achieve what I need? Please help and thanks


Hi,

I made a budget with many narrow columns and have turned the data into a table. I will only ever filter the data with the left-hand most column. However, all the other columns are filled each with the grey drop-down filter arrow button, and I can no longer see the headings of these columns and frankly seeing 50 little unused arrow buttons is rather ugly. Is there any way to simply remove the filter drop-down buttons on some columns or even all of them, just leaving a filter button at the heading of the first column? I know I can hide the headings, but then I lose sight of the first filter arrow that I use.

Sorry to ask such a bizarre question, but I appreciate your help.

Thanks


i know how to use the data filters vertically but have been wondering if its possible to filter data horizontally so i could put a filter on, say column c and sort the data across the sheet rather than down. if its not possible i will find another way to do what i want but this seems to be just what i would need. does anyone know if it can be done?


Hey everyone,

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?


I am trying to work out how to calculate the geometric mean of a series of values, some of which are negative, ie. investment returns over a series of years with some negatives.

The Geomean function doesn't work because it only uses positive numbers.

Any help would be greatly appreciated.


Hi!

I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.

However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.

I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?

Any advice is higly appreciated.


Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter


I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


I have a very simple pivot table that I am trying to use as a data source for a VLOOKUP request. Unfortunately, every time I do it I get the result #NA. I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result

Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?

Thanks


I'm attempting to filter columns E, G, and I all at the same time. I need to
display rows that have values greater than 0 in any one of those three
columns. So far I can only filter progressively. If I filter column E and
then filter column G, the second filter is only applied to the results of the
first filter.

Does anybody know if it's even possible to do what I'm trying to do?



Seemingly super simple, but I can't figure it out.

When I create a bar chart, the bars are horizontal. I want the chart bars to be vertical. It tried to rotate the chart so that it is vertical, but the "rotate" options are greyed out.

How do I get those bars vertical?


Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag


i want to retain the data and format, but get rid of the pivot capacity before sharing the spreadsheet. right now if a copy/paste special, i can get the data, but not the formats, any suggestions?


In my pivot table I have a lot of cells without any data. The report looks like garbage with all the (BLANK) sohowing up in the report.

I've tried condtional formatting where if the cells = (BLANK) it woulf format them as white -did not work

I tried the pivot table options and clicking on the box "for empty cells show" and set it to 0 then empty but that did not work.


any Ideas???

Thanks


Dear Sir,

If any one could give me a solution for this its will be a great help for me.

I am working in a bank and on every day we receive Month to date data of Loans and advances made by every branch with Region wise total and manager wise total. With Sum of loan and count of loan. In our MIS format the in A coulum branches are sorted and listed in a sequence with sub total Region. I have data where if if do the pivot and change the data the out put should be displayed accordingly in the MIS format. Can any one help me how can we do this or is there any other office addins/softwares are available if so the data is changed on a daily basis and if we do the pivot it will automatically copy the data to the MIS format.

Thanks.


Is it possible to apply a filter to multiple sheets within the same Excel workbook, using the same filter criteria?

For example, suppose you have tables of data on 3 different tabs, all having the same headings. Suppose one of the headings is "gender". If you apply a filter to the table on Sheet 1, to only show the rows containing the word "female", is it possible to have the tables on Sheets 2 and 3 automatically update to only show the "female" rows? Or would you have to manually update the filter criteria in Sheets 2 and 3?

I hope this makes sense. Thank you in advance for any help.


I am a very advanced Excel user so this is a new one for me.

When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.

In addition:

Can not fill series
Can not change cell format (specifically to show Currency or Accounting w/$ symbol)

And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.

Help!