Hello. I'm here in beautiful London with David Bena, a fellow youtuber who runs el consulting and we are here to walk around some of
the sites of London and share some of the top secret XL tricks with you guys
So David, what's the first tip you're going to show us? So the first tip that I'm gonna show you is clear
All no one knows this feature in Excel, but it's a way to actually delete things
not only what's the contents in the cells, but also they're formatting in one click
I love this trick. Ok, great. So I want to go from this to absolutely nothing in one go
Uh A lot of people know that you can select it, press the delete button
but then you also have to remove the colors and then remove the borders and all of these things can be quite tedious
Um People also generally know you can right click choose delete and then this will move
But what happens is this don't move me has now moved up. So that's not what I want at all either
What I want is to select this and then in the home tab very hidden on the right
You have clear with a drop down. What does that do you have clear all
That's exactly what you want. Get rid of everything in one go. You also have very usefully here
clear formats which keeps the data but gets rid of the formatting
both extraordinarily useful. Right here we are in London with the typical traditional black taxis and some double decker buses
So Alan, what is your first tip that you want to share with people
My first secret tip is going to be how we can hide the grid lines of Excel
So often, I'm asked how you get it to look so clean around your tables and your data
So we'll look at that quick tip now. So this is a very quick tip because I am often asked when I am delivering my
training and providing my sample spreadsheets, how do I get rid of all of the lines on the spreadsheet
and they are your grid lines? So in the data range that's been used in range B two to D 10
Here, I have used the button on the home tab to provide some borders
But then for the grid lines on the view tab, we have a simple check box here where we can toggle on or
indeed toggle off those grid lines on the page layout tab of the ribbon
We also have some grid line options here where once again we have the toggle for the screen view about whether we would like
the grid lines visible or whether we'd like them not. And you have the additional option of displaying the grid lines when printed
by default grid lines on the screen, no grid lines on the print
but you can change this to your own desires. So here we are outside the Shard and David
what is your next tip you've got for us? So my next tip and Excel that I absolutely love is a hidden feature to convert
from us style dates to European style date. Let's go see what that looks like
Fantastic. So here we have our data in Excel with our dates
If you select those, you click on the data tab and then this text two columns feature
it's nothing to do with texture columns, what I'm about to do, but it's hidden away in this click next two times and then you
get to this in date, choose MD Y month, day, year and then click finish and you'll get it looking like
this before I move on. I just want to show you this is clearly 4 22 22nd of April
same as this one. Even though there's two verses four digits for the year
this one um will go from the sixth of April if it was in day
month, year to the fourth of June and these two are with text
but they still work. So click on finish there and let's see it
do that. All right. And final bonus tip, which is how to go from this style to this style with a keyboard chalker press control
and that in many keyboards control, shift three uh UK keyboards, it's a bit different
There you go. Done. All right. So Alan, what is your next tip
Ok. Well, the next one is going to be using spark clients in Exel er which is a great visual tool for your dashboards and
your reports. Awesome. Let's go see it. So in this tip, we are going to look at spark lines
a wonderful little visual for your Excel reports. And on screen, I have six weeks worth of sales data
for these products for our food delivery service and on this sheet
I have a wonderful looking dashboard. I'm sure you'll agree. It has nothing apart from a list on here but maybe imagine
the charts and the other visuals we have going on in this range though C four to C 10
I want to put a little micro chart to visualize the trend of the last six weeks of sos
So if we click on insert tab, we have spark lines about two thirds along we have the option of the line
column or win loss spark line, the win loss spark line relies on negative values
So we won't be choosing that here. It's either line or column
I'm going to click on line. So there prompts me for where the data is to use location range is already selected
It's back to the other sheet for us. And I'll select all of the values that I'm plotting here
Click. OK. And here are our spike clones. Seven different little line graphs
So at a glance, we can see what the last six weeks have been like for each of these products
Now, if I click on one of those spark clients, it selects them all as they work as a group
you can change that. But that's normally quite a useful thing. At the top
We have a design tab. And on here, I could change this to a column type
Instead, you may prefer that to the line. We also have these wonderful check boxes where we can show important values such
as higher point and it changes the color of it. And then we also have these styles where I could choose a different style such
as this one, I could even expand the gallery and look at creating a Rome by changing the settings for the sparkle color
and the marker color or maybe just choosing one of the other built in ones uh such as this one here
this one looks quite nice and now that one is applied and that is a quick insight
a quick intro into the wonderful spark lines tool. OK. So David
what is the next tip you have for us? So almost everyone highlights cells based on what's in it
If text contains unpaid or waiting, they like to put that in yellow and red or whatever
But actually Excel is a feature that can do that for you automatically called text that contains within conditional formatting
Let's go see what that's about. So what people typically do when they have this thing is they will make the word pending
for example, in yellow. But the issue is when it's changed to scent
it doesn't actually change color. And the issue that we get is that you get the wrong information
What's better is something like this when it says no, instead of yes
it automatically changes to this pink red color. So how are we gonna do that
Uh Select your data, go to conditional formatting in itself. One of Excel's best hidden tricks
Almost no one knows this. And by far, the most useful part of conditional formatting is text that contains click on highlight cell rules and
text that contains and then here we can say the word say sent is going to be showing in green
That's OK and you are done. That's it. Um It is text that contains
So if you have for example, sent now it's still in green
And last point you have yellow, green and red. But if you have more
more colors, you have options that says custom formatting as well. All right
And Alan, what is your next tip? Well, my next tip, David is the go to special feature of Excel
So talking about things that are hidden in plain sight. Here is a feat chart that will select cells containing elements that we can't see
things like data validation rules, conditional formatting rules formulas. So yeah, really really useful hidden feature of Excel in
this tip, we are going to look at the go to special feature of Excel a feature that can select cells
that contain elements. We cannot see this could be formulas, it could be conditional formatting can be data validation
Some really useful options. In this first example, we will look at selecting cells that contain formulas
We cannot see the formulas in these cells on my sheet at the moment
So if I click on the find and select button, it is on the end of the home tab
it presents me with what they think are popular options and we want formulas and there it is
So I'll click on formulas and immediately it selects all of those cells
So from here I can now do what I want to do whether that be to change its color
whether it be to unlock the cell ready for some protection, whatever it may be for this quick demo
I will simply go to my field color and give it a nice maybe green shade and all of that is done as quick and
accurately as possible. Now if I jump to another sheet, another quick demo in this list
I have lots of blank rows. So what I'm going to do is select cells A across to D columns
A to D sorry. And we want to get rid of those blank rows
Now, there's quite a few approaches to this, but I don't want to change its order
I don't want to filter anything out. I want to actually remove them
So I'm going to the find and select button. Now, blanks is not an option
So we will go to special to open up the window with a complete list of options
some really cool and useful stuff in here. I'm going to choose blanks and click
OK. That now selects all the cells in the range that I selected that are blank from here
delete button on home delete sheet rows and as easy as that the blanks are removed
And I have myself a clean data set for analysis. So David
what are your top three secret shortcuts that everybody should know in Intel
So everyone knows copy and paste and undo. But there's some really great ones that hardly anyone knows
So here's one to go to the next sheet. You can press control and page down
control, page up for the previous sheet. I also love using alt equals to do the auto sum feature
It automatically puts in a sum formula. But then probably my biggest one is I like bringing everything together and actually customizing your own keyboard
shortcuts. With a feature called the quick access toolbar. All right. So let me show you some of my favorite shortcuts
If you want to go to the next sheet, you can press control and page down
control, page up, goes to the previous sheet in Macs and certain versions of laptops
it will be control function and down arrow, control function up, arrow
Next one. If you want to get a sum very quickly, instead of typing it out
you can press alt and equals. And that will give you the sum like that point to note
if you have blanks or text in your data in the middle, it will stop there because they think that your column has stopped there
as well. So best to avoid those, it goes even further. If you have multiple rows and multiple columns
just select them with some gaps at the end press alt equals
And there it will do sums in all of your rows and all of your columns super super fast
So those are two of them. Finally, my next tip is about getting stuff that you're not using all the time
They are hidden in plain sight into your quick access toolbar. So we've shown you some really cool stuff in this video
For example, conditional formatting text that contains that is three clicks deep and most people don't know it's there or clear all but
why not get them so that you can very quickly access them here. So you can actually create your own keyboard shortcuts by adding these
to the quick access toolbar, quick guys. So toolbar is this thing here can be above or below your ribbon
find an item you like to use often. So for example, let me go for go to special that we showed
in this video and click add to quick access toolbar. It might show you that icon or it might show you the icon that you
see next to it and then very quickly it can pop up with that thing there
You can even create your own keyboard shortcuts with the alt key
If you hold down the alt button, you get the numbers that appear next to each item
And for me, number seven is clear. All so alt seven is my handmade shortcut for a clear all
If you right, click on this, you get more options. You can do further customization steps through this
Uh It's quite useful as well, but that's what I'm gonna show you today
Great. Thanks for watching. All right, Alan. So we're also talking about shortcuts in this video
What are your top shortcut tips that no-one knew about in Excel? Ok
let me show you. Here are my three shortcut tips. Now, first up
we have the short cuts to put in today's date. So, so useful
I use it pretty much daily myself and that is control semicolon and that is today's date
Saves you typing it also ensures it is accurate. The amount of people who type the wrong date through lack of attention is
higher than you imagine. And that would ruin any analysis we're going to do
If not checked next up, we have a formula we're going to pull and sell at three nice and easy equals
the quantity of something sold. And I'll multiply it by, let's say £3.50 is a prices thing we're selling
If I press enter and move back to that cell, I want to copy it to the bottom
This list is massive. So rather than drag this down, I'm going to double click the square
in the corner to fill handle your mouse turns from that fat white cross into that skinny black cross
double click shoots to the bottom job done. Finally back to the keyboard
We want to navigate this big list from my previous tip. Now
if I'm in the cell, San Maria Anders cell H two, I wanna get to the bottom of this list
control key and the down arrow or shoot to the first empty cell I E the last one from
here, I could press the down arrow alone to move into the next vacancy already
For some new data entry, I could all press up to move it back into that row
And let's say I want to select all the cells for this individual
maybe able to remove it, maybe to copy somewhere. Control shift and the right arrow and I've now got the entire row
It could have been 30 columns wid rather than the four that it is
And I can now once again, delete, change color, copy, whatever I want to do
If I wanted the whole table control, shift up arrow shoots me to the top
Do you want the headers? Yes. Carry on. But don't shift and the down arrow moves me one row down
I want to get back to just column. H I don't want all four shift arrow
left, shift arrow, left, shift arrow left. I'm now back to that one
So knowing those keys, the control key with the arrows and control shift with the arrows
The first one for navigation includes shift for selection are essential shortcuts
I think when you work with Excel. So David, is there anything that you can share with us about what is new to Excel
I love that Excel keeps pushing out these awesome core features every month
especially the office Insiders channel. They get a sneak peek about what's coming next
Something that I found recently is called workbook statistics. And I'll show you that in a sec
But another one I want to point out is the linked data types, particularly geography
data types that can pull out anything from population to area to GDP or like birth rates
fertility rates inside different countries, different provinces and states and cities. It's really cool
Absolutely. Yeah. Have a look. So here we are in Excel and we have some countries listed out here
In the newer version, you can select these countries which just has the text in there
click on the data tab and choose this thing called geography. What it does is it looks them up and finds out information about that
country. So if you, if you click on this, it shows the card for that country with all of this information and you can select
the whole column, choose this plus button and choose for example, the area or something more obscure like uh the forested area
maybe the armed forces side, something like that. So if you like that
that's cool. But you can then select your data and choose another new feature in the insert tab called a map chart
Click on that and it puts it in a map darkest to lightest
which is pretty cool. You can do this with states and with cities as well
So you can just keep sort of adding to the list of what you want to see and it gives you different er
informations for each of these, like the, the administrative divisions, the capital for states
et cetera, et cetera. Another pretty cool feature which is only in the newest version of Excel is in the review tab
This thing called workbook statistics tells me here. I have three tables in this worksheet
The end of the worksheet sells with data number of charts, number of formulas
pivot tables and macros in the whole workbook as well. So that's really cool
These are some of my favorite new features in Excel. Thanks for watching and we've come to the end
Thanks for watching. So I hope you find some of those secret tips very useful and incorporate them into your day to day work
Thank you for watching. Please check out some of our other tips and videos on our youtube channel and go and check out David's youtube
channel, Excel Consulting. I'll put a link in the description of this video to go check that out. Thank you for watching