2022 Developer Survey is open! Take survey.

Questions tagged [microsoft-excel]

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.

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

MS Excel, counting instances of strings it 2 columns

If I have Column A with multiple instances of Chicken, Pig, Cow and Column B with multiple instances of Happy, Sad, Angry. How could I create a chart like this: Picture of spreadsheet I need? I have ...
user avatar
0 votes
0 answers
13 views

How to apply a calculation on a table column with a range of value per cell

I have a large table where in the first column for some row rather than just a single value there is a range e.g. I want to apply a calculation on all the data elements, but since in the first column ...
user avatar
  • 203
0 votes
1 answer
17 views

Excel help - Formula multiple criteria on a different tab

I have shown an image of two tabs on the same worksheet - Jan and Jan P & L. The Jan P and L is a huge document with a lot of information, the Jan tab is a review of the data on the P & L. I ...
user avatar
  • 19
0 votes
1 answer
16 views

MS Excel range of values in cell between percentage

I am currently using this function: =IF(G5=80%,A32,IF(G5=85%,B32,IF(G5=90%,C32,IF(G5=95%,D32,IF(G5=100%,E32,IF(G5=101%,F32,IF(G5=102%,G32,IF(G5=103%,H32,IF(G5=104%,A33,IF(G5=105%,B33,IF(G5=106%,C33,IF(...
user avatar
  • 1
0 votes
1 answer
19 views

Is it possible to filter a numeric column for values starting with a specific number, without using a helper column?

I have an Excel data where one column has numeric values of varying lengths, with sample values like 100, 100001, 11050, 201, 222001, etc. I want to filter values starting with 1. I am able to do it ...
user avatar
0 votes
0 answers
24 views

Ctrl+Shift+Alt+F9 doesn't always recalculate

I'm using Excel 365 and I have Calculation Options set to Manual. As far as I know the Ctrl+Shift+Alt+F9 shortcut is supposed to recalculate all cells in the entire workbook, even if some of them don'...
user avatar
  • 61
0 votes
0 answers
15 views

Calculate 'unutilised purchase quantity' and 'its cost' based on 'purchase quantity utilised in its sales' of multiple items on FIFO basis in Excel

I purchased Goods A, B, C & so on multiple times in different quantities at different rates. I sold Goods A, B, C & so on multiple times in different quantities at different rates. (Selling ...
user avatar
0 votes
0 answers
34 views

Excel - How to combine columns (without concatenating them) from multiple files

I have multiple CSV files, each containing several columns, but only one of interest, column B. It's a column whose header is "Results" and each row in the column contains numeric data. For ...
user avatar
  • 1
0 votes
0 answers
34 views

Excel help formula

I need help on a formula. I have set up a worksheet with 24 tabs. 12 review sheets, one for each month. 12 sheets for the P&L each month. I want to copy and paste the p and l into the relevant ...
user avatar
  • 19
0 votes
0 answers
17 views

How to process data from leaderboards over time in excel

I have data in the form of a leaderboard over many days. The leaderboard for one day might look like: Alice 1450 Bob 120 Caitlin 11 etc With names and scores like that. The leaderboard isn't the ...
user avatar
0 votes
0 answers
50 views

How to split text from one cell into multiple cells

Hope somebody can make this task a bit easier for me. Basically, i'm transferring notes from and Excel spreadsheet into our CMS platform. My main problem is the Excel entries are all on a single line, ...
user avatar
0 votes
0 answers
24 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 ...
user avatar
  • 896
-3 votes
0 answers
16 views

Convert Weekly Off into Absent Excel VBA

Would request you to help me providing excel VBA code which will replace weekly offs and holidays into absent in Sheet 1 if employee's attendance status is absent before and after weekly offs or ...
user avatar
0 votes
0 answers
8 views

Common Add-in Folder (XLA File folder) for Excel 2016 and Excel M365

We are migrating from Office 2016 to Office M365 in Phases. Office 2016 Excel file picks the XLA file and all our calculations runs correctly. But Few folks migrated to Office M365, Excel is not able ...
user avatar
  • 101
0 votes
1 answer
31 views

Creating list of repeating qty's of all possible combinations in an array

Looking for some help on a problem. I have an array in Excel with variables in both directions, and the cells corresponding to the QTY at the intersection of each variable combination. Ex input: ...
user avatar
  • 1
0 votes
0 answers
13 views

Using data slicers and advanced filter for the same table in excel

I've got one table which I filter by some slicers. Although, I want to use also an advance filter on the already filtered table. I'm using a macro to start the advanced filtering, which can be started ...
user avatar
0 votes
1 answer
21 views

Excel Trend Lines on Categorical x value

When creating a trend line in Excel using categorical data in the x-axis, does Excel make an assumption about the values of 'x'? Maybe, by assigning arbitrary values: 1, 2,..., n to the x categories? ...
user avatar
1 vote
0 answers
32 views

How can I overlay a colour and a pattern?

Is there any way I can add a pattern onto a colored cell through conditional formatting such that the color of the cell remains visible? I am trying to do a sort of Gantt chart, where the different ...
user avatar
  • 11
0 votes
0 answers
11 views

Dynamic file path linking to another workbook

I have a series of workbooks that are stacked in sub folders (below a root folder) on a thumb drive which I want to link data to a master file in the root of the thumb drive. I would like to have a ...
user avatar
0 votes
1 answer
13 views

When making a chart, how can I split values into series based on some other column value?

I want to make a chart of bw(qnb), split into 2 series. One serie where offload=0, one where offload=1. Is there a way to specify a filter when defining a series? If I select the whole column it will ...
user avatar
  • 355
0 votes
0 answers
24 views

How to combine time interval data on excel

I have a set of data for internet usage for two people. For each person I have each time they logged in and out of the internet over a day. I want to make a bar chart/histogram where the x axis is ...
user avatar
1 vote
1 answer
29 views

Easy way to fill progressive data column in excel

I am preparing monthly report of my institution. For example: For April Vaccine name during the month utilization progressive utilization HSV 500 500 BQV 300 300 ASV 200 200 For May Vaccine name ...
user avatar
0 votes
0 answers
13 views

How to return default after auto hide empty columns when filtering rows?

I followed the solution posted in Excel - any way to auto hide empty columns when filtering rows? which works but it doesn't return to default when I clear the filter. This means that I will have to ...
user avatar
0 votes
1 answer
23 views

Wrong order of intervals in Pareto chart

I have some table with 2 columns of data. First one is project id, other is Time To Market in days (finish date minus start date). Smth like: Id TTM ID-1 23 ID-2 11 ID-3 167 ID-4 2 ID-5 40 I'm ...
user avatar
  • 101
0 votes
1 answer
28 views

Creating a Polar plot with magnitude and angle in excel

Vector plot How can I create something like this using Magnitudes and 360° angles in Excel 2016.
user avatar
  • 1
4 votes
3 answers
654 views

Use of combined comparison operators in Excel formula

How does Excel evaluate combined comparison operators to return either true or false as shown in the image below? For example, the formula =IF(4<5>6,"true","false") evaluates ...
user avatar
  • 61
0 votes
2 answers
37 views

Multi-level sorting in Excel

I have to run a report that has the data shown in the pictures. I need to sort it only by the R-A** (release) in column c then sort each release by assembly (column b). I can't sort it that way ...
user avatar
  • 1
0 votes
1 answer
16 views

How do you clear filter cache in pivot tables in Excel?

I have turned number of items to retain field to none and also tried the VBA macro in the link below. Neither worked and when I refresh my pivot table the filter still keeps items not being used. The ...
user avatar
0 votes
0 answers
41 views

How to avoid using VLOOKUP?

I have a workbook where the user can create a report then move it to a new workbook. This one takes up less than 1MB of space, and works well on my own Desktop. In this report there are approximately ...
user avatar
0 votes
3 answers
45 views

Excel: delimited data in two columns, match elements between columns

Hi everyone and thank you in advance for any insights My Excel data looks as below Cell A1 Cell B1 x | y | z 1 | 2 | 3 I need to match between the two and create a dataset ...
user avatar
  • 1
0 votes
1 answer
26 views

Merging two lists in Excel

The Situation: I have two teams both on separate sheets of an excel document writing out the info of people they talk to each day (Name, customer number, Date etc). The third team want a sheet with ...
user avatar
1 vote
1 answer
21 views

Calculate average score with missing values

I have an Excel-sheet with student grades, ranging from 0 to 5. If a student didn't submit a solution for the test he/she gets a score of 'NA'. My sheet looks like the following: student test 1 test ...
user avatar
  • 13
0 votes
1 answer
16 views

Line Chart vertical grid lines only for aggregated values (days, instead of hours)

I have two columns: day of week, hours (for each day) and number of things occurring on each hour of a specific day of week. Below an example with first two days: I want to plot these data as a line ...
user avatar
0 votes
0 answers
27 views

Value in Formula Bar not shown correctly

I have a column in Excel that contains the following values : However, when I click on the cell that contains '01' for example, the formula bar shows '1'. Is there any way to have '01' displayed in ...
user avatar
  • 113
0 votes
0 answers
18 views

Formula to automatically distribute an amount across multiple cells where the max value of each cell and the order of distribution can be defined

EXCEL Question: I have 5 cells that I need to distribute the sum 1,000 into. I need to do this in a specific order (the 5 cells each have a numerical value that I can can be used to order the ...
user avatar
  • 1
2 votes
2 answers
38 views

Excel counting the number of entries filtered over categories, excluding duplicates

I'm counting the attendance to the series of events. Each event participants' data is stored in different sheet, with the identical form: Name Surname Institution Institution type - dropdown list ...
user avatar
  • 21
0 votes
0 answers
20 views

Which function to use for adding and multiplying cells?

I need to add up and multiply some cells but only if a number in a certain cell is in range from one value to another. Can somebody help me which function do I need to use and how? Here is what I need....
user avatar
  • 1
0 votes
1 answer
35 views

In Excel why use INT instead of MOD function to reduce value to a range?

In the Excel example on page 23 of Practical Astronomy with your Calculator or Spreadsheet (by Duffett-Smith and Zwart), cell C13 equals -465.9862462 hours. In order to reduce this value to the range ...
user avatar
  • 103
0 votes
0 answers
22 views

Possible to implement more code, so that highligted dates does'nt show until "in progress" is written

I am coordinating a large amount of production orders that are linked to thereown projects. In the worksheet that I am working on I have created a calendar which I have managed to highlight weekend ...
user avatar
  • 33
0 votes
5 answers
36 views

How to keep a spreadsheet cell reference fixed when rows are inserted?

Say I have a Google Sheets or Microsoft Excel spreadsheet where the formula in cell A1 is "=SUM($A$5:$A$100)". Assume there is data is cells A5 through A10, but nothing (yet) in A11 through ...
user avatar
  • 4,058
0 votes
1 answer
19 views

Excel filter function - pick data column based on cell value (header)

I'd like to output concatenated email addresses in C5 based on selected category from the pick list. The questions is, can I filter Table 1 in cell A18 based on selected category in C2? "Table1[...
user avatar
  • 174
0 votes
3 answers
44 views

LOOKUP Column Value based on right-most value

I have a list of footage for a film that is continually changing. They are organized in rows by REELS, and the rightmost value will be the latest version. The first row are dates and the rest are ...
user avatar
0 votes
1 answer
22 views

All COM add-ins hidden after closing last Excel Window

Following an update of Office 365 in March or April 2022, my COM Add-ins for Excel started to disappear: Restart your computer, run Excel and choose new blank workbook: all COM add-ins show as ...
user avatar
  • 101
0 votes
2 answers
18 views

EXCEL conditional formatting

I have a worksheet where I am attempting to have a row highlight if a specific cell in the same row is not blank. Is there a way to apply that rule to the entire worksheet without formatting each ...
user avatar
0 votes
1 answer
33 views

Excel File with more than 100k rows has errors

i would like to know if there is a way to fix excel's problem: i have a file with more than 100k rows, and everytime i am using filters in columns it doesn't really show everything after, there is a ...
user avatar
-1 votes
0 answers
42 views

Regarding M S Excel-VBA

I have an Excel workbook containing three worksheets. ITEM LIST Contains four columns - SR.NO, ITEM CODE, ITEM NAME and SALES PRICE. ITEM CODE and ITEM NAME should be locked (non-editable) ITEM ...
user avatar
0 votes
1 answer
20 views

Leftmost border on Excel doc doesn't show in print preview / won't print

I have had the issue where the leftmost or topmost border on my Excel sheet doesn't show in print preview & also doesn't print for some time, and I'm pretty sick of it! I've tried fitting columns /...
user avatar
  • 1
0 votes
0 answers
25 views

How to unhide rows in an Excel spreadsheet depending on the contents of a separate cell using VBA

I'm working on a checklist for work, and I want to be able to unhide rows below a question based on if the answer to the question is yes or no. I.e. if it's yes, unhide these rows, if its no, keep ...
user avatar
0 votes
1 answer
27 views

Pivot calculated field of variation

I want to create a pivot graph that shows the variation (%) in number of road accidents from year to year+1. I need it to be pivot because I want it to be refreshable if data change. My data is a list ...
user avatar
1 vote
3 answers
69 views

Avoid Excel to chop off the last digits of my numbers

As indicated in this URL, the largest number Excel can handle is about 10^307. Nevertheless, Excel seems to have problems with smaller numbers too (about 10^17) as you can see from my example: I'm ...
user avatar
  • 1,462

1
2 3 4 5
474