A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function.

learn more… | top users | synonyms (2)

0
votes
3answers
93 views

How to tell Excel that “Years” are “Years”, not data

I have googled everywhere about this but no solution found. I have three columns in Excel: A: Years, from 1960 to 2013 B: Maize production (in tonnes) C. Wheat production (in tonnes) If I make a ...
3
votes
1answer
68 views

Excel date month issue

I am working on projects that come in every 4 and a half month. I want to make a calender so I can see when the deadline is for each project. I have a formula that adds up 4 months and 14 days. (4,5 ...
1
vote
1answer
86 views

Copy an excel file with Macros and New Tabs in the ribbon to another computer

I'm working with Microsoft Excel 2013. I'm using couple of macros I did and I made a new tab in the ribbon menu which shows the macros I did so that I can click on the this created tab then click on ...
0
votes
1answer
115 views

Automated Goal Seek - Excel

I have been using goal seek successfully to back into a known number. This works great with a handful of lines - now I need it for 100s (probably 1000s) of lines. Any help would be greatly appreciated....
0
votes
0answers
35 views

Put a new string of characters on a new line after each line break after every new string in a text file

I need to put a new string of characters on a new line after each line break after every new string in a text file. It is a large list of website addresses that are only seperated by one space on one ...
6
votes
1answer
144 views

All Public Variables in VBA Macros in my office stopped working this morning

It turns out, I'm an idiot (or, at least, hopelessly inexperienced with this whole software development thing). The below is what I spent my whole day panicking about. I'll write an answer detailing ...
0
votes
0answers
26 views

How to merge/teleport a column

I'm working on an inventory sheet. As I sale items or buy items, I'd like to have one enter point ( or two if needed) to key them in. So if (G5 = sold) then the number enter would "move" to F5(total) ...
0
votes
1answer
68 views

How to to divide column containing formulas and hard-coded strings into two separate columns?

I have a column that consists of hard-coded cells as well as formulas. Based on whether a specific cell is either hard-coded or contains a formula, I want to display it in two separate new columns. ...
0
votes
0answers
134 views

How can I select two data points on line graph and return its corresponding values?

I have a simple line chart and I want to select any two data points of line and wants in return its corresponding value. For example, my 11 different points are below. Simple line graph can be drawn ...
1
vote
2answers
110 views

Returning a blank cell in MS Excel formula if 2 source cells are blank

My formula in B3 is =A3+B2-B1. I want B3 to be blank if both B2 and B1 are blank also. Could anyone help me with this please?
0
votes
1answer
88 views

Turn off automatic display of properties

In my workplace, any time I open an Excel sheet (Office Professional Plus 2010) the "Document Properties" or "Other Properties - Server" docked dialog appears at the top of the document, dominating a ...
0
votes
1answer
38 views

Merging five colums but do not merge if row contains two values

I have 5 columns all with numeric data in. R S T U V The first three columns can be seen here so you get the idea. I want to merge them all into the first column "R" - in the vast majority of ...
0
votes
2answers
865 views

How to lookup a value and return multiple unique values to a single cell - comma separated

I am attempting to lookup a unique identifier (Product Number) which is held in several different stock locations. For example: __A__ __B__ __C__ 1 Item # Warehouse ...
2
votes
1answer
114 views

Concatenate a range with a conditional seperator

I'm looking for something similar to this trick for concatenating a range with a seperator, but I want the output to include a special character on the item on the range that is for this row. For ...
0
votes
1answer
1k views

Date values displayed as numbers instead of dates

Whenever I type a date into one of my worksheets the cell displays the value as a number not a date. The cell's value is a date and the format is date but the text displayed is a number. ? ...
0
votes
0answers
65 views

Multiple selection and graph export in Excel Online

I'm exploring Microsoft's online Office Suite and I have some questions about Excel Online. 1) Is it possible to multi-select multiple cells? I'm referring to the action in the "classic" version ...
2
votes
2answers
637 views

Show decimal places if not a complete whole number

I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any. For example: ...
0
votes
0answers
28 views

Excel 2013 External data import and copy

I have a workbook into which I import external web data. This is linked to another worksheet so that for instance when I refresh data into cell A1 that is automatically copied into A1 in another sheet....
0
votes
1answer
37 views

