# Tagged Questions

The 2010 version of Microsoft's spreadsheet application.

**0**

votes

**0**answers

18 views

### Anyone know what can make Excel freeze for a long time AFTER calculation?

When Excel calculates a large workbook (say, 60+ pages with 000s of formulae each) I figure it's pretty normal for calculation to take a while, especially when those calculations make fairly extensive ...

**0**

votes

**0**answers

43 views

### Copying only filtered data with a pivot table

I've got a pivot table with data from several geographical districts (60 columns 1000s rows). I have a pivot report I run by slicing on each district, using various slicers on other columns.
I want ...

**1**

vote

**1**answer

17 views

### Excel 2010: Hiding column with Ctrl+0 does not work

it is strange ... every shortcut overview I found so far claims that Ctrl+0 is meant to hide the selected column. On my computer (German Windows 7, German Windows 10, German keyboard layout) the ...

**1**

vote

**1**answer

31 views

### How to plot a complex table relation on excel?

How can I generate a table as displayed in the image attached using Microsoft excel. The x and y axis in yellow are simply perfect squares while The table content is composed of axis sums. I cannot ...

**1**

vote

**1**answer

26 views

### How to limit a filter to some rows only in Excel?

When adding a filter to an Excel spreadsheet, how do I limit its scope to some rows only?
For instance, in this document:
the “Hello” column filter will contain the values 0, 2, 4, 5, 7, 9 and 27. ...

**0**

votes

**1**answer

19 views

### Move data in a table present in 2000+ .docx files to a single .xlsx file (2010)

I receive 40+ emails per week each with a word doc attached. The word documents are in the same semantic format but carry unique client data authorizing services. I only need a portion of the data ...

**1**

vote

**0**answers

25 views

### Find and replace multiple values, according to table value - excel formula

I would like a FORMULA that change letters according to table of values that i make.
example:
êíöä = LOVE (because i made a table that show: ê=L, í=O, ö=V, ä=E).
example- Picture:
As you can see ...

**0**

votes

**2**answers

69 views

### Excel: What can cause manual calculation / F9 to stop recalculating?

What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?
I've Googled this problem and ruled out all the explanations I can find:
Calculation settings are ...

**0**

votes

**0**answers

12 views

### Search with key on multitple sheets

I need to count the presence of people for each week on a particular event.
Each sheet of my excel workbook has a date as a name, which is when the event took place.
The organisation of each sheet is ...

**0**

votes

**1**answer

72 views

### Excel Chart, Formatting Line with Stack Column Chart

I have a stacked column graph, it is duration based, goes for 16 hours, (starting at 4:00 pm on T, and ending at 8 am on T+1)
I have the primary axis completed, showing 0 to 20 hours, how can I get ...

**0**

votes

**1**answer

17 views

### Uncrowding a scatter plot when most points lie near lower end of X-axis

Given an Excel scatter plot thus:
where the bulk of the points lie at the lower end of the X-axis, how can the lower end be made to look less crowded (without removing data points), while ...

**0**

votes

**1**answer

21 views

### Combining/consolidating columns with almost identical data in Excel

Given the following table (which includes all the countries in the world, but is truncated here for convenience):
My ultimate aim is the scatter-plot the values in B against the values in E. The ...

**0**

votes

**1**answer

42 views

### Hightlight cells where the IFERROR=TRUE; Not “TRUE”. Suggestions?

Example:
Formula: IFERROR(H12,1)
Table:
I am trying to highlight every cell in the table that returns a 1 only if the IFERROR statement is TRUE (not "TRUE"), and without comparing the returned ...

**2**

votes

**2**answers

43 views

### Excel convert string equation to mathematical function

I have a spreadsheet whereas I have a string value in one column, and I have an amount value in another column, and a balance value in yet another column. I have a VLookup array to determine the ...

**0**

votes

**0**answers

41 views

### x axis formatting with dynamic excel chart

