Note: The other languages of the website are Google-translated. Back to English
Login  \/ 
x
or
x
Registrati  \/ 
x

or

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


freccia blu freccia destraUtilizzando 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:

doc-riempi-celle-vuote-con-valore-sopra2

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:

doc-riempi-celle-vuote-con-valore-sopra3

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

doc-riempi-celle-vuote-con-valore-sopra4

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:

doc-riempi-celle-vuote-con-valore-sopra5

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


freccia blu freccia destraRiempi 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.

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:

doc-riempi-celle-vuote-con-valore-sopra7

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!


freccia blu freccia destraRiempi 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:

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

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

doc-riempi-celle-vuote-con-valore-sopra8 doc-unione-più-cartelle-di-lavoro-freccia2 doc-riempi-celle-vuote-con-valore-sopra9

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!


freccia blu freccia destraRiempi 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.
scheda kte 201905

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!
fondo officetab
Say something here...
symbols left.
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
  • To post as a guest, your comment is unpublished.
    Rex Chaney · 1 years ago
    Very Helpful for managing bulk data
  • To post as a guest, your comment is unpublished.
    Imran Noori · 1 years ago
    very good information.thanks
  • To post as a guest, your comment is unpublished.
    Paulo Thuler · 1 years ago
    Thank you very much! Amazing!
  • 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.
  • To post as a guest, your comment is unpublished.
    roman.kultajev@oregonlearning.org · 2 years ago
    Great! Thank you!
  • 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.
  • 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?
  • 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.
  • 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!
  • 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!!
  • 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.
  • To post as a guest, your comment is unpublished.
    Cameron · 3 years ago
    thanks so much! Saved me an hour at least....
  • 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..
  • To post as a guest, your comment is unpublished.
    Paula · 4 years ago
    Thank you! Was really helpful!!
  • 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
  • To post as a guest, your comment is unpublished.
    Elisa López · 4 years ago
    Best excel tip ever! Thank you so much!
  • 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
  • To post as a guest, your comment is unpublished.
    sreeja · 4 years ago
    Thank you!! saved my time!!!
  • To post as a guest, your comment is unpublished.
    James · 4 years ago
    A real time saver - Thank you!!
  • 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.
  • 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
  • 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......
  • 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
  • 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.
  • To post as a guest, your comment is unpublished.
    suresh · 4 years ago
    Thank you so much, for this shortcuts
  • 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)
  • To post as a guest, your comment is unpublished.
    anusri · 5 years ago
    Eyeopening!! thanks a lot, thats such a relief
  • To post as a guest, your comment is unpublished.
    JB · 5 years ago
    This is awesome and helped me a lot. Thank you.
  • 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!
  • 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?
  • 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!
  • To post as a guest, your comment is unpublished.
    Indramani · 5 years ago
    Amazing trick.
    This trick will save my lot of time.
    Thanks.
  • 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
  • To post as a guest, your comment is unpublished.
    Ani · 5 years ago
    Saved ages of time. Works great!!! Thanks for sharing
  • To post as a guest, your comment is unpublished.
    sunitha · 5 years ago
    thanku so much , it saved my time
  • 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
  • To post as a guest, your comment is unpublished.
    Lucky · 5 years ago
    Thank you very much! A real time saver.
  • 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 =)
  • 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?
    • 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
  • 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
  • To post as a guest, your comment is unpublished.
    Sheryl · 5 years ago
    Thank you very much! A real time saver.
  • 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.
  • To post as a guest, your comment is unpublished.
    curvygirlme · 5 years ago
    thank you - keep forgetting this formula!
  • To post as a guest, your comment is unpublished.
    Nadia · 5 years ago
    Thank you so much it's help me a lot
  • 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
  • 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.
  • 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.
  • To post as a guest, your comment is unpublished.
    Jen · 6 years ago
    THANK you!!! save me hours of work.
  • To post as a guest, your comment is unpublished.
    RAMAKRISHNA · 6 years ago
    thank this is so helpful