Come riempire le celle vuote con il valore sopra / sotto / sinistra / destra in Excel?

doc-riempi-celle-vuote-con-valore-sopra1

Quando impostiamo i dati in Excel, lasciamo sempre una cella vuota se i dati per quella cella sono gli stessi della cella sopra. Ad esempio, ho un foglio di lavoro come lo screenshot a sinistra mostrato:

Questo modulo sembra più ordinato e gradevole se c'è solo una riga di intestazione invece di molte righe ripetute. Ma anche questo può causare alcuni problemi, non possiamo usare funzionalità come SOTTOTOTOLI di Excel o tabelle pivot di Excel. In questo caso, dobbiamo riempire le celle vuote. Ecco alcuni trucchi per riempire le celle vuote con il valore sopra in Excel.

Riempi le celle vuote con il valore sopra con la formula

Riempi le celle vuote con il valore sopra con Kutools per Excel

Riempi le celle vuote con un valore specifico o 0 con Kutools per Excel

Utilizzando una formula per riempire le celle vuote con il valore sopra

Con questo metodo, esegui le seguenti operazioni:

1. Seleziona l'intervallo che contiene celle vuote che devi riempire.

2. Clic Home > Trova e seleziona > Vai allo speciale ..., E Vai a speciale apparirà la finestra di dialogo, quindi selezionare Blanks opzione. Vedi screenshot:

3. Clic OKe tutte le celle vuote sono state selezionate. Quindi inserisci la formula "= A2 "Nella cella attiva A3 senza modificare la selezione. Questo riferimento di cella può essere modificato in base alle tue esigenze. Vedi screenshot:

4. stampa Ctrl + Invio, Excel copierà la rispettiva formula in tutte le celle vuote. Vedi screenshot:

5. A questo punto, i contenuti riempiti sono formule e dobbiamo convertire le formali in valori. Quindi selezionare l'intero intervallo, copiarlo e quindi premere Ctrl + Alt + V attivare il Incolla speciale… la finestra di dialogo. Quindi seleziona Valori opzione da IncollaE selezionare Nessuna opzione da operazione. Vedi screenshot:

6. Quindi fare clic OK. E tutte le formule sono state convertite in valori.

Riempi le celle vuote con il valore sopra con Kutools per Excel

Con l'aiuto di Kutools for Excel, puoi riempire le celle vuote con il valore sopra, belpw, sinistra o destra rapidamente e comodamente.

Kutools for Excel : con oltre 300 utili componenti aggiuntivi di Excel, da provare gratuitamente senza limitazioni in 30 giorni.

Vai a Download
Prova gratuita di 30 giorni Acquista
PayPal / MyCommerce

Dopo l'installazione Kutools for Excel, si prega di fare come segue:

1. Seleziona l'intervallo in cui desideri riempire le celle vuote. E clicca Kutools > inserire > Riempi le celle vuote. Vedi screenshot:

2. Nel Riempi le celle vuote finestra di dialogo, fare clic su Basato su valori da Riempire cone controlla giù da Opzioni. Vedi screenshot:

3. Quindi fare clic OK or Iscriviti. E tutte le celle vuote sono state riempite con il valore sopra. Vedi screenshot:

doc-riempi-celle-vuote-con-valore-sopra8

doc-unione-più-cartelle-di-lavoro-freccia2

doc-riempi-celle-vuote-con-valore-sopra9

Scarica subito Kutools per Excel!

Riempi le celle vuote con un valore specifico o 0 con Kutools per Excel

Il progetto Riempi le celle vuote è uno strumento multifunzionale non solo può riempire celle vuote con il valore sopra, ma può anche riempire celle vuote con un valore specifico o 0 di cui hai bisogno.

1. Seleziona l'intervallo in cui desideri riempire le celle vuote con un valore specifico.

2. Applica questa funzione facendo clic Kutools > inserire > Riempi le celle vuote, Nella Riempi le celle vuote finestra di dialogo, selezionare Valore fisso sotto il Riempire con sezione, quindi inserisci il valore che desideri riempire gli spazi vuoti nel file Valore pieno casella di testo, vedi screenshot:

3. E poi clicca Ok or Iscriviti pulsante, il valore specifico verrà inserito nelle celle vuote contemporaneamente. Vedi screenshot:

Osservazioni:: Questa utility può anche riempire celle vuote in base a valori lineari, dall'alto verso il basso o da sinistra a destra.

Scarica subito Kutools per Excel!

Riempi le celle vuote con il valore sopra / valori lineari / valore specifico

Kutools for Excel: con più di 300 utili componenti aggiuntivi di Excel, liberi di provare senza limitazioni in 30 giorni. Scarica e prova gratuita ora!

Articoli correlati:

Come riempire le celle vuote con valori lineari in Excel?

Come riempire le celle vuote con 0 o un altro valore specifico in Excel?

I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
Più di 300 potenti funzionalità. Supporta Office / Excel 2007-2019 e 365. Supporta tutte le lingue. Facile distribuzione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.

Leggi di più... Download gratuito... Acquista...

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
Aumenta la produttività del 50% e riduce centinaia di clic del mouse ogni giorno!

