Questions tagged [microsoft-excel-2013]
The 2013 version of Microsoft's spreadsheet application.
1,615
questions
67
votes
11
answers
262k
views
Excel copy-paste: always match destination formatting
I use Excel to crunch numbers, so I usually don't want to copy the formatting along with the text. But this is the default. Is there a way to default to always match destination formatting when ...
- 9,267
66
votes
8
answers
412k
views
How to turn plain text links into hyperlinks in excel?
I have a list of links in an Excel spreadsheet that I want to turn into hyperlinks so I can view them in a web browser. Is there a way I can change all of them to hyperlinks at once? I'm using Excel ...
- 793
53
votes
7
answers
67k
views
Disable Excel 2013 cell animation
When clicking into a cell of a Excel 2013 sheet, the green border of the cell is animated while moving from the current cell to the new one.
This is really disturbing. How can I disable this ...
- 8,238
47
votes
8
answers
277k
views
How can I stop Excel from opening a second blank window when opening a file from Explorer?
When I open an Excel file from Windows Explorer, I always get a second Excel window as well. Annoyingly, when I close it, it doesn't close, but the other window does!
This seems to be a common issue:
...
- 1,184
33
votes
4
answers
5k
views
AND function gives TRUE result for empty, BLANK cell - why?
I have blank B2 cell.
=ISBLANK(B2) gives TRUE
A simple logical test on it returns FALSE
=IF(B2,TRUE,FALSE) gives FALSE
Yet when directly used in an AND statement, it returns TRUE
=AND(B2,TRUE) ...
- 333
32
votes
4
answers
4k
views
Get rid of all ##### errors at once in excel [duplicate]
In Excel, I usually solve ##### errors by double-clicking offending column headers. However, this can get a bit tedious when there are multiple error columns. Is there a quicker way to solve this?
- 343
28
votes
11
answers
130k
views
How to prevent Excel to use the OS regional settings for date patterns in formulas
According to this question I have the following problem:
I want to use some Excel function (not the cell formatting) like TEXT(A1, {date_pattern})
But the person who answer my previous question make ...
- 743
27
votes
6
answers
147k
views
How do I add conditional formatting to cells containing #N/A in Excel?
I have a column in Excel 2013 filled with values found with VLOOKUP(). For some reason, I am unable to use conditional formatting to highlight cells which contain #N/A.
I tried creating highlighting ...
- 14.6k
22
votes
5
answers
62k
views
PERSONAL.xlsb not opening automatically
For some reason, my PERSONAL.xlsb file is no longer opening automatically.
I've checked the Trust Center and the file location (C:\Users[user]\AppData\Roaming\Microsoft\Excel\XLSTART) is a Trusted ...
- 353
21
votes
1
answer
93k
views
In Excel 2013, where are Visual Basic (VBA), Macros and AddIn tools?
In Excel 2013, where are options and tools such as Visual Basic (VBA), Macros and AddIn tools? Are they supported?
- 875
20
votes
2
answers
40k
views
Is there a way to skip down to the next change in value in Excel?
For my current daily use of Excel, I have a sheet where hundreds of consecutive rows have the same value for a particular column. I’d like a way to quickly skip down to the next different value.
ctrl ...
18
votes
1
answer
42k
views
Excel save behaviour of CSV file with UTF8 encoding vs UTF8-Bom encoding
My original csv files are encoded with UTF8-BOM, this is so Excel can recognise the Unicode charcters (If its just encoded with UTF8 then Excel does not recognise the unicode characters).
My issue ...
- 283
17
votes
4
answers
61k
views
How to fill in missing dates in chart
I'm looking for a simple way to chart a list of values each correlating to a date. For example:
Date Value
11/7/13 2
11/8/13 2
11/11/13 3
11/14/13 2
Etc. The problem is, the chart does ...
- 273
14
votes
6
answers
15k
views
How to disable SYLK warning for CSV files in Excel?
I load a lot of CSV files into Excel and some of them cause the warning where Excel thinks it's a SYLK file, not CSV. The cause is known.
How does one disable this warning? Is there a setting, a ...
- 18.7k
12
votes
5
answers
85k
views
Fill formulas until end of adjacent table
I'm trying to fill a column with a simple formula using cells from an adjacent table. I know how to do that by dragging the mouse, but it's a very long column, so I'd like to use AutoFill.
However, ...
- 2,680
12
votes
2
answers
17k
views
Tab (\t) is not recognized as columns delimiter when pasting into Excel 2013
I'm pasting data separated by tab, usually the excel would be a "little" cleaver and separate the data by columns at each tab character.
I don't know why, but Excel doesn't recognize the tabs anymore ...
- 1,961
11
votes
2
answers
3k
views
How can I identify zero values, but format non-zero ones?
I have a column in an Excel spreadsheet that contains monetary values, most of which are zero. I want to hide the zero ones, but keep the non-zero ones formatted as currency.
This Microsoft page shows ...
- 523
11
votes
4
answers
85k
views
Excel Conditional Formatting greyed out
So I have a sheet open in Excel 2013. I have come to add some conditional formatting and cannot do so due to it being greyed out.
My suspicion is that it is due to this [Group] thing. I know that ...
- 1,538
11
votes
3
answers
52k
views
Excel 2013 - Is there a way to turn off a chart series without removing it?
This past year I finally switched to Excel2013 from Lotus123. The only task I miss from Lotus (that I can't figure out how to do in Excel) is: when formatting charts and creating copies of charts it ...
- 111
10
votes
7
answers
76k
views
Excel filtering very slow
At the company I work at we have a Excel spreadsheet to keep track of the purchases the company made in the last year. This sheet has aproximately 1500 rows and 30 columns.
A few days ago filtering ...
- 115
10
votes
5
answers
265k
views
How do I show only the print area in Excel and grey out the rest?
I'm trying to replicate an Excel spreadsheet that someone sent me and I can't figure out how. The print area is set, and all cells outside the print area are greyed out. The cells aren't simply shaded,...
- 858
9
votes
5
answers
11k
views
Combining first two letters from first name and first two letters from last name
I have a spreadsheet of usernames.
The first and last names are in the same cell of column A.
Is there a formula that will concatenate the first two letters of the first name (first word) and the ...
- 93
9
votes
3
answers
34k
views
Excel TEXT function does not work properly
Let say I have the following values in the cells
A1 : `=Now()` which displays 17.03.2014 13:45
A2 : 12.03.2014
Both are in date format and I have the function in another cells like this:
B1 : `=...
- 743
8
votes
5
answers
275k
views
Excel Formula To Get First Non-Zero Value in Row and Return Column Header
I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right.
Below is a ...
- 376
8
votes
5
answers
52k
views
What causes Excel to raise an error about "one or more formula references" when saving?
Upon saving my Excel sheet, I receive the following error message:
We found a problem with one or more formula references in this worksheet.
Check that the cell references, range names, defined names ...
- 988
8
votes
2
answers
52k
views
Dynamically filtering data before importing from SQL Server in Excel
I'm trying to figure out whether there is a way to filter the data from an SQL Server connection before it is imported into Excel. The initial import doesn't matter (when the connection is set up), ...
- 93
8
votes
1
answer
147k
views
How to change excel legend order?
I have a excel graph with two lines using the primary axis and a column using the secondary axis. The legend currently lists the column first followed by the two lines. I would like to list the ...
- 91
8
votes
10
answers
81k
views
Why is Excel 2010/2013 taking 10 seconds open any file?
I have a fast Windows 7 PC with two SSDs and 16GB of RAM, so I'm used to programs loading very fast. But recently, for no reason I can figure out, Excel has started taking way too long to open Excel ...
- 181
8
votes
4
answers
14k
views
Excel when connected to external data: "We found a problеm with some content in [file]"
I have created a spreadsheet in Excel 2013 that gets data from a CSV file, then made a pivot table based on the raw data and a chart to visualise it. The connection was also added to the data model ...
- 4,889
8
votes
7
answers
19k
views
How to combine a range and cell into a new range within a formula in Excel
I have a formula in Excel that takes an array/range of values as one of the input.
I have a range of values, let's say A1:A6, and a single cell C11. I want to combine the range with the single cell ...
- 255
8
votes
2
answers
3k
views
How to make Excel pay attention to the clipboard
I use Excel 2013 for a few hours every day, and I really hate it. Outlook is worse, but most of the Office suite saps productivity rather than increasing it.
One example, which maybe someone can help ...
- 101
7
votes
4
answers
128k
views
X-Axis labels in excel graph are showing sequence of numbers instead of actual labels
I have a chart as part of a template which gets plotted based on source data. Data seem to be fine but labels in x-axis are shown as sequence of numbers 1,2,3,4,etc., instead of actual labels(these ...
7
votes
6
answers
75k
views
Why won't Excel 2013 work as an "Open With..." program in Windows 7?
I just recently uninstalled Office 2010 32-bit and installed Office 2013 64-bit on my computer. I was sent some text files that are tab-delimited, so I want to open them with Excel.
I am trying to ...
- 9,226
7
votes
7
answers
20k
views
How to merge two Excel columns into one (the other way)
I've a list as shown in A and B columns, and I want to merge as shown in D:
How can I do that?
Here, alphabets (a,b,c,d,e,f,g,h) are just used as place holders. What I really need in column D is A1,...
- 4,359
7
votes
4
answers
24k
views
Average numbers if another column contains a certain substring text in Excel
I'm looking for a way to average the numerical numbers in one column, say B, only if the string in column A contains a certain text.
For example:
(In column A) (In column B)
Hello table . . . 1
...
- 71
7
votes
2
answers
9k
views
What is Macro worksheet in Excel?
I have Excel 2013. To access VBA I accidentally pressed Ctrl+F11 instead of Alt+F11 and a new worksheet named ‘Macro1’ was created. This looks like an ordinary worksheet but the default column width ...
- 1,698
7
votes
3
answers
19k
views
How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?
In a Google Docs spreadsheet, I can use this cell formula:
=GoogleFinance("GOOG", "price")
to download the latest price of a stock. Is there an equivalent function in Excel 2013?
Earlier versions ...
- 858
7
votes
2
answers
49k
views
Excel: how to delete text and fill color?
In Excel you can press the Delete key to delete the contents of highlighted cells, but not the cells' fill color. Instead you need to click on the Fill tool on the ribbon and choose No Color.
Is ...
- 173
7
votes
2
answers
27k
views
Plot a line chart in Excel with multiple lines
Let's say I have a data table like
╔════════╦══════════╦═══════╗
║ Stock ║ Date ║ Price ║
╠════════╬══════════╬═══════╣
║ Stock1 ║ 1/1/2017 ║ 17.0 ║
║ Stock1 ║ 1/2/2017 ║ 17.4 ║
║ Stock1 ║ 1/...
- 239
7
votes
3
answers
13k
views
Format numbers in different locale (with period instead of comma as decimal separator)
I am using Excel 2013 on a German version of Windows 8.1. When I format a number as a number, in cells or in a diagram, it is displayed with a comma as decimal sign (like "3,1415"). I would like to ...
- 2,085
7
votes
2
answers
16k
views
Excel 2013 Help Offline View
Is there a way to enable offline viewing of Excel 2013's help window like that of 2010's?
Every time I hit the F1 key, Excel wants to connect to the internet to help me!
Edit: Is there a way to enable ...
- 170
7
votes
3
answers
54k
views
Equivalent of Google Sheets Importrange
What is the equivalent of google sheets =IMPORTRANGE function in MS Excel?
=QUERY(IMPORTRANGE("1yH4Y1bz4zchsC0TFuWkJsAaL_XXXruNHf56BOiNJIM","Another sheet!A:M"),"select Col1,Col2,Col4,Col8",1)
E.g....
- 171
7
votes
5
answers
84k
views
Inserting specific number of rows in Excel
I, for the life of me, can not figure out why M$ has not provided this feature. If I want to insert 597, for the sake of argument lets say blank, new rows, all available information tells me to either
...
- 717
6
votes
3
answers
93k
views
Excel adds double quotes on csv export [duplicate]
I am experiencing a weird unknown behavior in excel. The sheet I want to export to a csv file consists of 4 columns with data like this:
site.aspx|de|lblChanges.Text|some text that will be used ...
- 191
6
votes
6
answers
176k
views
Apply conditional formatting to multiple rows
When using the Conditional Formatting in Excel 2013, I can't seem to apply one rule to multiple rows.
I use data which is not related in multiple rows. For example, row A contains prices, row B ...
- 61
6
votes
2
answers
23k
views
How to format numbers and thousands with decimals in Excel?
I wish to display the numbers 23567 and 100 as 23.6K and 0.1K, respectively.
I know the format ##0."K" gets me close: 24K and 0K, respectively. But I'm unable to tweak it to do what I want.
I'm on ...
- 63
6
votes
3
answers
40k
views
How does Excel decide to use an absolute path for external links rather than relative path
There seems to be a difference in the behavior of relative links in Excel 2013 from Excel 2010.
What I'm experiencing in Excel 2013 is the following:
If the option 'Save external link values' is ...
- 171
6
votes
3
answers
2k
views
How to find and replace the character “*” in excel text values in cell, but NOT formulas
Very similar to this question:
How to find and replace the character "*" in Excel
But I need to leave formulas untouched.
I've got about 50+ sheets that have two types of cells with "*"
...
6
votes
6
answers
55k
views
Sum of different currencies Excel
I have an excel workbook which requires me to calculate the sum of different currencies on the same column, here is the result I'm looking for:
The $40.00 in Dollars was calculated based on item A ...
- 171
6
votes
2
answers
6k
views
How to protect worksheet with writeable cells that retain formatting and comments
Is there a way to protect a worksheet such that certain cells are writeable (e.g. user can put numbers into these cells), but the formatting and comment box cannot be changed? I am able to create a ...
- 389