# Questions tagged [microsoft-excel-2011]

The 2011 version of Microsoft's spreadsheet application for OSX.

172
questions

**2**

votes

**2**answers

627 views

### In excel, how do I write a formula that understands blank cells are 0

In excel I have the following formula in J8 cell:
=SUM(IF($C8="DBS",SUM($G8-$F8),0)+$J7)
If G8 or FB are blank the result in J8 is #VALUE!, if I type 0 the correct answer is displayed. I do ...

**0**

votes

**0**answers

45 views

### Excel 2011 - pull stock price from web (data query)

I'm setting up a stock portfolio in Excel.
I want to pull a stock price from the web and insert into an individual cell. I want it to refresh every 1 minute. I've tried the instructions here: Tie ...

**0**

votes

**2**answers

2k views

### Summing values based on column and row titles

Currently using MS Excel for Mac 2011. I have been trying with no luck to make a formula that sums all values in an array that have a particular column title and row title. Row titles are unique, but ...

**0**

votes

**1**answer

393 views

### Using INDEX and MATCH, IF and AND to return the column title of a cell Excel 2011 Mac

[enter image description here][1]I have a data set consisting of columns representing 0.1 metre increments, and for each column I have a numerical value. These numerical values decrease as the ...

**1**

vote

**1**answer

5k views

### Excel: Combine multiple rows but keep column data?

I have an Excel spreadsheet (Excel 2011 for Mac) with 1175 rows containing species names (431 names total) and 72 columns (A:BT) of months from October 2011 to August 2017. Each row only has one cell ...

**0**

votes

**1**answer

260 views

### Need to Delete Blank Row including All Data in the Row - Excel 2011 for Mac

I have an Excel spreadsheet of email addresses with first and last names. Some of the names do not have an email address and it's blank so I want to delete the blank fields along with the first and ...

**0**

votes

**1**answer

280 views

### Excel: What's causes an option to appear twice in a drop-down menu?

I am working on a budget and I have a drop down menu for the items. I have one category called Food but when I type in Food, it appears that there are 2 options
I can only select one of them and not ...

**0**

votes

**1**answer

1k views

### Create custom hyperlinks in Excel

I am looking for a formula to automatically create custom hyperlinks into an excel spreadsheet, based on contents of another cell
I have a list of books where I can get the image from an url database....

**1**

vote

**1**answer

30k views

### How do I automatically sort a table in Excel every time one of its values is updated?

I'm keeping the results from a season of the baseball board game Strat-O-Matic in an Excel spreadsheet (Excel 2011 for Mac). The sheet has the league schedule and league standings. I've already set it ...

**1**

vote

**1**answer

59 views

### Match two columns, and bring along values in the corresponding columns - Microsoft Excel for Mac 2011 v. 14.4.1

I need to match two columns (B and C) and move down the values (C and D) inserting an empty line where there's no match.
into this
Is there a formula that can do this?

**1**

vote

**0**answers

329 views

### Office Excel 2011 for Mac : when exporting an excel sheet to csv, around 25000 empty csv lines get added

so when saving as windows csv and press save i get the actual data:
1,1,2,
then i get around 25'000 lines:
,,,
,,,
,,,
,,,
,,,
,,,
....
when i do the same with libre office i get only the data, no ...

**0**

votes

**1**answer

794 views

### How to do conditional formatting with date comparison on Excel 2011

Please forgive me since I don't know where to start with this problem.
I have an Excel sheet with multiple columns. Two of the columns are dates in the format of "MM/DD/YYYY HH:MM:SS", column A and ...

**0**

votes

**1**answer

345 views

### Excel 2011, VLOOKUP across sheets & List validation

Im trying to create a dropdown list on a sheet, that looks up a matched value in another sheet. In the picture below is of a sheet called PricesforCSV. Here I want the circled value in column B to be ...

**0**

votes

**1**answer

1k views

### Excel - Sorting within a Sort

I have an excel spreadsheet of my todos. Each row in the spreadsheet is a todo and each column gives an attribute of that todo (for example, the priority of the todo). I also have three columns that ...

**1**

vote

**1**answer

682 views

### How do I stop Excel 2011 VBA macro on an iMac?

I used to be able to type command+period to stop a running macro. Now, nothing seems to stop one. How can I interrupt and stop a running VBA macro, please?

**2**

votes

**1**answer

645 views

### Auto refresh Excel worksheet after changed by external application?

I have a question similar to this one but I'm just using an Excel file, no CSV involved:
Open a .csv file in Excel and have it update whenever .csv file changes
I have an Excel file that I ...

**-1**

votes

