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)

1
vote
1answer
188 views

How to count unique numbers based on month?

Column A include a list of numbers, some are duplicated Column B include a list of numbers from 1-12 that represent the months I need to count the unique numbers in column A where the month in ...
1
vote
1answer
56 views

Matching Zip Codes with various formatting

I have a few tables I'm trying to combine on the zip code field. My problem is that the zip codes are in no consistent format at all. I've been spending a lot of time trying to clean the data, and I ...
1
vote
1answer
791 views

How do you copy the value from one cell in one worksheets to two cells in consecutive rows in another worksheet by dragging down?

How do you copy the value from one cell in one worksheets to two cells in consecutive rows in another worksheet by dragging down? Example: Worksheet 1: 1 2 3 Worksheet 2: 1 1 2 2 3 3
1
vote
1answer
473 views

Excel Conditional Formatting Colour Scales highlight group of cells when another cell is a certain value

So I have a document that has 2 drop downs with the first acting as a currency selection and the other acting as a discount selection (0% - 50%). Basically whenever I select from the dropdowns the ...
1
vote
1answer
159 views

How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...
1
vote
1answer
51 views

Conditional formatting excel conundrum

I have a list of values in excel. I would like to highlight a cell if its more than 1.5 higher or lower than the value before it or after it. Is this possible? Thanks guys!!
1
vote
1answer
38 views

Looking up the same value in a row and returning multipe corresponding values

I am trying to lookup value 1 in the example below and return corresponding column name. When I use index match, it only looks up the first column which corresponds to 1 in the below example, it looks ...
1
vote
1answer
3k views

PowerView to PowerPoint?

Is there a way to export PowerView slides/tabs to PowerPoint? I realize there are a couple articles on how to export PowerView from Sharepoint, but wondering if there is a way to do this for ...
1
vote
1answer
100 views

How to Force 0 show in excel cell for ROUNDDOWN?

Good day I have the following Excel formula: =ROUNDDOWN(C30/4;0) In Cell (C30) is: 3 In the resulting cell it shows an empty cell. I would like the cell to show 0 Note: My Excel version is ...
1
vote
1answer
33 views

Trying to count names across multiple rows for work scheduling purposes

I have an excel sheet that looks something like this: +--------+--------+--------+--------+ | shift | Staff1 | Staff2 | Staff3 | +--------+--------+--------+--------+ | 2hours | john | bob | ...
1
vote
1answer
310 views

Excel Edit Mode: Time without seconds

Is is possible to show only hours and minutes when editing a time cell? Something which bothers me for a long time: I have a sheet to hold my working times. Everytime I want to change a time shown ...
1
vote
1answer
630 views

How to have a drop-down in a cell only if certain cells have text

I am trying to put a drop-down in one cell with options "Open" or "Closed." However, I don't want the user to be able to select from the drop-down unless they have entered text in cells A2:G2. I ...
1
vote
1answer
1k views

Hide/Unhide rows based on more than one cell value

