Free Microsoft Excel 2013 Quick Reference

Auto fill a column without dragging cell down Results

I feel daft for having to ask this, but is it possible to automatically have a formula fill a column?

Each day I have to open a file containing 20,000ish records and need to sort it in Excel (the file changes daily). I have a formula (thanks to Bryan) that will sort my file how I want it but it needs to be populated to all the rows in a table. The only way I know to do this is to put the mouse on the little square thing in the bottom right of the top cell and drag it down so that it auto-fills (and changes the row references). Is there a way of pressing a couple of buttons and having it fill down until it finds no more data in a row (or even better no more data at all as there are blank rows every 1500 records in my data)?

If I fill cells by dragging a formula down a column, Excel inserts a Fill
Option box offering a "choice of fillings" - copy, fill with format and fill
without format. Is it possible to stop this box appearing? I do not think it
was part of Office 2000.

If I fill cells by dragging a formula down a column, Excel inserts a Fill
Option box offering a "choice of fillings" - copy, fill with format and fill
without format. Is it possible to stop this box appearing? I do not think it
was part of Office 2000.

Hello everyone,

I'm kind of new with excel and I'm turning crazy with a small problem.

I need to perform some operations with about 1000 daily data contained data-cells.
So I picked a column, inserted my formula in the first cell, and then dragged it down for the one thousand following cells.
Now, I hoped excel would understand automatically the right range of data-cells to be considered in these result-cells but obviously that would have been too good...
In practice, the formula I inserted in the first cell was:
=MATR.TRASPOSTA(MATR.TRASPOSTA(T309:V309)+MATR.PRODOTTO(MATR.PRODOTTO(MATR.PRODOTTO(BA309:BC311;MATR .TRASPOSTA(P320:R324));BQ309:BU313);MATR.TRASPOSTA(AI309:AM309)))

therefore in the cell right underneath I wanted to have:
=MATR.TRASPOSTA(MATR.TRASPOSTA(T310:V310)+MATR.PRODOTTO(MATR.PRODOTTO(MATR.PRODOTTO(BA312:BC314;MATR .TRASPOSTA(P325:R329));BQ314:BU318);MATR.TRASPOSTA(AI310:AM310)))
and so on.

As I said, excel, after laughing at me, gave me:
=MATR.TRASPOSTA(MATR.TRASPOSTA(T310:V310)+MATR.PRODOTTO(MATR.PRODOTTO(MATR.PRODOTTO(BA310:BC312;MATR .TRASPOSTA(P321:R325));(BQ310:BU314));MATR.TRASPOSTA(AI310:AM310)))

My question is if it is possible to solve this small problem without manually changing the formulas or if, in any case, it is possible to tell excel how to perform copy&paste rules (for example, how do I create a column with cells:
=5+A1; =5+A6; =5+A11; =5+A16 etc.).

I'm not sure if this is an easy or tough problem but I'll love forever anyone who can save me.

Thanks
Fra

Hi

I am a total newbie with Excel, and I just need it for basic data entry.

I don't even really know the terminology for what I am trying to do, so it is making it hard to find the answer...here it is

When I enter a number into a cell, and then drag down in the column to auto fill the cells below, the number increases. I want to be able to drag without the number increasing in increment.

Where and how do I do this?

Thank-you in advance for your help!!

Haven't touched excel for like 10 years, now I find myself trying to make a little database for a text-based game I'm playing.

Here's the deal, I need to have a cell that's like

[text] [data from cell X] [data from cell Y] text

Or to be more specific, here's exactly what i'm trying to do.. This is about locations and coordinates to them in this text-based game..

A1: Big Rhino
B1: -65270
C1: 32735
I want D1 to look like: grid add bleh -65270 32735;grid bleh;grid del bleh

But naturally without having to type the coordinates in manually, I want cell references to B1 and C1 so I later can just drag down with auto fill in column D for every other location I got.

Thanks in advance.

hope you can help a novice. I can usually figure out what's needed on my own, but this has wasted most of my day without getting anywhere.

I'm just trying to speed up the process of completing a compilation report every day. We'll say A2:A10 is a list of names, B2:Q10 is blanks to fill in...many of which I already have formulas in to figure variance to the prior day (tab), or percentage, totals, etc.

The data I need to fill in the blanks comes from multiple pivot tables, copied and pasted onto one sheet. As is, I have to manually enter the numbers needed, but I want to paste it below the main report and have it auto-fill.