**1**answer

1k views

### How to use non-contiguous range arguments in Excel's XIRR?

The XIRR requires range arguments. I would like to know how I can somehow use 'split' ranges which are in different locations. Perhaps the picture explains it better. What do I enter in F7, if I want ...

**2**

votes

**0**answers

23 views

### Excel for Mac alters document names after saving

I have a 2012 non-Retina MacBook Pro with Excel 2011 for Mac version 14.6.3. Every time I save a document using the Save As command and save as a PDF document, Excel alters the name I entered as the ...

**0**

votes

**0**answers

31 views

### How do I create a macro in Excel for Mac? [duplicate]

I have found this thread on this topic
How to get the list of sheet names of ms-excel?
But I have no idea how to create the macro people are talking about. I will be grateful for your help on how to ...

**0**

votes

**2**answers

31 views

### How to find the number of rows that have date time fields between 5 and 6 PM

I have a phone log that I want to report some data off of. All of the solutions out there that I have looked up seem to assume I only have a time value and that they don't include an actual date.
I ...

**2**

votes

**1**answer

841 views

### Bitwise Operations Missing? (Excel for Mac 2011)

According to the relevant
Microsoft Support page on the BITAND function, I should have access to it, as I am running Excel for Mac 2011 (with the latest updates installed):
"APPLIES TO: Excel 2016, ...

**0**

votes

**1**answer

1k 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 maintaining ...

**2**

votes

**2**answers

6k views

### MINVERSE only returns a single number

For the life of me, I can not get the MINVERSE function on Excel 2011 on my Apple Macbook to work.
I entered the function into a matrix.
I type =MINVERSE(A1:C3), the location of my matrix to be ...

**0**

votes

**1**answer

45 views

### Concatenating datas from different sheets

I have an Excel workbook with 2 sheets. Sheet #1 contains 2 columns, names and points.
| A | B |
|-----------|----|
| John Doe | 65 |
| Jolly Doe | 45 |
| Hello Doe | 50 |
Sheet #2 ...

**0**

votes

**1**answer

102 views

### How to get the difference between 2 cells

I have an Excel file like below.
The value in column C should be the difference between A and B (A is always longer, B is always A substring).
If both values in A and B are same, C will be empty.
...

**0**

votes

**1**answer

858 views

### Merge two sheets on two columns into a new sheet in Excel?

I'm using Excel 2011 on OSX. I have Sheet1 that looks like this:
pupil_id date test_score
1 2015/10/01 98
2 2015/10/01 83
And I have Sheet2 that looks ...

**0**

votes

**1**answer

2k views

### Excel formula disappears when saving as .xls