I'm having a problem with a dynamic x,y chart.
I set up named ranges using this method:
My x axis label is "time" =OFFSET(graf!$D$2,0,0,COUNTA(graf!$D$2:$D$1000)-1)
I do the same for my data (y ...

**0**

votes

**1**answer

49 views

### Excel Spreadsheet - Distributing Master Sheet rows and deleteing duplicates

I have a master sheet that I have the row distributing to different sheets with specific labels.
I want to have each row distribute to the appropriate tab (from the Master) dependent on the ...

**0**

votes

**2**answers

99 views

### Excel dynamic named range ignore blank formulas

In column A I have a formula that collates two other columns together.
=IF(ISBLANK(B5),"", B5&" "&C5)
Then in the Name Manager, a dynamic named range to use for a data validation drop down ...

**0**

votes

**2**answers

28 views

### How do I highlight some specific parts of a chart depending on the axis line?

I would like to know if it is possible to highlight (underline) some parts of a chart other than manually ?
See image below for a pivot chart where I underlined manually all the parts that interest ...

**0**

votes

**1**answer

11 views

### Correlating values between to columns

I'm a novice Excel user. Here's my problem. =COUNTIF('Roll-up'!$C:$C,B2) provides me with the total number of times value B2 (part number) appears in database C:C (corresponding list of part numbers)
...

**0**

votes

**1**answer

36 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 ...

**0**

votes

**1**answer

26 views

### Can I use the output of a cell as the cell location in another formula?

Here's what I have:
Cell D13=AN and Cell AE13=13
Cell K13 has this formula =D13&AE13 so K13 outputs "AN13".
From there I would like to have a formula that grabs K13's output(AN13) as a cell ...

**0**

votes

**2**answers

41 views

### Excel VBA Run-time error 9 subscrit out of range

While closing the workbook I am getting:
Run-time error 9 subscript out of range
This is the VBA code:
Set wbk = Workbooks.Open(path & filename)
wbk.Sheets(sheetname).Range("b6").Resize(...

**-1**

votes

**1**answer

60 views

### Format Data in a Chart Data Table

I'm working on a project where I'm creating charts programmatically using VBA. These charts include data tables. The business requirements specify that the chart data cannot be tied to the source data,...

**0**

votes

**0**answers

15 views

### Merging spreadsheets with same columns, some data same, some dup and some needs merged [duplicate]

I have two very large identical spreadsheets. Both have data populated. They both have some data missing here and there, in many cases it's different elements, there is some duplication and needs ...

**0**

votes

**2**answers

27 views

### Excel 2010 - How to push values into many cells based on a single cell

I have what can be seen as a questionnaire with up to 60 questions (all yes / no). If someone answers Yes, for example, in cell B1 I know questions in cells B6, B9, B22, and B50 will be NO. To be ...

**2**

votes

**1**answer

28 views

### How would I select and group multiple images from excel worksheet?

I'm having an issue with Selection.ShapeRange.Group.Select, if it's removed I'm left with only the selection of all my images on the active worksheet. I would like to group the images on each ...

**0**

votes

**1**answer

97 views

### Excel - Sort by formula when there are duplicate values

My data will change, therefore I am using dynamic lists. Sorting needs to be dynamic as well. I do not want to click on the built in sort function.
I'm sorting dynamic data by formula, but it fails ...

**1**

vote

**1**answer

42 views

### Excel count unique values if second criteria is also satisfied

I currently have a formula to count all unique values of a range, however I would like to expand this count all unique values, if a second column meets a second requirement, for example.
A ...

**0**

votes

**1**answer

49 views

### Losing characters when copying and pasting into a batch file via VBA

I am currently using the following VBA code to copy and paste a range of data in an excel file to create a Batch file. I am running into the following problems:
Quotations are being placed around ...

**0**

votes

**1**answer

11 views

### Excel - Change width of some cells in column

I have a spreadsheet where the top 2/3 is description and the bottom 5-6 rows are amounts. I want the amount cells to be less wide than the description cells and have not been able to do. Everything ...

**1**

vote

**1**answer

31 views

### Hide blank head rows in Pivot

Usually I use the filter to take out the blanks, but this is not adequate this time. Since I have three "columns" of my excel-sheet in the "Row Labels" section, called "Project-Category" (1-3), ...

**0**

votes

**2**answers

89 views

### Parameterizing IN clause in PowerQuery SQL query

I've seen examples (and coded one) that parameterize a PowerQuery SQL statement with values read from cells in the spreadsheet'a table. But these always deal with simple logical conditionals like ...

**1**

vote

**1**answer

35 views

### Excel 2010 does not complete manual calculate

I have an Excel file that has a happy number of sheets with various formulas, ranging from simple sums to far more complex array formulas.
I have always had the sheet set to automatic calculation, ...

**0**

votes

**1**answer

38 views

### Excel (.xlsx) - Drop-down lists/tables - Include specific values/text in drop-down list selection

Say I have a column, "Names", of single and multiple names (where two can occupy once cell), e.g.
Names
Anna
Bruce
Charlie
Bruce / Charlie
Anna / Bruce
Upon creating a table I am given a drop-...

**0**

votes

**0**answers

32 views

### Need simpler way of displaying data without merging cells (Excel)

Unmerged cells:
See attached screenshot. I want to show multiple limits/attachments/premiums for a single Insured/broker/industry.
I've tried merging rows (see below) for Insured/broker/industry ...

**0**

votes

**1**answer

40 views

### Index Match on multiple criteria not working

I have two sheets with the following data within;
Sheet1:
Column A (rows 2 - 100): EASTING;
Column B (rows 2 - 100): NORTHING;
Column C (rows 2 - 100): MYKEY;
Sheet2:
Column A (rows 2 - 100): ...

**0**

votes

**2**answers

33 views

### Excel Formula Repeating Not Desired, Assistance Requested

Alright so I have a problem that is probably fairly simple to fix but I can't seem to figure it out. My problem is I can't figure out how to make it only display the count once not as many times as ...

**0**

votes

**2**answers

21 views

### How can I extract time information from excel

I have a large data in excel with, 02:12 format. I want to find out the time difference between cells in minutes. Like cell A2-> 02:12 cell B2-> 02:20 now I want 08 in another cell using formula B2-A2....

**0**

votes

**0**answers

18 views

### Count number of minutes in each hour of a period defined by start and finish times in excel

I have a big number of records in excel which have a start date/time and an end date/time
Event name Started Ended
Event1 25/3/2016 01:55:10 25/3/2016 06:15:50
...

**0**

votes

**1**answer

30 views

### How do I copy elements from one sheet to another?

This is a slightly complicated question because it has two dimensions.
I have a row of formulas in one sheet in a workbook in Excel. I want to copy the numbers in that row as a column to another ...

**0**

votes

**0**answers

11 views

### Use file with new name?

I am trying to figure out how to use a file in excel with a new name. Every time I generate the specific file Windows creates a new file name for that file. However, there are specific unchanging ...

**0**

votes

**0**answers

13 views

### Using Excel 2010 I want to be able to hid and unhide columns based on if there is text in a particular cell in another sheet

I want to have a list of names in column B of sheet 1 of people within a group. Then in sheet 2 I want it to pull through the people's names across the top row of a new table (row 5). If there is no ...

**-2**

votes

**1**answer

4k views

### Create folders based on cell values in Excel + create hyperlink

I have the following cell values (Column FOLDERNAME) for which I'd like to automatically create a folder in my current spreadsheet active directory.
How to do that, this is my first question?
Inside ...

**0**

votes

**1**answer

37 views

### Conditional Formatting help in excel (highlight certain row if a cell in a column displays #N/A)

I am trying to use conditional formatting to highlight a specific row of cells if a certain cell displays #N/A. I tried to use the function = isna() but it did not work as it only highlighted the ...

**1**

vote

**1**answer

43 views

### Excel VBA - Calculation is returning sum for Last Row instead of First Row

I'm trying to set up a calculation that sums all of the values between "M" and "X" in each row. However, when I run the code, it only gives me the sum for the last row in the worksheet, when it ...

**1**

vote

**1**answer

61 views

### Weighted average, ignoring blank cells and avoiding the #DIV/0 cell error before sell has value in Excel

As stated in the title, I am having issues coding for a weighted average (in this example, if the client has bank savings and an annuity, I want the estimated return percentage average to take into ...

**3**

votes

**1**answer

72 views

### 3 column dependent drop down list

I have a 3 column table of data and I need to create drop down list based on these 3 columns.
Column 1 has duplicate values.
Column 2 also has duplicate values. The options are dependent on the ...

**0**

votes

**1**answer

1k views

### How to assign values to text fields for sum and average

I have 7 columns (E:K) of "Pass/Fail" results. I need to assign a value of "1" for the "Pass" result in 4 (E,F,I,K) of the columns and "2" for the other 3 (G, H,J) columns, along with a "0" for all of ...

**0**

votes

**1**answer

26 views

### Link excel file to SQL table

In all versions of Microsoft Access, I used to link access table to any excel
file.
Can this be done with SQL server 2012 without the need to import the file itself?

**2**

votes

**1**answer

34 views

### MS Excel: Add the contents of the column A to the cell B, if column B is empty

how can I add the contents of the cell A to the cell B, if cell B is empty in MS Excel 2010?
My situation in detail:
Column A (column with some empty and some non-empty values): AD
Column B (no ...