Leggi di più... Download gratuito... Acquista...

95 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
zuhairacc · 1 months ago
Thanks i tried and it worked for me
- Reply
To post as a guest, your comment is unpublished.
Rex Chaney · 1 years ago
Very Helpful for managing bulk data
- Reply
To post as a guest, your comment is unpublished.
Imran Noori · 1 years ago
very good information.thanks
- Reply
To post as a guest, your comment is unpublished.
Paulo Thuler · 1 years ago
Thank you very much! Amazing!
- Reply
To post as a guest, your comment is unpublished.
Elle L · 1 years ago
Excellent instructions, great illustration and best of all, easy to follow.
- Reply
To post as a guest, your comment is unpublished.
roman.kultajev@oregonlearning.org · 2 years ago
Great! Thank you!
- Reply
To post as a guest, your comment is unpublished.
pschultz@afcinc.org · 2 years ago
Never mind. i figured out my mistake. This does not work -->Then input the formula “=A2 ” into active cell A3 without changing the selection If you click in an "active cell" you lose your special selection. Instead highlight the cells to be included starting with the first cell you wish to change, then follow the above procedures to find and select Special and BLANKS, then type the formula in the Excel formula box NOT in the cell itself. This worked for me. Great tip! Using it to prepare report for our annual accounting audit.
- Reply
To post as a guest, your comment is unpublished.
pschultz@afcinc.org · 2 years ago
I tried the formula method and it did NOT work for me. I selected Special and Blanks, I entered the formula and hit ctrl enter and only the cell I was in changed. What am I doing wrong?
- Reply
To post as a guest, your comment is unpublished.
Raymond · 3 years ago
Thanks, this saved me about 3 hours. This is so good, I'm still reeling from the awesomeness.
- Reply
To post as a guest, your comment is unpublished.
Jordan · 3 years ago
Thank you for the tutorial. Your Excel quick tips saved the day!
- Reply
To post as a guest, your comment is unpublished.
Alberto · 3 years ago
Awesome... you saved my day :) I cannot thank you enough!
Maybe the only thing to add is the following watch out:
- when the data set comes from merged cells, when you click unmerge, office will create cells that are not fully blanks. The Go To > Special > Blanks will not work.
- to overcome that:
1. Unmerge cells
2. Select columns and click data > text to columns
3. Proceed with Go To > Special > Blanks

Hope this helps!

Thanks!!
- Reply
To post as a guest, your comment is unpublished.
Gultasab · 3 years ago
Thanks brother, for an Estimator you saved a 50% of time by sharing this brillient trick.
- Reply
To post as a guest, your comment is unpublished.
Cameron · 3 years ago
thanks so much! Saved me an hour at least....
- Reply
To post as a guest, your comment is unpublished.
nahid · 4 years ago
How can copy below cell value to upper cell. like ctrl d works..
- Reply
To post as a guest, your comment is unpublished.
Paula · 4 years ago
Thank you! Was really helpful!!
- Reply
To post as a guest, your comment is unpublished.
Chan Myae Htau · 4 years ago
This trick is help me. And I think it will be help on problem of other excel users.
Thanks
Chan Myae Htay
- Reply
To post as a guest, your comment is unpublished.
Elisa López · 4 years ago
Best excel tip ever! Thank you so much!
- Reply
To post as a guest, your comment is unpublished.
Daniel · 4 years ago
Can I save my drop down list formula? When I close my excel and open again I do not get the drop down list i created. Thanks
- Reply
To post as a guest, your comment is unpublished.
sreeja · 4 years ago
Thank you!! saved my time!!!
- Reply
To post as a guest, your comment is unpublished.
James · 4 years ago
A real time saver - Thank you!!
- Reply
To post as a guest, your comment is unpublished.
Maaz Ahmad · 4 years ago
It was extremely helpful, saved my hours. i was compelled to comment to appreciate your efforts.
- Reply
To post as a guest, your comment is unpublished.
ALper · 4 years ago
You just saved my life. I mean a few more months from my life time =D thank a lot
- Reply
To post as a guest, your comment is unpublished.
sanket wazalwar · 4 years ago
Fill blank cells with value above with formula :
4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells. See screenshot:
Can somebody help me on step no 4
i am unable to copy the formula for other blank row......
- Reply
To post as a guest, your comment is unpublished.
Wendy P · 4 years ago
I have used this macro and it is very good - Insert blank rows when value changes with VBA code. I need to have 2 blank rows inserted (instead of the one) and in the 2nd blank row I need the name of the value that was used in the separation.