Sum of Excel Range Between Dates

So I've struggled with this problem several times and usually just end up giving up on it, but this time I want to solve the problem once and for all. I have a straightforward table as follows: Date ...
1
vote
2answers
40 views

Merge tables with each table a column in the new table, merging rows

I've looked at the question How do I merge two tables in Excel that have identical columns? (and the unanswered followup Merging tables in Excel). I have a case that is somewhat similar, but with ...
0
votes
2answers
399 views

Fill one cell depending on value of another

I'm trying to create a Risk Assessment sheet in Excel. I have one drop-down with days at the moment and I want another cell to be populated depending on what day is selected. I have managed to get ...
0
votes
1answer
55 views

Excel formula related to conditional formatting

Column A has 10 numbers expressed as percentages and is conditionally formatted to highlight the top 40%. I want column B to show a "1" next to the corresponding highlighted top 40% of numbers in ...
0
votes
1answer
10k views

Getting “Application Defined or Object Defined” error '1004"

So I was trying to copy data from different sheet and paste on current and I am getting this error: "Application Defined or Object Defined" error '1004" Can someone help me with this ? Option ...
0
votes
1answer
206 views

Find out the number of months between two dates [duplicate]

I need to calculate accumulating leave for personnel. It grows by 1.25 days per month. What formula should I use to add 1.25 per month starting from the date of employment?
0
votes
0answers
83 views

Excel 2007 Conditional Formatting On Formula Not Updating When Cell Updates

I have set the following conditional formatting rules: =$B97=1 Set Format (Custom) ">" @ On cell =$D97 =$B97=2 Set Format (Custom) "> >" @ On cell =$D97 =$B97=3 ...
1
vote
1answer
67 views

Creating a table similar to pivot table

I'm not sure how to explain it, but I hope this example makes it clear. Data: Yes/No | Place1 | Place2 | Place3 ---------------------------------- Yes | Thing1 | Thing2 | Apple No | Apple | ...
1
vote
3answers
166 views

Excel, Average for a specific column within table

Im trying to calculate an average just for one column within a table of values. It is like this: Hi Hello Hey 1 | 2 | 3 1 | 2 | 3 1 | 2 | 3 And I'm trying to get the average for one ...
7
votes
1answer
1k views

How to disable snap/jump scrolling in Excel 2016 for Mac

I'm desparately looking for a way to disable snap-scrolling in Excel 2016 for Mac. Basically as you scroll Excel will continually try to keep the cell you're focused on in the centered. So when you're ...
2
votes
1answer
325 views

How to transpose repeating groups in Excel

How would I transpose repeating rows and columns in Excel. For example how would I transpose the repeating pattern of rows (Tan color) to repeating columns (Purple). Would I need to do this in VBA ...
1
vote
1answer
251 views

Excel : how to put a literal dot into a cell format

Whenever I put a dot character into a custom cell format Excel will interpret it as if I want a decimal separator in that location of the format. The problem being that decimal separator changes with ...
0
votes
1answer
237 views

Search two columns in Microsoft excel 2013 and set the value of a third column with a specific value

I want to search a specific text which is the firstname_lastname e.g John_Smith in worksheetA. WorksheetA has a column firstname and another column lastname. So after knowing which row has the ...
0
votes
1answer
327 views

How do you prevent corruption of shared Excel files?

I have 10 or so Shared Macro Enabled Excel files that about 30-50 users modify multiple times each day. Over time the files get bogged down and bloated from excel thinking users are still using them ...
0
votes
2answers
53 views

Finding #NA values after Vlookup between two excel

We have two sheets for Jan and Aug. Both consist two columns , one for Client name and another one for no of shares they attain. we have to find out client names which exist in jan but not in Aug ...
0
votes
1answer
21 views

Excel on Mac automatically converts all numbers to thousands

I'm on Excel 14 for Macintosh. It automatically converts all numbers to be representative of thousands. I have searched throughout to change a preference or option and cannot find it. Example ...
2
votes
1answer
614 views

Microsoft Excel 2016 Mac: Smooth cell transition

