Questions tagged [microsoft-excel-2013]

The 2013 version of Microsoft's spreadsheet application.

Filter by
Sorted by
Tagged with
0 votes
1 answer
27 views

count unique with multiple criteria, what should i do?

Problem I want to count uniquely in multiple criteria but it seems the formula isn't working The count should be 6 instead of 3.5 what should I do?
user avatar
0 votes
2 answers
41 views

Index Function with error "You've entered too many arguments for this function"

=index(_70Rain,1+int((row(b4)-1/columns(_70Rain)),mod(row(b4)-1+columns(_70Rain),columns(_70Rain))+1) I just followed the code given in https://www.extendoffice.com/documents/excel/1172-excel-...
user avatar
0 votes
0 answers
19 views

Merging two columns unmatched vlookup values in excel

I have two workbook after doing vlookup on sheet 1 in workbook 1 and sheet 2 in workbook 2, I got some values that don't match with sheet 2, so i want them to add to sheet 2, how can that be done. ...
user avatar
0 votes
0 answers
22 views

I would like it to show "no sale" rather than 0 if possible when using sumifs [duplicate]

When I us sumifs formula in excel and if conditions are not matching the result is showing 0 (ZERO) but I want it to show no sale.
user avatar
0 votes
1 answer
63 views

In Excel my formula is skipping the first two cells of a table

Hi I have an Excel Spreadsheet and I'm trying to convert a table with multiple rows and columns into just one column for sorting and filtering. But the issue is that it doesn't start at cell A2 where ...
user avatar
0 votes
0 answers
37 views

Excel: How to match substring to another string and return the matched result?

When doing data dump for time log activities, the data comes out to simply the name of activities in one column and a string of one or multiple labels in another column (column B in image). Then I do ...
user avatar
0 votes
0 answers
29 views

Having issues filling a formula horizontally

Hey Guys please I need your help I am trying to copy a formula horizontally in sheet 2 while referencing cells from sheet 1 but it’s not working: So in sheet 1 I have a daily sheet for every day ...
user avatar
0 votes
2 answers
60 views

Excel Custom Ribbon Separators

I was looking for a way to insert vertical separators into a custom ribbon in Excel 2010, which seems to be problematic for everyone. I've searched the planet and been told it can't be done!
user avatar
1 vote
1 answer
35 views

My IF function isn't working when I try to pull out a specific word

I am trying to have the cell populate if the word CAT|I is in a different cell. I am using the following function: =IF(ISNUMBER(SEARCH("CAT|I,",F12)),"CAT I","NO") ...
user avatar
0 votes
0 answers
21 views

How to keep cell blank when applying formula from another sheet: Getting #value! error

I'm trying to calculate the days between two dates using the following formula =if(or(isblank(A2),isblank(B2),"",(B2-A2)) but I'm getting the #value! error message. Note: A2 and B2 has a ...
user avatar
  • 1
0 votes
0 answers
24 views

Several Odd horizontal blue lines through spreadsheet

I have a spreadsheet that has several, heavier than grid lines, running horizontally through my spreadsheet. Can't seem to get rid of them. Any ideas? Also, when I try to adda screen shot here, my ...
user avatar
  • 1
1 vote
2 answers
62 views

getting Max/Min value in range based on a cell's condition

Greetings to all Excel gurus.. I've got the dataset shown in the image attachedenter image description here.. I'd like to show the MIN and MAX value of "Price/Oz" based on the condition if ...
user avatar
11 votes
2 answers
2k 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 ...
user avatar
0 votes
0 answers
18 views

Office 2013, 2016 QATs remove padding at right

Can anyone help with how to remove the blank padding at right of the QATs? This is happening in both Word and Excel, 2013 and 2016.
user avatar
0 votes
2 answers
36 views

Append string to an Excel-cell, but ignore appended value when referenced in calculations?

I am wondering if there is a way to append a cell with a string value (for better/readable presentation), but ignore this appended value in formulas when referenced? For example, lets say A4 has the ...
user avatar
  • 323
0 votes
0 answers
74 views

how to add multiple hyperlinks in image placed inside single excel cell?

i have an image inside excel 2013 as shown below: i want to make multiple body parts hyperlink or behave as a button insight this single image. any help would be appreciated. Thanks,
user avatar
0 votes
1 answer
33 views

Excel: Take on the value of a formula cell when other cell is equal to x?

I know that title isn’t great but essentially: Let’s say A1 = some number And A2 = some formula involving A1 I want to store values of A2 in a column for different values of A1 so like a function ...
user avatar
0 votes
1 answer
29 views

Excel 2013 fails to record macro correctly

So, i want to create a macro that will set selected cells' format to 0,0 ¥. However, if you record the steps and then try to run the macro it will set format to 0,0 ?. Is there a way to fix this?
user avatar
  • 1
0 votes
1 answer
27 views

How to get numeric month from date (the date format: both D.MM.YYYY and DD.MM.YYYY)?

I would like to get a numeric month to the next-to cell from Date text of previous cell. Example: 1.3.2020 should result, in new cell, as 3 I cannot use MID function due to two reasons: First is that ...
user avatar
  • 1
0 votes
1 answer
59 views

Excel Hyperlink within the same sheet

I have an excel workbook with 2 sheets. Sheet1 and Sheet2. I want to stay on sheet1 and create a hyperlink to sheet2 but I want the information that is available in sheet2 to be displayed in sheet1 ...
user avatar
-1 votes
1 answer
53 views

Excel Count Different Values With extra if

I have a large amount of data. I want to count every different data. I've found that part of my formula but I want to add ab extra if; For example, I can count different data with this formula; =SUM(...
user avatar
  • 1
0 votes
1 answer
52 views

excel 2013 conditional formatting is forcing absolute

I am trying to use relative cell expression but when I do it automatically makes it absolute no matter what I try. before apply When I hit apply it changes the value to =$A$5 after apply
user avatar
0 votes
1 answer
57 views

Highlight all the cells if the keyword in the cell matches to any value of a list

For one of my excel sheet, it is required to highlight the cells that has specific keyword in it. To solve this, i have created a list called my_list which contains value1, value2 & value3. I have ...
user avatar
1 vote
1 answer
105 views

how to check for returning customers in Excel 2013?

I have the following sales data, split by customer and year: 2015 2016 2017 2018 2019 2020 2021 expected result customer 1 100 0 0 100 0 0 0 2018 customer 2 100 0 0 100 100 0 0 2018 customer 3 100 ...
user avatar
  • 123
0 votes
1 answer
102 views

How to I get Excel to take a user id in one table, match it in another, return only the most recent value, and ignore older values?

I need Excel to take the userID from Table A, match it to Table B and return the most recent SaleDate only, and ignore older SaleDate entries. In addition, I need to also return the corresponding &...
user avatar
5 votes
3 answers
416 views

Excel: Get range address from single input (looking for compact formula)

I'm trying to get an absolute range address from single input. Something like this. Formula =SOME.MAGIC(A1:B10) Output (string) "$A$1:$B$10" Attempt 1 I'm able to get this result but using ...
user avatar
  • 820
0 votes
1 answer
32 views

Excel Power Query - M Language - Reference to "ThisTable"

As total Power Query newbie I'm trying to create Power Query function which adds an Index Column to the table. But I'm struggling with passing a table reference to AddIndexColumn function What I tried:...
user avatar
  • 820
0 votes
2 answers
85 views

Excel - (Column) chart - Z-index of data series according to actual value

I have a column chart with multiple data series. For example: I don't want to have columns next to each other so I set reduced Series overlap and Gap width in Format Data Series... Now the Series ...
user avatar
  • 820
1 vote
0 answers
243 views

Excel (2013) Power Query: [Expression.Error]: The key did not match any rows in the table - what files(s) is source of this error

I have tens of Excel files with similar table (means: same name, same headers, different content). I'd like to merge all data from these tables to one big table for future processing. I did it many ...
user avatar
  • 820
0 votes
2 answers
223 views

Merge Excel rows based on common column values

I have a table similar to the image below. Column A has repeated values, while column B has values corresponding to column A. e.g. a single customer can have multiple orders. What I would like to do ...
user avatar
  • 141
0 votes
2 answers
766 views

Excel: How to graph a multiple line chart by date and country

I have the following dataset that I would like to graph in excel.Dataset The data is from OurWorldInData.org if you need the full dataset. It has a country, date, and new cases columns. I would like ...
user avatar
  • 5
0 votes
1 answer
172 views

Excel: Drop down with month instances

Is there a possibility to have a drop down with months inside, when choosing one month to show me all instances that are within that month? Something similar has Excel format Tables, when you choose ...
user avatar
  • 121
0 votes
1 answer
17 views

conditional formatting SEARCH and LEN

I just want to ask a help for my excel formula. I was having a hard time by trying a conditional formatting and formula these scenario: To Search and highlight a cell that doesn't start with "S&...
user avatar
0 votes
2 answers
55 views

Excel: Return all matches in columns using one criteria (from drop down)

I am having two tables, one table is main data, separated into many columns and blocks per 10 rows in one, this is fixed. In Column A are unique numbers that will be used in drop down. Table 1 On ...
user avatar
  • 121
0 votes
0 answers
34 views

Excel matrix formula has to return matrix (vector), but returns single (scalar) value

Yes, I read: Excel: Matrix formula - Why isn't matrix (vector) returned? but this is not the same. Basically I can't figure out why is formula like this: {=MATCH({1,2,3},{1,2,3},0)} which usually ...
user avatar
  • 820
0 votes
0 answers
36 views

Corrupted excel files are zero-byted

We have an issue at work with specific Excel files which seem to have been tampered with. Well, those Excel files are stored in a folder which is accessible on the work network. We are not sure about ...
user avatar
0 votes
1 answer
157 views

How to find specific cell containing data

I have spreadsheet A that is very large (10,000 cells for example). I also have spreadsheet B. I want to use a formula that can locate the specific location of data I enter in a specific and store it ...
user avatar
0 votes
1 answer
27 views

Hide all columns except the one which has a header matching a specific cell value

I want to see data of the only branch which I selected in C2, except all remain hidden. I have different branches' data e.g. ABT, BWP, and SWL. In cell C2 I have made a drop-down list by using the ...
user avatar
  • 3
0 votes
1 answer
59 views

Conditional formatting for specific group of values/rows based on cell value(s)

I am having some data table that is separated in lets say "blocks" of rows. As you can see on the image, starting from Column D (A1) there are some values in first row, and then 5 rows empty ...
user avatar
  • 121
0 votes
1 answer
30 views

IF FORMULA FOR EXPIRE-NEAR EXPIRE AND FRESH

Hope you all are doing well. I need a formula for one of my sheets having data of different medicines. I have to bifurcate products by their expiry status. Fresh, Near Expired, Expired. Here I get to ...
user avatar
  • 3
0 votes
1 answer
21 views

Sum first values of non-unique data in different category

I have a table consisting of 3 types of costs and each type of these costs has several cost IDs, as below: Cost ID Amount Cost Type IT01 100 Direct cost IT03 300 Indirect cost IT04 400 Indirect ...
user avatar
  • 1
0 votes
2 answers
101 views

Get last value of Excel sheet

I want to get the last value of the excel sheet. An excel sheet having almost 100+ columns and I need its net total, the last figure of the report. I have attached the image and also highlighted the ...
user avatar
  • 3
0 votes
0 answers
68 views

Checking Whether Two Columns Match any Numbers from a Different Column

I was wondering if anyone knew the best way to use one column as a sort of "criteria" column by which two other columns are searched for any numbers that match that criteria column. However, ...
user avatar
  • 1
0 votes
2 answers
103 views

Excel group row if value less than number specified

I have a sample data as following: | Item | Qty | | -------- | -------------- | | A | 18 | | B | 18 | | C | 7 | | D | 20 | | E | 13 |...
user avatar
0 votes
1 answer
31 views

How can I get SUMIFS to start over when there is a change of identifying data in a row? (to sum by "groups")

Currently SUMIFS calculates as intended for 200,000+ rows. There is one exception I can't figure out, when data is the same for 10 rows, then the 11th row changes and then row 12 through 21 contains ...
user avatar
0 votes
1 answer
128 views

Excel - count unique days in a start-end date range

Happy new year! I have an Excel filled with start-end dates (A2:B7). My interest is to try to get unique days in an interval. I've made a research but I can't seem to find any solution to this, most ...
user avatar
0 votes
1 answer
56 views

Excel formula with identifying %change on repeated sites and multiple years

I'm looking for an Excel formula to drag down on a large dataset. My issue is I have a couple hundred repeated sites with various numbers of years (see subset Excel table example). In the first years' ...
user avatar
0 votes
1 answer
27 views

Excel formula for repeated sites

I'm looking for some help with a formula. I have a dataset with repeated sites but with different number of visits per year. I could just do a simple formula for each separate site however, I have a ...
user avatar
0 votes
0 answers
124 views

Cannot connect to OData with Excel 2013 without PowerQuery

I need to connect to Odata sources using Excel 2013 in an environment where we are not able to install PowerQuery on most user machines. Excel 2013 has an option for getting data from Odata feeds, but ...
user avatar
0 votes
2 answers
317 views

Remove duplicate character in row in Excel

I'm trying to make it possible to only type "x" (without the quotes) once in every row. I would rather use data validation, but if that's not possible, I also could use VBA. I have tried ...
user avatar

1
2 3 4 5
33