Computer Tutorials for Beginners

The Joys of Excel Flash Fill  - Flash Fill in Excel can make your life a lot easier, if you have to manipulate values in a cell. With Flash Fill, Excel tries to guess what you want to do. Let's have an example. Example One - extract product IDs from a cell Suppose you had a spreadsheet like this: However, all you want is the product IDs at the start, the letters. Is there an easy way to extract just the letters? Yes - use Flash Fill. Create a simple spreadsheet like the one in the image above. Now click inside cell B2. Enter the first of the letters, ABC: Press the enter key on your keyboard and you'll see cell B3 selected: To do a Flash Fill, hold down the CTRL key on your keyboard. Keep the CTRL key held down and press the letter E on your keyboard. Excel Flash Fills the rest of the product IDs for you: Notice the small box that's appeared in the bottom right of the selected cell (B3). Click the arrow on the box to see a menu: Cl...

The Excel SumIF Function  - Another useful Excel function is SumIF. This function is like CountIf, except it adds one more argument: SUMIF( range ,  criteria ,  sum_range ) Range and criteria are the same as with  CountIF  - the range of cells to search, and what you want Excel to look for. The Sum_Range is like range, but it searches a new range of cells. To clarify all that, here's what we'll use SumIF for. (Start a new spreadsheet for this.) Five people have ordered goods from us. Some have paid us, but some haven't. The five people are Elisa, Kelly, Steven, Euan, and Holly. We'll use SumIF to calculate how much in total has been paid to us, and how much is still owed. So in Column A, enter the names: In Column B enter how much each person owes: In Column C, enter TRUE or FALSE values. TRUE means they have paid up, and FALSE means they haven't: Add two more labels: Total Paid, and Still Owed. Your spreadsheet should look something li...

The Excel CountIF Function  - Another useful function that uses Conditional Logic is CountIF. This one is fairly straightforward. As its name suggests, it counts things! But it counts things IF a condition is met. For example, keep a count of how many students have an A Grade. To get you started with this function, we'll use our   Student Grade spreadsheet   and count how many students have a score of 70 or above. First, add the following label to your spreadsheet: As you can see, we've put our new label at the start of the K column. We can now use the CountIF function to see how many of the students scored 70 or above for a given subject. The CountIF function looks like this: COUNTIF( range ,   criteria ) The function takes two arguments (the words in the round brackets). The first argument is range, and this means the range of cells you want Excel to count. Criteria means, "What do you want Excel to look for when it's counting?". So click i...

Icons in Excel Cells  - You can format your cells so that they have icons in them. The icons are used to illustrate the values. For example, here's a task list in Excel without icons. The 1 indicates that a task was complete, while the 0 indicates it is not complete: Now here's the same task list with icons: The second task list is far more useful! Let's see how it's done. Create the same simple spreadsheet as the one above. Now highlight the numbers from cell B2 to C5. With the cells highlighted, locate   Conditional Formatting   on the   Home   ribbon at the top of Excel. From the Conditional Formatting menu, select   Icons Sets : The Icons Sets are grouped into four categories: Directional, Shapes, Indicators, and Ratings. Click on the circles under Indicators. Your B column will then look like ours from the image at the top of the page. If you hold your mouse over each icon set, you'll see that it says something like "3 indicators",...

Excel Colour scales in cells  - You can also use Conditional Formatting to add some colour scales to a set of values. For example, in the image below we have some Celsius temperatures: Wouldn't it be nice if we could colour the cells, based on the temperatures? You can do that with Colour Scales. Create the column of values in the image above. Highlight the values from E2 to E9. On the   Home tab of the Excel ribbon, locate the   Styles   panel. Click   Conditional Formatting > Colour Scales : You can select one of the colour scales from the sub menu, if you like. But the colours are not quite the ones we want for a temperature scale. So click the   More Rules   entry at the bottom of the menu to see the following dialogue box appear: The dialogue box shows a 2-Color Scale, which is OK. Click the   Color   dropdown list under the   Minimum   heading and select a blue colour: For the Maximum colour, select a...

Format your spreadsheet cells with Data Bars  - You can format the cells on your spreadsheet so that they have bars in them, or even set up a range of numbers to have a colour scale. You can do all that with Conditional Formatting. In the image below, we've formatted a set of sales figures so that the cells contain data bars: Create a new spreadsheet. In A1 enter the text heading January. Widen the A column and then enter any numbers you like in cells A2 to A7: Now select the cells A2 to A7. From the   Home   ribbon at the top of Excel, locate the   Styles   panel and select the   Conditional Formatting   item. From the menu, select   Data Bars . You'll then see some formatting options appear: You can select a gradient fill for your bars, or a solid fill. Click on one of the Fills to see how it looks on your spreadsheet. If you click on   More Rules , you'll see the following dialogue box appear: Here, you can select f...

Conditional Formatting in Excel - Averages  - Have a look again at the student scores spreadsheet from an earlier tutorial: If you haven't got this spreadsheet, download it using the link below  Student Averages Spreadsheet When you open up the spreadsheet above in Excel, click   File > Save As   and save it with a different file name, as you'll be needing another copy later. What we're going to do is some conditional formatting based on Average scores. We'll have one colour for those scores that are above average and another colour for those that are below average. When you're done, the spreadsheet will look something like this: With the Conditional Formatting applied, you can easily see that Mary achieved above average scores in all subjects (red). You can also see that poor Mark, meanwhile, has lower than average scores in all subjects (green). This information is not readily apparent without the Conditional Formatting. So how is it done?...