I have a long formula in one of my cells. It is:
=IF(K11=1,MATCH(C66,valv1,-1),IF(K11=2,MATCH(C66,valv2,-1),IF(K11=3,MATCH(C66,valv3,-1),IF(K11=4,MATCH(C66,valv4,-1),IF(K11=5,MATCH(C66,valv5,-1),IF(...

**2**

votes

**1**answer

593 views

### Can you autofill numbers going upward?

I know how to autofill numbers going downward. But I'm curious if you can autofill numbers going upward? I'm using Excel 2011 on MacOS X (El Capitan).
It may sound counter-intuitive, but my boss ...

**0**

votes

**2**answers

3k views

### Hide a Control Box in Excel

I have some radio buttons in Excel (2011 Mac), and they are grouped by a Group Box.
I want to make the Group Box invisible. I searched on the internet and found that I could do that with VBA by ...

**0**

votes

**0**answers

429 views

### Why doesn't Excel auto-adjust formula when a row is moved?

I have a simple spreadsheet with just one formula, which auto-increments the number in the first column, going from cell A6 down. So the value in A6 is hard-coded 1, then A7 contains the formula =A6+...

**0**

votes

**1**answer

30 views

### Can Powerpoint update two Excel objects that are from the same Excel file?

I've been reading through the forums and haven't come across a definite answer for my query. I have a PPT presentation that includes 2 different worksheets from the same Excel file, pasted as Excel ...

**1**

vote

**1**answer

3k views

### How can I use a macro to group and average the values in an Excel column?

I'm new here and I've been struggling with macro writing. I was wondering if anyone could help me out with making/explaining a macro that could be used in my data set.
I have an Excel file that looks ...

**0**

votes

**1**answer

76 views

### Getting the name of a named cell in MS Excel 2011

I am using a formula in Excel, which involves cell names.
The formula is
=MIN(name1,name2,name3)
name1 is the smallest, but it shows name1's value. I want it to show the text "Name1". How do I do ...

**2**

votes

**1**answer

1k views

### Replace method generates application-defined or object-defined error in Excel for Mac

I'm having trouble with this on the latest version of Excel for Mac.
Here's the macro:
Sub Run()
'
' Run Macro
'
'
Sheets("Data").Select
Cells.Select
Range("BK1").Activate
...

**0**

votes

**1**answer

60 views

### Sorting using Subtotals and Data Subtotals

In sorting using Subtotals via Data Subtotals, is there a way I can get all lines of info to come down NOT just the ones I'm totaling?
The only way I have remedied it is copying it down from the Page ...

**1**

vote

**1**answer

1k views

### How do I use Excel 2011 Personal Macro Workbook?

I realized I asked about excel 2010, but that only covers Windows OS. How can I use the Personal Macro Workbook in Excel 2011 on OSX?

**0**

votes

**0**answers

110 views

### sum rows and columns depending on two values

I got the following table in Excel (Microsoft Excel 2011 for Mac).
It is basically for controlling issues. It shows a number of persons (employees) that are assigned to specific work-packages that ...

**3**

votes

**2**answers

8k views

### How to close an excel file using terminal or python

I am using XlsxWriter to create an Excel file using a Python script.
When I re-run the script to update the file with new information, which I need to do often, I have to manually close the Excel ...

**1**

vote

**0**answers

347 views

### EXCEL 2011 - Inventory with Matrix and Vlookup

I am creating an inventory matrix, which has on the first column a list of all the items in the inventory, and on the first row a list of the dates for when the inventory gets renewed. Basically, not ...

**1**

vote

**2**answers

1k views

### How do I use IF in MS Excel-Mac 2011 for 3 variables?

When I type A, B or 0 in one cell, I want to see in other two cells (separately) to show
2,1 if I type A
1,2 if I type B
0,0 if I type 0
Can someone help me how to do this?
I currently only have ...

**1**

vote

**1**answer

141 views

### How do I match cells in one worksheet to another and print a result?

Not new to excel, but new to formulas. I have to say this is killing me.
So what I am trying to do is take strings in one column, check if any of them are in another column of a separate worksheet, ...

**0**

votes

**1**answer

148 views

### Error in importing the numbers in scientific notation

I am importing(copy-paste) values into Excel from R Console which are in scientific notation (1.920943e-10, 7.580894e-05). Excel is not treating them as numbers instead text. How to resolve this ?
...

**0**

votes

**0**answers

543 views

### Update Table based on Range start value and end value

I have a set of data that uses a primary key of month of the year by numerical value, e.x. Month 1, Month 3. I am trying to update my table and its values from the raw data set based on a user entered ...

**1**

vote

**2**answers

76 views

### Get rid of PivotTable help on Excel for Mac

Every time I start up Excel For Mac 2011 and build a Pivot Table, Excel feels it's needed to remind me in a modal what Pivot Tables do ("PivotTables summarize data and reveal patterns and trends").
...

**0**

votes

**1**answer

30 views

### I want to take two columns of cells (not the whole columns) and copy them and sort them into different cells

My Table:
I want to copy the names and scores from the desirability score area into the "Most to least desired rooms area but place them in descending order. How do I do this? Edit: I want the ...

**3**

votes

**1**answer

33k views

### How to make a concentric pie chart in Excel?

I'd like to make a chart (preferably on its own sheet) that is a combination of a regular pie and a doughnut. The regular pie sits in the middle and the doughnut surrounds it.
I have seen those ...

**4**

votes

**1**answer

69k views

### Remove duplicate rows, and keep newest row based on date column

I have a huge list of data in excel (250.000+ rows) in the following format:
Number Value1 Date Value2
40325 1 21/01/11 18.10 2
65485 3 22/01/11 16.47 2
40325 9 ...

**0**

votes

**2**answers

1k views

### Is there a way in Excel to have a formula stretch its row extent to include adjacent inserted rows?

In Microsoft Excel 2011 for Mac OS X, I have several columns of numerical figures and a final row at the bottom that shows a maximum value for each column. My data set is ordered, such that most ...

**1**

vote

**1**answer

279 views

### How to create stacked chart

I go nuts at Excel. So here's my problem: I have a table where the Y-values are projects that we are working on in that year on the x-values are project related properties. On of those properties is ...

**1**

vote

**3**answers

13k views

### Excel 2011 for Mac/ Pivot table / tabular layout how to “fill down?”

I've got a great pivot table in tabular layout, but how do I "fill down" values in column A?
A10 should also read "Alpaha Police"
A15:A18 should also read "Atkinson Co Sheriff"