Welcome to the second batch of quick Excel tips to enable you to use Excel more efficiently. This isn’t like a sequel in a film franchise, where the first film is good, and all the sequels are rubbish. The tips are presented in no particular order, so depending on how you use Excel these ten tips might be more valuable than the first ten.
Quick Excel Tip #11
This may just be the fastest way to paste values in Excel. Right-click on the border of the selected cells, drag right, drag left, release the mouse button. Click Copy Here as Values Only.
Quick Excel Tip #12
Want to hide the workings in your Excel Workbook? Use “;;;” as the custom number formatting. The value/formula is there, but it is not displayed.
Quick Excel Tip #13
Give the appearance of splitting a cell diagonally to show header descriptions for the row and column at the same time.
Quick Excel Tip #14
Use Go To Special to quickly find differences between rows or columns of data.
Quick Excel Tip #15
ALT + Enter will insert a line break into a cell. Can also be used in formulas to make them more readable.
Quick Excel Tip #16
You can add data points to an existing chart series, even if it is not in a contiguous range.
Quick Excel Tip #17
Use Copy & Paste special -> Formats to make charts look the same as each other.
The best time-saving tip I ever learned.
Quick Excel Tip #18
Add a small amount of white space between columns to make tables easier to read.
Quick Excel Tip #19
Do you get annoyed by the error messages and problems caused by merged cells? If so, try to use Center Across Selection instead. Similar functionality but with no annoying messages.
Quick Excel Tip #20
If you’ve got hidden rows and only want to copy the visible cells try Find & Select -> Go To Special -> Visible Cells only.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
#16 Pasting data into a chart just blew my mind. Fantastic post as usual!
Thanks Will.
It took me a long time to realize the Paste Special options box changed depending on what you had clicked on. So I’m glad I could share the knowledge.
Copy/paste special on charts…who knew! Thank you so much for those, especially the copying as points. I used to copy/paste a new series and then have to reformat it….no more!! 🙂
#20 copying visible cells only is awesome as well.
Thanks Jeff. I used to do the same as you, until I found that little feature. Good to know you’ve found it useful too.
Pressing Alt + ; = Visible cells
Shriram,
That’s a good shortcut, it saves 5 clicks!!!
I didn’t know that one, thanks for sharing it.