i am a novice at this so please can you help me
- Reply
To post as a guest, your comment is unpublished.
vipul · 4 years ago
Thank you so much. I can not describe in the word that how much grateful to you. The solution saved my labor of at least 5 hrs. Thanks once again.
- Reply
To post as a guest, your comment is unpublished.
suresh · 4 years ago
Thank you so much, for this shortcuts
- Reply
To post as a guest, your comment is unpublished.
Anuj kumar · 4 years ago
use indirect function: indirect("column_name"&row()-1) like column is A then indirect("A"&row()-1)
- Reply
To post as a guest, your comment is unpublished.
anusri · 5 years ago
Eyeopening!! thanks a lot, thats such a relief
- Reply
To post as a guest, your comment is unpublished.
JB · 5 years ago
This is awesome and helped me a lot. Thank you.
- Reply
To post as a guest, your comment is unpublished.
Vnay · 5 years ago
Awesome! Worked for me to update close to 100K rows and saved lot of time...thanks much for the tip!
- Reply
To post as a guest, your comment is unpublished.
Ana · 5 years ago
Im trying to use the KUTOOLS fill blank space based on value and it is not working, do the nunbers or values have to be en a specific format?
- Reply
To post as a guest, your comment is unpublished.
MJ · 5 years ago
Hello,
I used this formula and works wonders,but I need to change the font color to be transparent for the data that was copied below. Could you please advise on how to do that?

Thank you for your help!
- Reply
To post as a guest, your comment is unpublished.
Indramani · 5 years ago
Amazing trick.
This trick will save my lot of time.
Thanks.
- Reply
To post as a guest, your comment is unpublished.
v s raju · 5 years ago
This is really helpful and I saved lot of time
- Reply
To post as a guest, your comment is unpublished.
Ani · 5 years ago
Saved ages of time. Works great!!! Thanks for sharing
- Reply
To post as a guest, your comment is unpublished.
sunitha · 5 years ago
thanku so much , it saved my time
- Reply
To post as a guest, your comment is unpublished.
praveen · 5 years ago
thank u so much.it is real time saving. i really wasted my time before i got to konw
- Reply
To post as a guest, your comment is unpublished.
Lucky · 5 years ago
Thank you very much! A real time saver.
- Reply
To post as a guest, your comment is unpublished.
berdict · 5 years ago
How to revert it to the blank cell after I done that?
Thanks =)
- Reply
To post as a guest, your comment is unpublished.
Frank Medoff · 5 years ago
Close but not exactly what I am looking for.
Example:
Column A with random placed values. Changes every time download form server.
To setup a table or pivot table I need to fill in the blanks as mentioned by users above.
However, there are thousands of rows in the download, with data appearing randomly down the column. I need to copy the first cells data down to the last empty cell before the next cell with data. Then copy the new data down to the last empty cell above the next cell with data.
The routine ends at a specified row.
Each time the file is downloaded from the server the cells with data in column A change.
Next step, to execute this vba macro for any selected column.
The suggestions above work as long as I manually repeat the process at every time a cell with new data is encountered.
With over twenty thousand rows of data randomly interspaced the above ideas require the same time as copy and paste by hand.
Any ideas on how to automate using a vba macro?
- Reply
- To post as a guest, your comment is unpublished.
  Sue · 3 years ago
  
  Hi Frank,
  
  This is exactly the situation I have! I can't find anything to work. Did you ever get an answer to your question?
  
  Sue
  
  Reply
To post as a guest, your comment is unpublished.
EliD · 5 years ago
Great help! very performant macro. I've found other macro but very heavy when cell to fill are more then 1500. this one finish everything within a minute... 8) Thanks
- Reply
To post as a guest, your comment is unpublished.
Sheryl · 5 years ago
Thank you very much! A real time saver.
- Reply
To post as a guest, your comment is unpublished.
Justin · 5 years ago
Is there a formula that can populate column D without filling the blank cells in column A so it looks like the result below?

Name......Order....Date.....Introduce
QUE.......10......10/20.....A handy tool
*blank*...31......11/2......A handy tool
*blank*...22......11/16.....A handy tool
WEN.......23......11/25.....with this utility
*blank*...34......12/1......with this utility

Thanks for your help.
- Reply
To post as a guest, your comment is unpublished.
curvygirlme · 5 years ago
thank you - keep forgetting this formula!
- Reply
To post as a guest, your comment is unpublished.
Nadia · 5 years ago
Thank you so much it's help me a lot
- Reply
To post as a guest, your comment is unpublished.
Faisal · 6 years ago
thanks this is very helpful - one question i used the VB code and merged it with another part of my code which unmerges cells. the issue is that in the blank cells now it shows R[-1]C instead of the value in the cell above. any idea how i can fix this? I am using office 2013.

thanks
- Reply
To post as a guest, your comment is unpublished.
Andy · 6 years ago
I would probably depress myself with how much time i've wasted in the past doing this kind of thing manually, but i'm in a positive mood so i'll think about all the time it will save me in the future!

Thanks for taking the time to publish the tips.
- Reply
To post as a guest, your comment is unpublished.
Stan · 6 years ago
what are the limitation? I'm testing on one column with 95k rows and about 30k unique lines (each has about five blanks under that need to be filled). I get a #REF error. Works on about 10k lines.

I guess will have to do a loop macro.
- Reply
To post as a guest, your comment is unpublished.
Jen · 6 years ago
THANK you!!! save me hours of work.
- Reply
To post as a guest, your comment is unpublished.
RAMAKRISHNA · 6 years ago
thank this is so helpful
- Reply

Accedi al tuo account

Password Reset