xx

Create a formula using the CONCAT function.

=CONCAT(S6&" ("&Q6&") "&R6&" ratings") 例如这个里面的: BUS1305-Excel-ApdxA-Project-File2-LuLeo-Starter.xlsx

Create a formula using a nested IF function.

=IF(D5=3,E5*1,IF(D5=2,E5*0.5,0) 看手机图片夹46

Create a formula using the LEN function.

=LEN(B3) LEN算的是这个地方的数字或字母的长度. (这个格子里面有多少字符)

Create a formula using the OR function.

=OR(E5>25000,D5>=3) 在OR里面,只要满足任意一个条件,就都是TRUE, 两者都不满足就是FALSE

Create a formula using the PROPER function.

=PROPER(J5) 自动大写 从glenn cutis 变成了 Glenn Curtis

Create a formula using the RIGHT function.

=RIGHT(H5,2) H5 是选的地方 2 是数几位 right就是右边最后2位,也就是,最后两个

Create a formula using the ROUND function.

=ROUND(J6,0) 0是后面小数点四舍五入到小数点后几位 这个是0 所以就是48500.31 变成了 48500

Create a formula using 3D references.

=SUM(October:December!B6) ! 让我知道这是页名称 每一页都应该有一个当页的Total Sales(也就是B6), 最后有一个好几页加起来的总Total Sales.

Create a formula using the TODAY function.

=TODAY()

Create a formula using the VLOOKUP function.

=VLOOKUP(C5,GE:H10,2) C5, 是前面要查的数据,是那个被对比的数据 GE:H10, 是对比的数据 后面的小表格 2 后面小表格的第几行或第几列 是要输入的数据

Add a timeline slicer to a PivotTable.

Analyze Filter Insert timeline

Drill down into a PivotTable.

Analyze Active Field Drill Up

Insert a page break.

Between row 18 and row 19 先选择区域 点击row 19, 这个会加在row 19的上方 Page Layout Page Setup Breaks Insert Page Break

Change a column width to an exact value.

Change the width of column C to exactly 15 characters.

Switch the row/column data used in a chart.

Chart Design Data Switch Row/Column

Delete duplicate records from a table.

Data Data Tools Remove Duplicate

Convert text to columns.

Data Data Tools Text to Columns

Insert subtotals.

Data Outline Subtotal

Sort a table based on a custom sort order.

Data Sort & Filter Ascending Sort A to Z (lowest to highest, small to largest) Descending Sort Z to A (highest to lowest, largest to small)

Add a total row to a table.

Design Table Style Options Total Row 按下右边的箭头会出现一系列选项 按 Sum

Create a formula using absolute cell references.

Enter a formula in cell C6 that divides the value in cell B6 by the value in cell B18, using an absolute cell reference to cell B18. =B5/$B$18

Check workbook compatibility.

File Info Check for Issues Check compatibility OK

Save a workbook as a template.

File Save As

Save a workbook to a new location.

File Save As Browse Documents Financials

Use the Fill handle to copy a formula.

Fill handle to copy a formula 就是右下角的黑色加号➕ (就是Auto Fill)

Wrap text Win a cell.

Home Alignment Wrap text

Rotate cell content to an exact value.

Home Alignment ab/ (ab还有向上的一个斜杠,Wrap text左边) 按下旁边的黑色小箭头 Format Cell Alignment最后一个 右边输入 Degrees

Apply the Currency number format.

Home Number General后边有个小向下箭头 Currency

Use the Format Painter.

Home Paste 旁边 第三个 有个 Format Painter

Create a New conditional formatting rule.

Home Styles Conditional Formatting Top/Bottom Rules More Rules... 改变数字 Format... 改变底下颜色, Fill 选择颜色 OK OK

Clear a conditional formatting rule.

Home Styles Conditional Formatting Clear Rules

Create a Data Bars conditional formatting rule.

Home Styles Conditional Formatting Data Bars Gradient Fill Green Data Bar

Create a Duplicate Values conditional formatting rule.

Home Styles Conditional Formatting Highlight Cells Rules Duplicate Values... 后选择颜色

Create a Highlight Cells conditional formatting rule.

Home Styles Conditional Formatting Highlight Cells Rules Less Than... 输入数字与改变颜色

Format as a table

Home Styles Format as Table

Create a custom cell style.

Home Styles cell styles New Cell Style... 输入Name Format

Create a formula using the IF function.

In cell F4, enter a formula using the IF function that retums a value of YES if cell E4 is greater than 100,000, and a value of NO if not. =IF(E4>100000,"YES","NO")

Insert a Line chart.

Insert Charts Insert Line or Area Chart 2-D Line

Insert a chart using the Recommended Chart button.

Insert Charts Recommended Charts Stacked Column

Insert a Combination chart.

Insert Charts Recommended Charts All Charts Combo (最后一个) 底下选择 Secondary Axis

Insert a hyperlink to a website.

Insert Links Link Address: 网站 Text to display: 你最后想让它显示什么

Set print titles for a worksheet.

Page Layout Page Setup Print Titles 选择Rows或者Columns去输入

Refresh PivotTable data.

PivotTable Tools Analyze Refresh (((Data > Queries & Connections > Refresh All)))

Change the layout of a PivotTable.

PivotTable Tools Design Layout Report Layout Show in Outline Form

Move worksheets.

Reorder the worksheet in the workbook 在最底下去移动Worksheet

Insert markers into a sparkline.

Sparkline Style Marker Color (四个颜色的那一个) High Point 后选颜色 Low Point 后选颜色

Extend the range of a table.

Table Design Properties Resize Table

Convert a table to a range.

Table Design Tools Convert to range

Set the print area of a worksheet.

先选择区域 Page Layout Page Setup Print Area

Add alt text to an object.

右键单击Chart Edit Alt Text... 右边输入

Fill a range using a custom AutoFill list.

在A10:A13加入custom AutoFill list 只要在一开始A10写下list的第一词语, Deerfield 然后后面用右下角的➕ AutoFill 剩下的就好了

Create a formula using structured references.

就是那个([@bonus%])

Create a defined name for a range.

左上角Name box

Insert a header using Header & Footer Elements.

最底下在调整底下百分比大小傍边有3个按钮 第二个 最上面的 Design 可以看见输入Header 的地方 中间按 Sheet Name 右边输入 Last Viewed_ 然后按 Current Date

Rename a worksheet.

最底下的名字! Sheet1 去改名字

Move a chart to a chart sheet.

点击Chart Design Location Move Chart (最后面) 选择New sheet

Add gridlines to a chart.

点击Chart, chart旁边有个加号➕ Chart Elements 点击Gridline 点击Primary Major Horizontal

Delete a chart element.

点击Chart, chart旁边有个加号➕ Chart Elements 点击Legend, 去删掉选择

Change the units of a chart axis.

看图27: 右键单击图中的vertical axis 最后一个 format axis... 右边Units Major 输入 Minor 输入

Create a formula using mixed cell references.

看图片夹 70

Insert a picture as a worksheet background.

看手机上的图片夹 63

Add a field to the COLUMNS area of the PivotTable Fields task pane.

这个就是旁边往下拖的那个, 上面一堆 底下四个框

Create a custom AutoFill list.

选择文字范围 File 最下面Options Advanced 最下面 Edit Custom Lists... (Create lists for use in sorts and fill sequences:) Import OK