Excel 101
Sophie avatar
Written by Sophie
Updated over a week ago

Excel 101 

Below are a few beginner tools to help you master Excel!

  • “=SUM”: adds values → Type “=sum(d1:d10)“ type in cell values in place of "d1:d10"

  • =Average”: finds the mean → Type "=average(d1:d10)"  - type in cell values in place of "d1:d10"

  • =Count”: finds the number of values → Type "=count(d1:d10)"  - type in cell values in place of "d1:d10"

  • Inserting an extra row in table: click on number rows on left then click insert → highlight number of rows you want to insert

  • Locking a cell: add '$' sign

  • Copying 1 value down a column: double click bottom right corner then hold control/command and click down arrow to go all the way down row 

  • Adding a Filter: click data tab, select ‘filter’ → can put values in ascending/descending order or hide/only show certain rows

  • Color coding:  highlight data then click conditional formatting under Home menu, select your ‘logic from the drop down’ (you can create your own rule), then select OK

  • Counting with condition: logical test (condition?), what do you want me to do with it?, what if it’s wrong? → =if (pick cell) = (pick number), → =COUNTIF (range,”condition”) →ex: range= B2;B64 , condition is RED → Type COUNTIF(B2:B64,”RED” ) → this will count all the cells from row 2 to 64 if they contain the word RED

  • Removing Duplicates: Highlight the row/column you want to remove duplicates from, go to the data tab and select ‘remove duplicates’ (under tools) → A pop up will confirm data you want to work with

  • Combine cells: Enter two different cells in top bar of excel with & and “” ex;   =A2&””&B3

  • Percentile: Order the data from low to high, Multiply the number of observations in the data set by r/100, Round the resulting number up to the next integer, Select the value corresponding to that integer → that is the __th percentile

  • Histogram: Highlight all the cells that have data you want to use in your histogram/chart, Click on the Data tab, then click "data analysis", then select the histogram command, input your range (A1:A10), control, shift, down arrow, then plugin your bins in 2nd column (a bin represents the range of values you want in each bar on your graph, for example if you want 20 values in each bin you would Type 20 into B1, 40 into B2, 60 into B3...up until the highest value you have in the A column that is a multiple of 20) → This creates a histogram → once you do this you will have a value table that states Bin and Frequency, select this chart and click insert - then select the bar graph icon and click enter to get your chart --> from here you can edit the visual aspects!

  • Basic Math: + = add,  - = subtract,  * = multiply,  / =divide

  • Printing your Spreadsheet on 1 page: select page layout tab, set scale and fit options to the values you want for height and width 

If you're after further info (and to get some practice!) check out Udemy for a free tutorial on the basics https://www.udemy.com/ms-excel-from-0-to-working-professional-in-1-hour/?siteID=TnL5HPStwNw-TEcQ_qSTWfK6M0BSpwAyMA&LSNPUBID=TnL5HPStwNw

Did this answer your question?