# Questions tagged [microsoft-excel-2019]

The microsoft-excel-2019 tag has no usage guidance.

75
questions

1
vote

1
answer

50
views

### Nested If's with Vlookups not working in Excel

I have an MS Excel 365 (with current patches) spreadsheet that tracks mileage and tolls to various locations. I am trying to build in logic that won't populate mileage and tolls if I have 2 entries ...

0
votes

1
answer

103
views

### How to TEXTJOIN specific no of rows based on constraints?

I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process. I have columns as listed in the below screen shot and I ...

0
votes

1
answer

63
views

### How to get an exact match with VLOOKUP into the current cell

The name "Dodson" is on A5, the lookup is from B5, the table A16:B19, the value in the second column of the table, but am looking for the precise result of a VLOOKUP.
The formula I have is:
=...

0
votes

1
answer

33
views

### Where is the Sparkline tab in the Show group for Excel?

How would I display the high points?
screenshot
Copilot says:
You Apply Blue sparkline color and display the high points for the
sparklines.
Copilot Certainly! Let’s create sparklines in Excel, apply ...

1
vote

1
answer

120
views

### Disable truncation of labels of treegraph in excel

I'm creating a treegraph in Microsoft Excel, but my labels get truncated unnecessarily. See the image below:
As you can see, the second digit of the labels of the smallest rectangles, which are the ...

0
votes

0
answers

65
views

### How to turn off the automatic alignment to cell borders in microsoft excel?

In microsoft excel, the page border always automatically stick to the border of cells.
I don't know how to exactly phrase this phenomena. We know that the area in which we are able to view the data ...

0
votes

2
answers

920
views

### How to use the Offset() function when referencing the cell's tab/cell address

I am trying to use the Offset() function to return the value of a neighouring cell. Starting on the "Order" tab I would like to enter a cell reference ("source") to a "Ticker&...

1
vote

2
answers

46
views

### Lookup data corresponding to the same time for each date

I have a sheet with two columns, column A is the date and time and column B is the data:
A B
|------------------------|----|
1 | 7/21/2023 12:00:00 AM | 5 |
|-------------------...

0
votes

1
answer

57
views

### How do I repeat a function that has different size inputs?

Is there a single function that can multiply cell A1 by B1:B24 and put the results in C1:C24, then A2 by B1:B24 and put the results in C25:C48, then A3 by B1:B24 and put the results in C49:C72, etc... ...

0
votes

1
answer

29
views

### Excel 2019 Transpose: Transposing multiple columns Excel

I have an excel sheet with three columns: Country, Year and Fuel consumption per capita.
Dataset:
The country column has data for all worldwide countries. The year range for each country is from 1965 ...

0
votes

1
answer

329
views

### VLOOKUP Name Error even after using formula wizard

I'm having a really hard time trying to figure what is wrong in by VLOOKUP formula. I even used the formula wizard to try to fix it.
Here is the formula I'm using.
=VLOOKUP(A10,Melaminedetails,2,FALSE)...

0
votes

3
answers

3k
views

### Using Excel formulas to create multiple rows of data from one row

I am looking for a formula that takes the name and quantity of a table and itemizes it so the quantity amount matches the amount of rows the name appears.
An example being taking this table as input:
...

-1
votes

1
answer

158
views

### Import Data from Text File into Excel 2019

When attempting to convert from TXT to Excel as below inside Excel I can't get desired output. I have tried it with different delimiter but no luck.
I want to put the text in separate columns in ...

0
votes

1
answer

54
views

### Combining two Excel Formulas into one