Please help me I am using the following code to hide rows if cell values are 0: Private Sub Worksheet_Calculate() Dim LastRow As Long, c As Range Application.EnableEvents = False LastRow = Cells(...
1
vote
1answer
2k views

Excel 2007 or 2010 copy/paste without merging cells & with shift all down

Let say I have an excel sheet with structure like I select a few cells (at one column) and click Ctrl+C. After that I want to paste it. I select one cell(B116): And click Ctrl+V. It gives me ...
1
vote
1answer
180 views

How to plot excel chart with multiple series from single data source

How do I plot the following multiple series information on a single chart: DateTime SeriesName Duration 2014-05-02_10:21:21 X 2013.006 2014-05-02_10:21:22 Y ...
1
vote
1answer
108 views

How to get reference to the only datatable in the active sheet

I am adding a new row to the table I have in the active sheet using the following method: ActiveSheet.ListObjects("MyDataTable1").ListRows.Add(AlwaysInsert:=True) The problem is that it is ...
1
vote
1answer
654 views

Unable to format datetime in Excel Table

I have an Excel document with a table linked to a table in sql server containing a datetime field. The datetimes display as yyyy-mm-dd hh:mm:ss.sss and I'm unable to format the cells directly to a ...
1
vote
1answer
1k views

How do I create a dynamically-growing graph which ignores empty tail cells?

I have a table on an Excel 2010 Worksheet 'Consolidated Data' where: Column A is filled with arbitrary dates sorted by increasing age Column F has numeric data relevant to this question Row 1 is the ...
1
vote
1answer
272 views

How do I sort and merge data (in Excel) with hexadecimal codes?

I take attendance in large, lecture classes. Each student inputs a vote on a multiple-choice question with a "clicker" (a hand-held, remote control device), and each clicker has a 6-digit, ...
1
vote
1answer
44 views

Editing Excel cells with a different format while keeping the current format

Say I have cells in Excel with text in it in subscript format. When I want to add more text in the cells, it will appear in subscript format. To make them appear in normal format, I have to start ...
1
vote
1answer
276 views

MS Excel - is it possible to change from writeable to read-only?

I have a question if it is possible while using MS Excel spreadsheet to change how it's open from writeable to read-only without closing it? I am working on the network file and sometimes when ...
1
vote
1answer
1k views

How do I link to excel chart data in PowerPoint with relative paths?

This question is similar to the question posted in How do I embed video in PowerPoint with relative paths? But in my case I search for an answer to get Excel charts, which are linked in an absolute ...
1
vote
1answer
398 views

Excel output XML using XSLT

I am attempting to use xml2json-xslt to convert my Excel generated XML to JSON data. It is written in XSL 1.0 so I can run it from the browser and it works just fine. However I would like to include ...
1
vote
1answer
613 views

Excel 2007 VBA code to aggregate data based on conditions and calculate a weighted average

I am importing data from a trading system that has buy and sell orders for multiple securities(stocks). I need to aggregate the same security buy orders and the same security sell orders, creating a ...
1
vote
1answer
3k views

Scroll Lock key on for keyboard lights but off for Excel

My keyboard uses the Scroll Lock key to turn the lights for the keys and key background on and off. Excel uses the same key to control movement when using the arrow keys. How can I "tell" Excel the ...
1
vote
1answer
199 views

How can I copy cell contents from multiple sheets at once?

I would like to select cells in each sheet in my workbook and then copy them all at once, is that possible? Or even better, if I could mark those cells (group perhaps) so that I could get the SUM of ...
1
vote
1answer
566 views

Excel pivot tables: reverse running total

Is it possible to create a reverse running total in an Excel 2010 pivot table? Using the basic example below, I have a list of number of sales per month (B), along with a running total (C). Without ...
1
vote
1answer
77 views

How to connect a date with data on a second tab in excel

I am wanting to know how to connect data with a date on another tab. on the first tab I have the date using =TODAY() in cell A1, then in cell A3 I have Passenger numbers.. On the second tab I have ...
1
vote
1answer
1k views

How do I find which Pivot Tables has an error in Excel 2010?

I'm receiving an error: cannot open PivotTable source file ...XLSX SHEET... while refreshing all data. I have many Pivot Tables, and I can't find that one with the error, while refreshing ...
1
vote
1answer
357 views

How to: edit data in excel, visualize it on a second screen whilst live?

Suppose I have the following set-up: I have a laptop connected to a screen. The screen shows whatever. The screen is in front on an audience who are asked to choose between several alternatives, say ...
1
vote
1answer
372 views

head and cat don't recognize Microsoft Excel text files

I have a large .xls file with 53760 rows and 19 columns. I saved it as Tab Delimited Text in order to pre-process it through command line tools and load it into R later. However, when I run head ...
1
vote
1answer
43 views

Combine duplicates and total in Excel

Not the greatest at Excel.....but basically my data looks like: A B C D Part Number Qty Amount TOTAL $ 0020056 1.00 1,300.00 $1,...
1
vote
1answer
57 views

eliminate blank cells in excel when extracting data

I have two worksheets. One is where the user type in the data and the other is where it gets automatically summarize the data in matrix. the problem is I don't want blank cells in the matrix when data ...
1
vote
1answer
296 views

Is there a macro to filter table by some list elements?

I have a table with entries based on a list and I used a macro I found somewhere to be able to add/remove multiple elements from the list to one cell, let me show you an example: TEST TABLE test1 ...
1
vote
1answer
155 views

How to skip empty cells while counting instances of days-of-the-week

I've been using the following function to count the instances of days of the week in a file. =SUMPRODUCT(--(WEEKDAY(E2:E145826)=1)) Now that I'm trying to count them for the entire file instead of ...
1
vote
1answer
2k views

Comparing two similar lists in excel on mac to find duplicates

I have 2 rather long lists of companies (a developing main list and several other lists which need to be compared to the main) I need to find duplicates (but also fuzzy duplicates) - e.g. "A company" ...
1
vote
1answer
143 views

How to copy exact cells into another Excel file?

I need to copy some cells from a row into another Excel sheet. The cells in target sheet are not in same row. The starting row is changing and that is why my idea is following: run a macro, choose ...
1
vote
1answer
83 views

Excel 2010 Recordset - How to insert a sequential number into an extracted ODBC recordset based on unique identifer

ColumnA ColumnB EnquiryNo VisitNo 1804 0 1804 0 1804 0 1805 0 1806 0 1807 0 1807 0 Hope you can help as I am stumped! My excel ...
1
vote
1answer
100 views

Why is only one Excel spreadsheet crippled, but others are fine?

I have an inherited spreadsheet that I really don't want to rebuild at the moment. It's a simple small workbook that is small (< 200 rows that don't even reach to AA) and does nothing more than ...
1
vote
1answer
160 views

Moving Data from One Column into Six Columns

I have an Excel sheet that has six columns that are currently all combined into one column. I need to separate them out but the issue is the first column is words that sometimes are one word and ...
1
vote
1answer
532 views

How can I connect to an InterBase database using Excel?

There is an InterBase database that I'd like to connect to using Excel. I have the database name, address, and login info. IB, as I understand it, is a form of SQL. Using the SQL connect mode under "...
1
vote
1answer
1k views

Can I output results of a MySQL query to a csv file via Putty?

I've typed the following code to output the csv file , but it shows an error mysql> SELECT *INFO OUTFILE 'd:/student.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '...
1
vote
1answer
256 views

Excel access.Vlookup? IF?

What I am looking to do, is to move stuff from one excel sheet to another sheet, in the same workbook. We will call the sheet with the data the "suppliers list" and the sheet where I want it to go, ...
1
vote
1answer
524 views

Identiying repeating digits in Excel cell

I have a list of phone numbers in excel, some of which are obviously duds because they contain repeating digits like this '5555555'. Is there any easy way of identifying these? Its Excel 2010.
1
vote
1answer
273 views

Excel 2010 formula to track records and replace them when surpassed or bettered

I've found using game data is an excellent way to learn the ins and outs of Excel. I made a workbook complete with personally made achievements (all of which work), but I can't find a formula that ...
1
vote
1answer
683 views

How to filter rows which are blank rows from a specific column onward?

I have a file with thousands of rows and need to filter it. How can I filter rows for which all columns are blank after column X? The situation is as follows: I am working with a database of ...
1
vote
1answer
75 views

Export from Access to Excel, Bosnian char š not showing completely

I hope this is the right StackExchange forum for my question. I need to export a table from Access to Excel. It has all sorts of special characters in it. Ranging from é, à, ù to ß and finally š. ...
1
vote
1answer
5k views

Automatic Excel list exported from SharePoint not keeping my local data?

hope you can help!? I have set up a table in Excel which does a great job of exporting data from a SharePoint however I have added my own columns to add (local) information to each entry. ie. I am ...
1
vote
1answer
204 views

Formulas and icons in Excel

In Microsoft Excel 2007 and am attempting to track vehicle mileage I’m looking for Excel to calculate mileage until service, from two cells, and output 3 different icons or colors to been easily ...
1
vote
1answer
3k views

Match unsorted Excel data from two columns (products) to assign value to a third column (prices)

I have four columns: Column A has a part number, column B has a new price Column C has a part number, column D has an old price. Column A and C are not identical nor do they have the same number of ...