Changing cells in Excel 2016 Mac by clicking arrow keys results in smooth (i.e., painfully slow) transitions. Is there a way to disable this? (Note: distinguish this, I think, from 'smooth scrolling'...
0
votes
1answer
58 views

Formatting time s:mm.00 in Excel (Office 2010) and graphing

My son is a competitive swimmer. I would like to make a stacked bar chart using the various time standards for each stroke. The times are listed in m:ss.00 format. I have several problems I need help ...
4
votes
1answer
115 views

Extract number from complex string if it falls in a given range

I need an Excel formula (or VBA macro) that will allow me to extract a value from a string. The string is a sequence of words, separated by spaces, in a single cell. I want the word (representing a ...
3
votes
1answer
119 views

Excel - VLookup Example

I am writing a simple list of equities for the various two playing card combinations against a certain range of an opponent. My column A is the first card (A, 2, 3, ..., K), the column B is the ...
0
votes
1answer
45 views

VLOOKUP returning N/A because references contain “.” and “ ” in one table, not in other

OK I am trying to vlookup a table but the problem is the references on one table are slightly different to the references on the other table. I can get the VLOOKUP function working, but it is ...
0
votes
0answers
6 views

Is it possible to autofit a resized row with wrap text?

I want to autofit a resized row that is wrap text enabled. It is wrapping the text but it is not autofitting. Is it possible to auto-fit a resized row (without VBA)? I resized the row to 30 pixels ...
0
votes
0answers
70 views

Exteremly Slow Microsoft Excel operation in LENOVO G550 series Laptop; Shall I add more RAM?

I am doing complex Macro operations in Excel 2007/2013 which generates random numbers and does many multiplications/divisions in each cell value. It also copies data to multiple worksheets and text ...
0
votes
1answer
88 views

How to find number ranges (with text input) in Excel

In column A, I have some numbers, formatted as text due to leading zeros: +------+ | A | +------+ | 0001 | | 0002 | | 0003 | | 0012 | | 0013 | | 0015 | | 0017 | | 0019 | | 0020 | +------+ Using ...
0
votes
2answers
63 views

Fill cells with a repetitive Date pattern in excel

How would I create a repetitive pattern of "dates" (e.g. 1-Jan-15, 1-Jan-15, 2-Jan-15, 2-Jan-15 .....) Saw this command in one of the posts =ROUND(ROW()/3,0) which I modified to =ROUND(ROW()/2,42005) ...
0
votes
2answers
54 views

VBA macro to interchange columns

Please could you help me in VBA macro to interchange columns for example There are 4 columns Supplier1, Supplier2, Supplier Score1, Supplier Score2 I want to change columns as below Supplier1, ...
1
vote
3answers
125 views

Remove spaces between characters in cell, but only if the space exists between two single characters

To process in 10,000 lines of excel, to remove spaces between characters in cell, but only if the space exists between two single characters. P J A JACKSON AND SONS should become PJA JACKSON AND ...
3
votes
1answer
1k views

INDEX + MATCH function returning wrong values

I have a spreadsheet in Excel 2013 that uses an INDEX and MATCH function to search by an interval time (in I4) to pull values from the corresponding row where it is matched (in column A). It works as ...
0
votes
0answers
27 views

Sorting data by association?

That title may have been misleading, I don't know quite how to put it. I have a set of data where a number stands for a description. It currently looks like this: 10 Blue 11 Green 12 Purple 13 Red 14 ...
1
vote
1answer
186 views

VBA - Create an array of all table names in workbook

My end goal here is to have a button, that when pressed creates a new sheet with a table on it.. that new table should be added to the PivotTable Wizard list. The error being called is error ...
0
votes
2answers
104 views

IF statement with multiple criteria and multiple possible results

I have a spreadsheet and I need a formula to give me one of three possible results (good, neutral, bad) based on data from two cells. MTD and YTD on budget would be good, MTD on budget & YTD below ...
1
vote
1answer
600 views

Excel 2013 opens files slow in Windows 10 (when not in Safe Mode)

This is a problem that has come up on Google and superuser in the past, although the solutions I have found haven't worked for me. If I run Excel 2013, it opens instantly. If I am in Excel and open a ...