I have two formulas that work correctly by themselves and am not sure how to combine them into one formula.
1st formula:
=COUNTIFS('FFRCA - FMLA Archive'!D8:D2500,">=1/1/2020",'FFRCA - ...

1
vote

2
answers

429
views

### Excel importing: converting text file with many new lines into single cells instead of multiple

I have a lot of data in a text file and I'm trying to figure out the fastest way to convert what are individual lines in the text file to a single cell entry.
Each Person has a number of entries that ...

0
votes

1
answer

2k
views

### Excel column: If cell value is 0 replace with value from cell to the left

I am an excel novice. I have started working with data strings of over 1000 rows. Some cells in a column have 0 values and must be replaced with the value of the cell to the left. Currently, I copy ...

0
votes

0
answers

99
views

### Gathering data from raw data table with multiple criteria

I am coming up empty handed trying to formulate a string to pull specific training data from a huge list.
Raw Data has over 4800 rows
Column A has over 70 Unique Names
Column B has over 74 Unique ...

0
votes

0
answers

114
views

### Take value if ID is the same

I need a formula to take a value if the ID is the same. Example image:
I have table with ID and Value.
In first row is 22222 and 15. If I change 15 (in first row) to 55, then the value in table for ...

0
votes

1
answer

50
views

### Excel count if in sum of ranges

I have a sheet like this:
| A | B
--+----+---
1 | 20 | 1
2 | 20 | 2
3 | 40 | 2
I can do =COUNTIF(A1:A3, 40), but how do I count (or any other operation) on the sum of the columns? i.e. I want to ...

0
votes

0
answers

57
views

### Is it possible to automatically match rows in Microsoft Excel?

Let's say, that I have the following table:
1
2
3
A
A
B
B
C
C
C
D
E
Is there any automatic method to match rows in those three columns? Expected result looks like this:
1
2
3
A
A
B
B
C
C
C
...

0
votes

0
answers

198
views

### Running Excel.exe as Administrator is Much Slower than as a User

On Windows 10, I have an autohotkey script that opens an Excel file. If my computer has been running for a while and I have the script run the Excel file as administrator, it takes over 60 seconds to ...

0
votes

1
answer

1k
views

### In Excel, how do I join portions from different dynamic arrays into one new dynamic array?

In Excel (but without the use of VB) how do I join some columns from one dynamic array to columns from another such that result is also a dynamic array?
Here's a very simple example. Suppose I have ...

0
votes

1
answer

1k
views

### Dividing between values in an Excel pivot table

I am attempting to use a pivot table to sort numerical values by month and year (the field 'Close/Last'), and wish run calculations that take the standard deviation of the 'Close/Last' data for each ...

0
votes

1
answer

2k
views

### Return rows ONLY if another column is equal to "Yes" Using dynamic table

So I m trying to return rows of data in a dynamic table
Here's a picture below of the data, table name is "Assignments" and I've tried this formula
if(Assignments[Shipping]="Yes", ...

0
votes

1
answer

2k
views

### Excel blurry and bold text in ribbon

I have a new computer (Windows 10 Pro N 64bit) and I installed a clean MS Office & Business 2019 (also 64bit).
There is a strange problem with Excel: some of the text in the ribbon are bold and ...

0
votes

0
answers

150
views

### How to Increase a number in INDIRECT function

I need to increase the value referred to in $C$1 at the end of the following formula while dragging down:
=INDIRECT("data!"&A$3&$C$1)
A$3 refers to A, $C$1 refers to 1. And both A$3'...

0
votes

2
answers

66
views

### Excel Index Match with adjustable rows

I am using Excel 2019 and I want to write a formula that will do the following:
search for a student name in Sheet1 (named classes) and for every class he/she took, update from Sheet2 (named Archive) ...

0
votes

2
answers

215
views

### Creating multiple values from single value + monthly increment

looking for any advice/ tips that could help with the creation of this dummy data set
From this table, want to create another table
ID MOB date(dd-mm-yyyy)
A01 2 01-07-2019
A02 3 01-02-2019
This ...

0
votes

2
answers

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

1
vote

1
answer

24k
views

### How to sum a column based on existance of value in another column

I have the following sheet:
I want a sum of individual spending by Frank, John and Nancy in the yellow cells. Each person's spending is denoted by an x. I tried the SUMIF formula, but I don't know ...

1
vote

0
answers

3k
views

### Excel 2019 default encoding for csv files

Problem
I'm using Excel 2019 and I have this .csv file(Multiple language) which is in UTF-8 encoding and when I try to open the file, Excel opens it in ASCII encoding — the english characters and ...

0
votes

1
answer

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

1
vote

0
answers

182
views

### Excel color is abnormal

The Excel setting is Dark Gray, and it turns out to be super black inexplicably when opened today, and there are some dotted lines in the table.
I checked online and there are several methods, but ...

3
votes

1
answer

1k
views

### Datalabels are missing in dynamic chart

I am trying to create a dynamic chart but the data labels are missing. I add the data labels manually to all headers and then save and close the file. When I open the file and select a different ...

0
votes

1
answer

824
views

### How do I pull data from another tab/worksheet based on a column value in Excel 2019?

I am trying to create separate sheets on the same workbook that automatically update for all our sales based on one column value which is Column D. We get data from a master sheet in the same workbook ...

0
votes

1
answer

79
views

### Issue on Excel number formatting

I'm trying to type a number eg: 65, and when i press enter it shows as 0.065. Even I have tried the basic settings still it shows like this.
How can i fix this?

0
votes

1
answer

506
views

### How to find last digit IF next column has a condition?

In Tab 1 I have 2 columns, B with the material numbers of 11 digit, and C with the commentary.
In Tab 2 I have one column (E) with the 10 material number digits.
In column F I have to find the last ...

3
votes

1
answer

6k
views

### How can I fill a shape in Radar Chart?

I'm struggling to find a way to just fill one shape in a radar chart.
An example of what I would like to have is in the image.
I tried to start from a simple radar chart and then filling the shape --&...

1
vote

1
answer

2k
views

### Alternative for a filter formula

I used a filter formula to find all data from another sheet and column, that start with "US".
In the column, I have about 5000 rows that have different data with different markets. And the ...

0
votes

1
answer

7k
views

### Why there is dotted line in Excel?

The line between D and E is dotted. Why? How can I change it to normal (eg. the line between E and F)?
I am using Excel 2019.

4
votes

0
answers

861
views

### Microsoft Excel: how to disable the "two click" requirement when Excel window doesn't have focus?

In Microsoft Excel, all versions as far as I can tell (office 2007 to 2019), implement window focus differently than any other Windows apps:
When Excel does not have focus (i.e. you're using another ...

0
votes

1
answer

3k
views

### Excel formula. Find partial text in column and return cell value from row

This seems like simple concept at least to my limited excel knowledge, but I'm unable to find a formula that does the following.
I have two columns of text, column A & column B. I want to search ...

2
votes

1
answer

1k
views

### How to identify what a shortcut key is triggering in an Excel workbook?

I've inherited a workbook and a personal macro workbook that operate in conjunction with each other (drawing on a CSV and 2 LST files) to produce a report. The spreadsheet has rudimentary instructions,...

0
votes

2
answers

3k
views

### Excel highlight cell (text) that equals one of multiple other cells

I want to format cells $C$2:$C$32 if their value (text) is equal to not one other specific cell but one out of multiple cells, let's say $C$35:$C$42.
Additionally blank cells should remain untouched.
...

1
vote

1
answer

199
views

### Excel bar graph with error bar

At this point I feel kind of stupid for asking, because I should probably be able to do this.
I want to create a graph like seen on the first image below, with each task having its own label on the x-...

1
vote

2
answers

2k
views

### How do I get the first row and column to stay visible in Microsoft Excel

When I downloaded the fatalencounters.org data (found here: "download database" ) in a .xslx file. I have Microsoft Office 19 I believe. I noticed in this spreadsheet the first row and ...

0
votes

1
answer

146
views

### I get the "You've entered too many arguments for this function” on excel

I have a table where the user adds a chemical formula by adding elements one by one (the table has 5 rows) and i'm trying to calculate the degree of unsaturation in two ways. The user has to specify ...

0
votes

4
answers

3k
views

### How do I number Excel rows with a prefix?

I need the following text displayed in column A, as follows. It just needs to be plain text.
Cell A1: "R1" {without quoutes}
Cell A2: "R2" {without quoutes}
Cell A3: "R3" ...

0
votes

1
answer

235
views

### Font substitution in Excel

I have an Excel file with cells formatted with Trade Gothic LT Std font, and my computer has Trade Gothic LT Pro installed, which I believe is basically the same. It seems Excel is substituting the ...

-1
votes

1
answer

67
views

### How to combine table 2 sheets (ข้อมูลตัวอย่าง1 and ข้อมูลตัวอย่าง2) to new table 1 sheet (Use VLOOKUP formula with multiple condition)?

I have data in Excel file to combine table 2 sheets (Have data with web : https://www.contextures.com/xlSampleData01.html)
How to combine table 2 sheets (ข้อมูลตัวอย่าง1 and ข้อมูลตัวอย่าง2) to new ...