How to delete data in Excel but not formulas?
I have a spreadsheet, lets say with columns a to z and 3000 rows of data, each field filled out. Thing is, i need to copy this data to another sheet, and re-use the current sheet, maintaining the formulas. I have for example some formulas and vlookups in columns g, h and k. If i plain delete the data, i lose the formulas. I could just delete the data in columns without formulas and leave the others but this will be time consuming. Is there some kind of delete function that will work on deleting data (values) in cells, but keep any formulas?
Very easy to do.
Press F5 to display the “Go To” box.
Select the “Special” button.
Choose the “Constants” button and remove the ticks from the Text, Logicals, and Error boxes.
“Numbers” should be the ONLY box ticked.
When you click “OK”, every numeric cell across the spreadsheet will be selected.
Now manually press “Delete” to delete the values.
If you have text in the spreadsheet aswell, you would just de-select the “Numbers” option and select the “Text” option.
That function can also be used to do many neat tricks.
Let me know if you have any problems.
Andrew
|
|
AceSoft 1830mAh Battery Desktop Charger USB Data CAR 4 Motorola TRIUMPH WX435 US $25.91 |
|
|
ADEMCO RELAY MODULE 4204 CONNECTS KEYPAD DATA BUS LINE $57.88 |
|
|
USB PC Data Cable/Cord/Lead For Nikon Coolpix S 9100 S 203 S4100 L100 S4000 $3.00 |
|
|
Red USB Data Cable Charger For iPhone 4 4G 3G iPod $1.00 |
|
|
3M 10ft Extra Long USB Data Cable Charger for iPhone 4 4G 3GS iPod Nano Touch $4.99 |
|
|
Assemblies Cost Data (Means Assemblies Cost Data) $147.09 |
|
|
OEM ORIGINAL USB CHARGER DATA CABLE FOR iPHONE 3G 3GS $11.99 |
|
|
Juniper Archer Longbow Data Collector 100m Rangefinder PDA Computer $5,495.00 |
|
|
USB Data & Charger Cable for MetroPCS Huawei M735 $3.29 |
|
|
SIEMENS AUTO HOME CHARGERS USB DATA CABLE C65 CF65 CX65 S65 CT66 S66 C75 M75 SX1 $24.98 |
|
|
Car Charger + USB Data Cable for Net10 Motorola W409g $4.99 |
|
|
Apple iPhone 2G 3G 3G-S USB Data SYNC Charger+Cable? $0.99 |
|
|
Original Micro USB Data Cable for HTC myTouch 4G Slide $6.99 |
|
|
Original Micro USB Data Cable for HTC Status $6.99 |
|
|
Original Micro USB Data Cable for HTC Explorer $6.99 |
|
|
Original Micro USB Data Cable for HTC Evo 3D $6.99 |
|
|
Original Micro USB Data Cable for HTC Radar $6.99 |
|
|
Original Micro USB Data Cable for HTC Rhyme $6.99 |
|
|
Original Micro USB Data Cable for HTC Titan $6.99 |
|
|
Original Micro USB Data Cable for HTC Merge $6.99 |
|
|
OEM Home Wall Charger+USB Data Cable for Sprint HTC EVO 4G Desire HD $0.01 |
|
|
5X New USB Sync Data Charger Cable Cord For Apple iPod iPhone 3G 3GS 4G 4S $0.99 |
|
|
15 in 1 USB Multi Charger Data Cable for iPhone HTC MP3 $1.00 |
|
|
PKH34 Honda Acura Transponder Data Override NEW $29.95 |
|
|
Analyzing Complex Survey Data (Quantitative Application $7.95 |
|
|
NEW 2 in 1 Retractable USB2.0 Charging Cable Data Cable for PSP Black $0.99 |
|
|
White Hi Speed Data Charger Cable for iPhone 3GS 4G 2G $5.98 |
|
|
Juniper Archer Longbow Data Collector 300m Rangefinder $5,995.00 |
|
|
Safco 4930CH Four-Drawer Computer Disk/Data Cabinet – Charcoal $975.99 |
|
|
Micro USB Charging Data Cable for Blackberry Bold 9900 9930 $4.99 |
|
|
Micro USB Charging Data Cable for Blackberry Curve 9360 $4.99 |
|
|
Safco 4930LG Four-Drawer Computer Disk/Data Cabinet – Lt Gray $975.99 |
|
|
White Pink Flower Rubber Case+Car Charger+Data Cable For HTC Wildfire S $6.98 |
|
|
NEW 2 in 1 USB Charging Cable Data Cable for PSP Black $0.99 |
|
|
Alpine IVA-D900, IVA-D900R, IVA-D900Re 3m Data Cable $44.00 |
|
|
Replacement USB Data Cable for Nokia 3530 3560 3590 $5.06 |
|
|
Juniper Archer Longbow Data Collector 1000m Rangefinder $6,495.00 |
|
|
OEM MICRO Data Sync USB +AC WALL HOME CHARGER FOR HTC HD2 myTouch 4G G13 G9 G15 $0.99 |
|
|
USB 2.0 Data PC Cable for Sony DCR Handycam Camcorders $0.99 |
|
|
NEW ORIGINAL Blackberry USB Data Cable 8520/8900 Curve $16.11 |
|
|
USB Power Wall Charger/Data Cable For Samsung MP3 YP-K3 $4.88 |
|
|
NEW Tandberg Data LTO-3 Barcode Label 1018565 $73.24 |
|
|
NEW Tandberg Data LTO Ultrium 4 Data Cartridge 433926 $538.99 |
|
|
USB Power Wall Charger/Data Cable For Samsung MP3 YP-S5 $4.88 |
|
|
Palm Vx Handheld PDA Data Organizer $12.99 |
|
|
NEW 3M 78-6972-0034-9 Apple Data Transfer Cable Adapter $33.24 |
|
|
USB Power Wall Charger/Data Cable For Samsung YP-P2J $4.88 |
|
|
USB Power Wall Charger/Data Cable For Samsung YP-S3Q $4.88 |
|
|
New White US Home Wall AC Charger+USB data Cable for iPod Touch Apple iphone4 4G $0.01 |
|
|
USB Power Wall Charger/Data Cable For Samsung YP-S3J $4.88 |
|
|
New USB Hard Drive Data Transfer Cable Cord + CD Software for Xbox 360 Gray $0.01 |
|
|
USB Power Wall Charger/Data Cable For Samsung MP3 YP-K5 $4.88 |
|
|
5x New USB Sync Data Charger Cable Cord For iPod iPhone 3G 3GS 4G 4S $0.01 |
|
|
HP 3760A Data Generator Service Manual $41.95 |
|
|
iMac 17″ iSight LCD SCREEN DATA VIDEO CABLE 593-0306 $24.98 |
|
|
USB Data Transfer Cable Cord For Samsung Yepp YP-Q1J $2.20 |
|
|
Chevrolet 1975 Car & Truck Data Book Album Corvette $59.99 |
|
|
USB Data Transfer Cable Cord For Samsung Yepp YP-T9B $2.20 |
|
|
Hello Kitty retractable USB Data Line Charging Cable iPod iPhone #IP102 $3.99 |
|
|
How to Use Excel® in Analytical Chemistry: And in General Scientific Data An… $92.23 |
|
|
USB Data Transfer Cable Cord For Samsung Yepp MP3 YP-T9 $2.20 |
|
|
USB Data Transfer Cable Cord For Samsung Yepp YP-P2J $2.20 |
|
|
USB Data Transfer Cable Cord For Samsung Yepp YP-S3Q $2.20 |
|
|
Star Trek Next Generations Lieutenant Commander Data Action Figure $9.99 |
|
|
USB Data Transfer Cable Cord For Samsung Yepp MP3 YP-K5 $2.20 |
|
|
Chevrolet 1998 Corvette Specialist Data Book & Brochure $94.99 |
|
|
New USB Data Snyc Cable Charger For Philips GoGear MP3 2GB 4GB 8GB VIBE $0.06 |
|
|
YAESU ADMS-VX3 ADVANCED DATA MANAGEMENT SYSTEM FOR THE VX-3R $39.00 |
|
|
NEW OEM HTC DROID INCREDIBLE 2 THUNDERBOLT WILDFIRE HD7 BEE CHARGER + DATA CABLE $5.25 |
|
|
OEM Home Wall Charger+USB Data Cable for Sprint HTC EVO 4G Desire HD $0.01 |
|
|
USB Data Cable for Net10 Motorola W409g $3.49 |
|
|
First Data FD100 Credit Card Processor w/ FD30 Keypad. $150.00 |
|
|
SuperSpeed fast data transfer rate USB 3.0 Aluminum Enclosure, 3.5″ SATA II HD $29.79 |
|
|
Samsung SCH-U350 Data Cable $3.95 |
|
|
Micro USB Data Cable + Sync Cradle Charger Dock For Samsung Galaxy S2 II i9100 $8.99 |
|
|
3pcs Mini Car Charger+2x Data Cable for Amazon Kindle Fire 7 inch Tablet $4.98 |
|
|
I-DATA LINKCOM XPRESS $15.00 |
|
|
USB 2.0 PC Data Cable/Cord/Lead For Canon Powershot XT/i A1100 IS 7DS SX30 IS $2.99 |
|
|
6PCS USB Data Sync Charger Cable for iPhone 4 4G iPod connector New $0.99 |
|
|
Car Charger+Case Cover+USB Data Cable for Net10 Motorola W409g $6.29 |
|
|
5X New USB Sync Data Charger Cable Cord For iPod iPhone $0.56 |
|
|
OEM Home Wall AC Charger+USB Data Cable for ATT Samsung Infuse 4G $4.99 |
|
|
OAKLEY THUMP PRO 2 MP3 512MB SUNGLASSES USB DATA CABLE $5.91 |
|
|
USB Charging/Data Cable for iPhone 2G/3G/3GS/4 – Blue (97CM-Length) $2.19 |
|
|
Hellerman Tyton Telephone & Data Module NIB RMD08 $20.00 |
|
|
USB Data Connector Charger Cable for Apple iPod iPhone $0.16 |
|
|
1942 Westinghouse Diary Clean no data entered except in pencil name on 1 page $3.99 |
|
|
OAKLEY THUMP RAZRWIRE PRO2 SUNGLASSES USB DATA CABLE $5.92 |
|
|
Car+Home AC Charger+Case+USB Data Cable for Sprint LG Remarq LN240 $9.99 |
|
|
For HTC EVO 4G Car Charger+Leather Case+USB Data Cable $6.99 |
|
|
Mattec Data System, Plastic Injection, Dell Computer $900.00 |
|
|
Data, Network, And Internet Communications Technolog… $152.95 |
|
|
Hello Kitty USB Data Line Charging Cable iPod iPhone #IP101 $3.99 |
|
|
Micro USB Data Sync CABLE Battery CHARGER for Sprint BlackBerry STYLE 9670 Cord $0.99 |
|
|
Listomania: A Completely New Collection of Useful and Non-useful Data… $18.49 |
|
|
iPad USB 3 Ports hub Power data & camera connection kit for Keyboard iPhone iPod $9.99 |
|
|
Micro USB Data Sync CABLE / Battery CHARGER for BlackBerry BOLD 9650 / TOUR 9630 $0.99 |
|
|
New Original OEM Apple 2g 3g 3gs 4g 4s Iphone Wall Charger USB Data Cable $10.50 |
|
|
Brand New Kingston 4GB USB 2.0 Genuine Flash Memory Drive Stick Data Traveler $6.49 |
|
|
Barco Data 708S CRT Multimedia Projector $99.99 |