Pivot tables - how do you creat them?
- Under Insert tab, select pivot table on left. If you have one block of data, Excel will automatically select it. If you have multiple blocks of data on your sheet, you will need to manually select rows/columns.
- You will then be asked where you want pivot table to be placed - new work sheet or existing work sheet
- You then get pivot table box on left and pivot table field list on right. The pivot table field list will only appear when you are clicked onto the pivot table box. You can drag the pivot table field list to be next to your pivot table box. Within the Pivot table field list box, you can choose fields to add to report under headings: Rows, Columns, Values, Report Filter. Usually best to start by selecting row labels.
The last two minutes of this video are a bit helpful:
https://www.youtube.com/watch?v=peNTp5fuKFg&list=PLHcaWW6M_d8HKRd2nhiKM53giwaLbHZSt
Populating in ascending order in excel
- How to duplicate numbers in a column: hover cursor over little box in lower right hand corner of cell until cursor turns to filled in cross, then drag down column - cell will duplicate all the way down. Works with numbers and text. Works across rows and columns.
- If you want to number columns in a logical way, for example 1-30 without typing in nubmers 1 to 30, or 5,10,15,20, or some other predictable pattern, you can do this by filling in first two cells, clicking off cells, highlighting both cells, and again dragging black cross down column. You can do the same thing across rows. And excel will intuit more than numbers - you can also do it with days of week, months of year, dates in DD/MM/YYYY format, etc. For dates, it is always best to format cells as date (go to Home tab, Cells and Format Cells).
Sorting in ascending and descending order - how do you do it. What does custom sort allow you to sort by?
Highlight cells to sort. Home tab, Editing, Sort and Filter, select sort ‘smallest to largest’ or ‘largest to smallest’.
Can also ‘Custom sort’ by cell colour, font colour, values or cell icon. Custom sort allows you to select which column you want to sort by.
Working with selections. How do you select your whole sheet?
Can select non-adjacent cells by holding down Ctrl. Use shift to select large blocks of cells.
To select your whole sheet, press on the little box in the upper left hand corner.
Keyboard shorcut for undo?
Ctrl+z
Removing duplicate values
Select colums (by clicking on column letter A, B, C etc) or cells.
Go to DATA tab.
Click ‘Remove Duplicates’
It will prompt you regarding your selection - if you want entire row associated with duplicate column value to be removed, you need to select whole data block, but remove duplicates based on particular column.
Basic formatting
Right click on column or cells.
From drop-down menu, select Format cells.
You can format Number, Alignment, Font, Border, Fill, Protection.
Under number, you can add decimal places, format as date, currency, time, text, etc.