To do that I need to come up with a way to have a formula in B2:B10 say that (for B2),
IF A2 = A15:25, then B2 needs to equal B?? - the corresponding cell. (If Joe Smith is A2 and A20, then B2 needs to equal the value in B20). It's easy enough to just make B2=B20, the problem is that there are about 40 of them per column and 4 columns, and I'd much rather be able to put something in B2 and drag it down instead of entering 160 unique formulas for each blank.

I can get the names in the same spot every time for the pivot table results that I'm pasting, becasue they're alphabetical. The names in the main report are not alphabetical and can't be.

Hi,

I have an worksheet that calculates commissions and bonuses that has a running total in the last column. I have a column of subtotals before this one that calculates different types of payments. (The running total is for the purposes of checking against the statements to be sure everything is correct.)

When I created the spreadsheet, I used the auto fill to copy the formula down the column. From what the little I know about absolute references, it doesn't look like I have created any, so it should update, right? I don't want to have to set up my formulas all over again for the new sheets. I want to clear/delete/add rows while keeping formulas and want the ability to add rows without either dragging or manually entering formulas.

Here is how I have it set up. Column AA is the column of subtotals. I have it set to add up the different types of payments that are received on each statements. The formulas for column AA is as follows:

=SUM(L3,P3,Q3,W3,Z3)
=SUM(L4,P4,Q4,W4,Z4)
=SUM(L5,P5,Q5,W5,Z5)
etc.

Here is how I set up column AB. (The data starts in row 3, and row 2 does not contain any values.) Starting on cell AB3:

=SUM(AB2,AA3)
=SUM(AB3,AA4)
=SUM(AB4,AA5)
etc.

I am having trouble keeping my formulas both when I delete the contents of cells/rows and when I try to insert rows. I would like to create a new workbook based on the old one and copied the sheet to a new book. I'm outlining the two different issues below:

Deletion/clear contents problem:

When I delete a row, I get a cell reference error. Example, if I delete row 4, the formula in cell AB4 is =SUM(#REF!,AA4). That cell and the rest of the cells then show #REF!. The formulas in the other cells have the correct references. So cell AB5 is =SUM(AB4,AA5). I know #REF! error in AB5 is there because it can't reference cell AB4, but it's odd that it updates and doesn't display the formula in one of these ways: =SUM(#REF!,AA5), or =SUM(#REF!,AA6), or =SUM(AB5,AA6). I know I can fix this by entering the right cell where it says #REF!, but I want it to auto update so I don't have to do this each time I create a new sheet.

When I delete the contents of a cell, the formulas disappear. If I clear the contents of row 4 and want to update the formula, I have to drag the formula all over again. Thankfully, for this one, the formulas in the following rows do not change.

Insertion problem:

When I insert a row, formulas are not updated. For example, if I insert a row above row 4, there is no formula in the new row; the formula in the new cell AB4 is blank. [I want it to update to be =SUM(AB3,AA4).] Also, the formula in the cell that was moved down does not update properly. It should update to be =SUM(AB4,AA5), but instead it reads =SUM(AB3,AA5).

Additional info:

Things seemed to update okay on this computer yesterday, but I don't recall if I tested these actions. When I sent the document to myself and opened it on another computer, that's when I had these problems with the formulas. (We're not networked.) I made some minor changes and sent the document to myself again at this computer, and I'm having the same problem. I don't know if there is a setting or something that I need to change or what, but I need some help.

Sorry for the long post, and thank you in advance for any help/advice you can offer.

Meredith

Some random questions, can't find anything that answers these specifically so apologies if they have already been answered.

I work with an awful lot of vlookup queries, however sometimes when vlooking up from one workbook to another, when I drag for a table array it doesn't work, I get no broken lines and nothing fills my vlookup function wizard. The only way I have around this is to copy all of the data in a sheet into a blank sheet in the same workbook I am using (I lookup from many workbooks). It seems to occur randomly, I assume this is down to some formatting somewhere but for the life of my I can't find consistent instances of this to diagnose.

Conditional formatting - is there any way to see what cells have any conditions assigned to them without having to click and go through every single cell?

Security - I have a document that only myself and a few other people should edit, that many can access. Tools > Options > Security gives me the option to require a password to modify which is exactly what I want, however this happens as a user opens the document. I ideally want them to have to enter a password to save it when they're done.

On a different problem I'm locking a lot of cells and only allowing a few columns to be unlocked (with the parameters of select unlocked cells). I'm also allowing auto filter, but on potentially a huge amount of data, the Find function is key but this doesn't seem to work on locked cells. Is there any easy way round this?

Many thanks,


No luck finding an answer? You could always try Google.